SQL - query as parameter

Question

I have code:

DECLARE @sqlQuery NVARCHAR(4000);
DECLARE @stn NVARCHAR(4000);
SET @stn=N'SELECT cast(isnull(SUBSTRING(CDN.DokSumT(16,739684,1045,1,32768,1,1,739684,1,2,1,0,78877,0,3,1,1,0,0,0,0,0,-1) , 9 , 7 ),0) as decimal (28,4))';
SELECT @sqlQuery = 'SELECT GIDNumber, Name, @stn as stn FROM Table_342 WHERE Name LIKE ''%ABLE%''';
EXEC sp_executesql @sqlQuery, N'@stn NVARCHAR(4000)', @stn;

This request returns

In column "stn" I want to have a query result not a query. How to do it ? Please help.

Answer 1

Try something like this....

DECLARE @sqlQuery   NVARCHAR(MAX) 
      , @stn        NVARCHAR(MAX)
      , @stn_R  DECIMAL(28,4);

SET @stn = N'SELECT @stn_R = cast(isnull(SUBSTRING(CDN.DokSumT(16,739684,1045,1,32768,1,1,739684,1,2,1,0,78877,0,3,1,1,0,0,0,0,0,-1) , 9 , 7 ),0) as decimal (28,4))';

EXEC sp_executesql @stn 
                 , N'@stn_R DECIMAL(28,4) OUTPUT' 
                 , @stn_R OUTPUT

SELECT @sqlQuery = 'SELECT GIDNumber, Name, @stn_R as stn FROM Table_342 WHERE Name LIKE ''%ABLE%''';

EXEC sp_executesql @sqlQuery
                 , N'@stn_R DECIMAL(28,4)'
                 , @stn_R;

Note

Your whole query is being treated as a parameter because sp_executesql only first parameter expects a SQL Statement any following parameters are either variable declarations or variable values.

You will need to split the execution of dynamic query into two and use output parameter to get the value out of first query and pass it to the second query.

Answer 2

First try with non dynamic manner. once you got..execute dynamically using EXEC(@sqlquery)