CODEWITHSUNDEEP
sql-servert-sqldynamic-sql
Sumeet Kumar
Sumeet Kumar

Reputation: 347

How To Get Value of Variable From Dynamic Query

DECLARE
    @id int = NULL, @district int = 12, @zone int = 48,
    @str nvarchar(max) = NULL, @sanctioned int = 0 

CREATE TABLE #runningTable
(
    postid int NULL,
    postname varchar(50) NULL,
    sanction int NULL DEFAULT 0,                           
    surplus int NULL DEFAULT 0,
    inposition int NULL DEFAULT 0,
    aar int NULL DEFAULT 0,
    vacant int NULL DEFAULT 0
)
                         
SET @str = 'select @sanctioned = (SUM('+@value+')) FROM staff_strength_details a 
                                            INNER JOIN school_master b ON b.udisecode = a.udisecode
                                            WHERE CONVERT(varchar, b.district_id) = ''' + CONVERT(varchar, @district) + ''' AND 
                                            CONVERT(varchar, b.zone_id) = '''  + CONVERT(varchar, @zone) + '''
                                                            
                                            @sanctioned int output
                                                                        '
EXEC sp_executesql @str, @sanctioned = @sanctioned OUTPUT

UPDATE #runningTable 
SET sanction = @sanctioned 
WHERE postid = @id

SELECT * FROM #runningTable

DROP TABLE #runningTable

In above query, I have declared a @sanctioned variable and need to get the sum of dynamic column in this variable. Then than variable need to be updated in the dynamic table #runningTable. But on executing the above query I get an error

Procedure expects parameter '@params' of type 'ntext/nchar/nvarchar'

Therefore I have declared @str as nvarchar but I still get the same error.

How should I get the value of declared variable @sanctioned?

Upvotes: 0

Views: 585

Answers (1)

George Joseph
George Joseph

Reputation: 5932

Here is a small example of how to use dynamic sql. It accepts one variable as input to the dynamic string, and outputs a single value sum(status) into the variable @l_out1

You may modify this example to suit what you require. In you example, i could find you have a statement sum(@value),however @value is not defined.

declare @str       nvarchar(4000)
declare @l_out1    int
declare @l_status1 int =1


set @str='select @l_out=sum(status) ' 
         + 'from master..spt_values '
         + 'where status=@l_status '

exec sp_executesql @str
                   ,N'@l_status INT,@l_out INT OUTPUT'
                   ,@l_status=@l_status1
                   ,@l_out=@l_out1 output

select @l_out1 as 'col1'

Working example

https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=4ebab535a4970f478ce22547e5dd8150

Upvotes: 2

Related Questions