It Should Print 12 but the output is NULL , Please tell me How to SET VALUE For the VAriable declare outside the SQL Text cretaed for dynamic query

[Lost User]

<pre lang="vb">DECLARE @CheckQuantity INT
SET @ParmDefinition = N'@CheckQuantity INT'
SET @SQL = N'
SET @CheckQuantity= 12'
PRINT @SQL

        EXEC \[dbo\].sp\_executesql @SQL, @ParmDefinition,
        @CheckQuantity=@CheckQuantity;
        PRINT @CheckQuantity</pre>

It Should Print 12 but the output is NULL , Please tell me How to SET VALUE For the VAriable declare outside the SQL Text cretaed for dynamic query

[Lost User]

any one who has solution for this

[Richard Deeming]

You need to make the parameter an OUTPUT parameter. https://support.microsoft.com/kb/262499[^]

DECLARE @CheckQuantity INT;
SET @ParmDefinition N'@CheckQuantity INT OUTPUT';
SET @SQL = N'SET @CheckQuantity= 12';
PRINT @SQL;

EXEC [dbo].sp_executesql @SQL, @ParmDefinition, @CheckQuantity = @CheckQuantity OUTPUT;
PRINT @CheckQuantity;

---

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

[Lost User]

Thank you friend it's working now