CODEWITHSUNDEEP
sql-servert-sqlvariablesstored-procedures
Fred Smith
Fred Smith

Reputation: 2139

SQL set variable as the result of a query in a stored procedure

I am creating a stored procedure with a cursor and I need to store the quantity of a column into a variable. SQL says that it cannot convert nvarchar into int I have tried to use CONVERT, CAST and EXEC but couldn't make it work. How can I solve this ?

DECLARE @FieldName nvarchar(255);
DECLARE @RequestCode Nvarchar(50);
DECLARE @ReqSQLQuantity nvarchar(max);
DECLARE @Quantity int;

Select @ReqSQLQuantity = concat('select count(*) From (select distinct [', @FieldName , '] from [report_' , @RequestCode , ']) as number')
Set @Quantity = (@ReqSQLQuantity)
Select @Quantity

Upvotes: 3

Views: 7064

Answers (2)

M.Ali
M.Ali

Reputation: 69564

Another a bit safer option would be to use sp_executesql stored procedure something like this....

DECLARE @FieldName nvarchar(255);
DECLARE @RequestCode Nvarchar(50);
DECLARE @ReqSQLQuantity nvarchar(max);
DECLARE @Quantity int, @tableName SYSNAME;

SET @tableName = N'report_' + @RequestCode

Select @ReqSQLQuantity = N'  select @Quantity = count(*) '
                        + N' From (select distinct ' + QUOTENAME(@FieldName)  
                        + N' from ' + QUOTENAME(@tableName) + N' ) as number'
Exec sp_executesql @ReqSQLQuantity
                  ,N'@Quantity INT OUTPUT'
                  ,@Quantity OUTPUT

Select @Quantity

Upvotes: 3

Remus Rusanu
Remus Rusanu

Reputation: 294417

Set @Quantite = (@ReqSQLQuantite)

This is not an evaluation, is an implicit conversion. From NVARCHAR to INT, and you are trying to convert a SQL query text into an INT, hence the error.

Also, you are assuming that results are the same as return values and can be assigned to variables. This is incorrect, SQL execution results are sent to the client. To capture a result, you must use a SELECT assign: SELECT @value =.... Trying to run @variable = EXEC(...) is not the same thing. Think at SELECT results the same way as a print in an app: a print sends some text to the console. If you run some pseudo-code like x = print('foo') then 'foo' was sent to the console, and x contains the value returned by print (whatever that is). Same with this pseudo-SQL, @x = EXEC('SELECT foo') will send foo to the client, and @x will some numeric value which is the value returned by EXEC (in a correct example one would have to use explicit RETURN statement to set it).

Overall, the code as posted has absolutely no need for capturing the value and returning it, it can simply execute the dynamic SQL and let the result be returned to client:

SET @sql = concat(N'SELECT count(*) FROM (SELECT ... FROM ...)');
exec sp_executesql @sql;

Upvotes: 2

Related Questions