Question about SP_EXECUTESQL
-
I have a stored procedure and i'm executing a dymamic SQL statement like this.
DECLARE @sql nvarchar(100)
DECLARE @RowCount Int
SET @sql = 'SELECT COUNT(*) FROM MyTable'
I want to get the result of the query and store it in the @RowCount variable.
I tried to use the following statement:
SET @RowCount = EXEC(@sql)
but this statement didnot work.
i read in some forums that we can use a table instead and set the result to it like this:
I created a table of one column:
CREATE TABLE #Temp1 (RowsCount int)
INSERT INTO #Temp1 EXEC(@RowSql)
Then i got the value using a simple SELECT statement like this:
SET @RowsCount = (SELECT RowsCount FROM #Temp1)
Then I dropped the table:
DROP TABLE #Temp1
So the problem is solved, but i'm not intersted in this solution and looking for a better solution to get a scalar value from this procedure.
Another solution was to use SP_EXECUTESQL procedure like this:
EXEC SP_EXECUTESQL
@statement = @RowSql,
@params = N'@RowsCountint OUTPUT',
@RowsCount = @RowsCount OUTPUT
but the problem was that in the procedure this statement returned this value in the result set, and i need this result to be (in-scope only) and don't want to return it to the applications result set.
So is there a way to prevent it from returning in the result set?
-
Assigning the COUNT(*) to a variable will prevent it returning a result set
DECLARE @RowsCount intSET @RowSql = 'SELECT @RowsCount = COUNT(*) FROM MyTable'
EXEC SP_EXECUTESQL
@statement = @RowSql,
@params = N'@RowsCount int OUTPUT',
@RowsCount = @RowsCount OUTPUT
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Great, it's working
thanks a lot.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply