CODEWITHSUNDEEP
sqlsql-servert-sqlsp-executesql
ngLucas
ngLucas

Reputation: 45

SQL - query as parameter

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

enter image description here

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

Upvotes: 1

Views: 271

Answers (2)

Sitaram Naidu Talachutla
Sitaram Naidu Talachutla

Reputation: 19

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

Upvotes: 1

M.Ali
M.Ali

Reputation: 69514

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.

Upvotes: 2

Related Questions