CODEWITHSUNDEEP
sql-serverstored-procedures
SH.Developer
SH.Developer

Reputation: 187

save string query result in variable in sql server

I have this query (this is a one slice of my query)

DECLARE @Count varchar(max)

SET @Count = ('SELECT TOP 1 COUNT(Id) FROM ' + QUOTENAME(@Tbl))

EXEC (@Count)

DECLARE @CalRemainPage int = convert(int, @Count ) - (3 * 15)

and when I run my query I get this error from this slice

Conversion failed when converting the varchar value 'SELECT TOP 1 COUNT(Id) FROM [Sample_Work]' to data type int.

I know my problem is result of @count, put the @count string instead of result of this 

DECLARE @CalRemainPage int = convert(int, @Count ) - (3 * 15)

How can I save result of @count query and use @count in other place such as top sample?

Thank you

Upvotes: 2

Views: 5501

Answers (1)

Pரதீப்
Pரதீப்

Reputation: 93704

That is not the right way of doing it. Instead of Exec use SP_executesqlto execute your dynamic SQL.

In SP_executesql you can declare the variable as OUTPUT parameter and you can use it outside of Dynamic query. Try something like this.

DECLARE @Count INT,
        @Tbl   VARCHAR(20)='jointest',
        @sql   NVARCHAR(max)

SET @sql = ( 'SELECT  TOP 1 @Count= COUNT(ID) FROM '+ Quotename(@Tbl) )

EXEC Sp_executesql
  @sql,
  N'@Count int output',
  @count output

DECLARE @CalRemainPage INT= @Count - ( 3 * 15 )

Upvotes: 3

Related Questions