Assign db query value to t-sql variable in dynamic query

Question

I have this requirement to be implemented in a stored procedure. Dynamically query the database to get the count of a table, store it in a t-sql variable and then take some decisions based on that.

This is the stored procedure that i am working on . This is throwing some errors as i don't think there is a simple way of assigning the result of a tsql dynamic query to a variable.

CREATE PROCEDURE test
AS
BEGIN
 DECLARE @sql VARCHAR(255)
 DECLARE @cnt int
 SET @sql = 'SELECT COUNT(1) FROM myTable'
 SET @cnt = EXEC(@sql)
 IF (@cnt > 0)
     PRINT 'A'
 ELSE
     PRINT 'B'
END
GO

Could someone tell me if there is a simpler way of achieving this using T-SQL ?

Thanks.

Alex K. · Accepted Answer · 2011-09-29 11:31:05Z

6

alternative:

declare @tablename varchar(512) = 'sometable'
declare @sql nvarchar(512) = 'set @count = (select count(*) from ' + @tablename + ')'
declare @count int

execute sp_executesql @sql, N'@count int output', @count=@count output

select case when @count > 0 then 'A' else 'B' end

answered Sep 29, 2011 at 11:31

Alex K.

177k32 gold badges276 silver badges299 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Asdfg · Accepted Answer · 2011-09-29 11:27:43Z

0

Try this:

 SET @sql = 'SELECT @cnt = COUNT(1) FROM myTable'
 EXEC(@sql)

answered Sep 29, 2011 at 11:27

Asdfg

12.4k26 gold badges110 silver badges188 bronze badges

Collectives™ on Stack Overflow

Assign db query value to t-sql variable in dynamic query

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related