CODEWITHSUNDEEP
sqlsql-serversql-server-2005t-sql
sreekanth
sreekanth

Reputation: 749

Return value from exec(@sql)

I want get the value from Exec(@sql) and assign to @Rowcount(int)

Here is my query:

'SET @RowCount = (select count(*) 
                    FROM dbo.Comm_Services 
                   WHERE CompanyId = '+cast(@CompanyId as char)+' and '+@condition+')'

Upvotes: 63

Views: 192239

Answers (6)

ozhug
ozhug

Reputation: 1083

With this pattern, you can return whatever type or results you are after from the results of your exec statement.

declare @result table ([rowcount] int);

insert into @result ([rowcount])
Exec(@sql)

Upvotes: 1

bugstar
bugstar

Reputation: 125

DECLARE @nReturn int = 0
EXEC @nReturn = Stored Procedure

Upvotes: 3

TylerM
TylerM

Reputation: 160

Was playing with this today... I believe you can also use @@ROWCOUNT, like this:

DECLARE @SQL VARCHAR(50)
DECLARE @Rowcount INT
SET @SQL = 'SELECT 1 UNION SELECT 2'
EXEC(@SQL)
SET @Rowcount = @@ROWCOUNT
SELECT @Rowcount

Then replace the SELECT 1 UNION SELECT 2 with your actual select without the count. I'd suggest just putting 1 in your select, like this:

SELECT 1
FROM dbo.Comm_Services
WHERE....
....

(as opposed to putting SELECT *)

Hope that helps.

Upvotes: 1

Manfred Sorg
Manfred Sorg

Reputation: 1890

On the one hand you could use sp_executesql:

exec sp_executesql N'select @rowcount=count(*) from anytable', 
                    N'@rowcount int output', @rowcount output;

On the other hand you could use a temporary table:

declare @result table ([rowcount] int);
insert into @result ([rowcount])
exec (N'select count(*) from anytable');
declare @rowcount int = (select top (1) [rowcount] from @result);

Upvotes: 114

kst
kst

Reputation: 1518

that's my procedure 

CREATE PROC sp_count
    @CompanyId sysname,
    @codition sysname
    AS
    SET NOCOUNT ON
    CREATE TABLE #ctr
    ( NumRows int )

    DECLARE @intCount int
         , @vcSQL varchar(255)

    SELECT    @vcSQL = ' INSERT #ctr FROM dbo.Comm_Services 
                       WHERE CompanyId = '+@CompanyId+' and '+@condition+')'
    EXEC      (@vcSQL)

    IF @@ERROR = 0
    BEGIN
         SELECT    @intCount = NumRows
         FROM #ctr

         DROP TABLE #ctr
         RETURN @intCount
    END
    ELSE
    BEGIN
         DROP TABLE #ctr
         RETURN -1
    END
    GO

Upvotes: 0

RPM1984
RPM1984

Reputation: 73113

If i understand you correctly, (i probably don't)

'SELECT @RowCount = COUNT(*)
                   FROM dbo.Comm_Services
                   WHERE CompanyId = ' + CAST(@CompanyId AS CHAR) + '
                   AND ' + @condition

Upvotes: -1

Related Questions