Populate temp variable within stored procedure using dynamic sql

Question

I have a stored procedure where I am declaring an int variable that needs to be populated using a dynamic sql

CREATE PROCEDURE USP_aTABLE_ADD
/*

    stored procedure variables

*/
AS

     DECLARE @count int
     SET @count = 1
     DECLARE @qry nvarchar(max)

/*
     SET UP @qry which will look like this
     SELECT @count = count(*) FROM aTABLE WHERE (col1 = 'val1' AND col2 = 'val2'...)
*/


/*
     How to get the value of @count so that I can continue with add process
*/

IF @count = 0
BEGIN
   /*add logic*/
END

Looks like you're already selecting the value of count(*) into @count, so what's the problem? — Gareth
– Gareth, Commented Feb 27, 2014 at 12:11

Alex K. · Accepted Answer · 2021-02-01 14:54:39Z

5

Use sp_executeSQL and an output parameter:

DECLARE @count INT = 1
DECLARE @qry   NVARCHAR(MAX)

SET @qry = N'set @count = (....)'

EXEC sp_executesql @qry, N'@count INT OUTPUT', @count OUTPUT
    
SELECT @count

edited Feb 1, 2021 at 14:54

answered Feb 27, 2014 at 12:10

Alex K.

177k32 gold badges276 silver badges299 bronze badges

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

Comments

Thorsten Dittmar · Accepted Answer · 2014-02-27 12:17:11Z

2

You could try and work with a temporary table like this

DECLARE @tmp TABLE(cnt INT)
DECLARE @qry nvarchar(max)

-- Insert the count returned by dynamic SQL into temp table
SET @qry = 'SELECT COUNT(*) FROM Table WHERE condition'
INSERT INTO @tmp EXEC(@qry)

DECLARE @count INT
SET @count = (SELECT TOP 1 cnd FROM @tmp)

answered Feb 27, 2014 at 12:17

Thorsten Dittmar

56.9k10 gold badges95 silver badges145 bronze badges

Collectives™ on Stack Overflow

Populate temp variable within stored procedure using dynamic sql

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