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

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 ...'

CodePudding user response：

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

CodePudding user response：

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