Execute a query string as part of a hard-coded statement

Question

I have a very long stored procedure that runs a select statement. The statement I would like to add at the end would work in a query string. Is it possible to execute a query string as part of a hard-coded statement in SQL Server?

SELECT * 
FROM INV_DATA 
WHERE 1 = 1 
  AND MY_ID = 

SET @SQL = @SQL + ' SELECT id FROM people WHERE id = 
    CASE WHEN NOT EXISTS (SELECT ... WHERE storeID = (CASE WHEN ...@ACT_METHOD = ...)) AND ...'

It doesn't look like you really need dynamic SQL. Where does `@ACT_METHOD` come from? — shawnt00, Feb 11 '22 at 20:58

score 1 · Answer 1 · edited Feb 11 '22 at 21:31

SQL Server has a built-in stored procedure that runs queries into string variables, sp_executesql.

Here's an exemple:

DECLARE @IntVariable INT;  
DECLARE @SQLString NVARCHAR(500);  
DECLARE @ParmDefinition NVARCHAR(500);  
  
/* Build the SQL string one time.*/  
SET @SQLString =  
     N'SELECT BusinessEntityID, NationalIDNumber, JobTitle, LoginID  
       FROM AdventureWorks2012.HumanResources.Employee   
       WHERE BusinessEntityID = @BusinessEntityID';  
SET @ParmDefinition = N'@BusinessEntityID tinyint';  
/* Execute the string with the first parameter value. */  
SET @IntVariable = 197;  
EXECUTE sp_executesql @SQLString, @ParmDefinition,  
                      @BusinessEntityID = @IntVariable;  
/* Execute the same string with the second parameter value. */  
SET @IntVariable = 109;  
EXECUTE sp_executesql @SQLString, @ParmDefinition,  
                      @BusinessEntityID = @IntVariable;

SQL Server sp_executesql documentation

What if the current query string would be over 8,000 characters, would Sql Server make some adjustment for that? — DCWinchester, Feb 15 '22 at 15:49
i think the problem is in your string, https://stackoverflow.com/questions/4833549/nvarcharmax-still-being-truncated try to split your string into some others and concatenate when pass to `sp`. You can create some views and stored procedures in order to reduce your "main" code. — Marcio Rocha, Feb 15 '22 at 16:18

score 0 · Accepted Answer · edited Feb 11 '22 at 21:30

It's not possible the way you are trying to do it.

Another way to achieve the same result would be:

I am assuming , the last statement you want to add to existing procedure must have some dynamic criteria but it will always return a list of IDs.
So write a dynamic SQL to select all Ids and put the result in a temp table.

Declare @sql varchar(2000) @sql = ''Select Id from ....'. -- put your query which you want to add to procedure here.

Create table #temp ( Id int)

Insert into #temp Exec(@sql)

Now use this #temp table as a join to existing select query in proc.

Select *
From INV_DATA a
Join #temp b On a MY_ID = b.id
Where 1=1

Execute a query string as part of a hard-coded statement

2 Answers2