How to use sp_executesql for dynamic sql which is stored in sql table

1

how to use sp_executesql for dynamic sql which is stored in sql table

2

Here is an example of how you would do it. That said, don't do it. It is very vulnerable to SQL injection attacks. If you can describe your end goal, people on this forum might be able to suggest safer/better alternatives.

CREATE TABLE #tmp (QueryString NVARCHAR(4000));
INSERT INTO #tmp VALUES ('SELECT * FROM sys.tables')

DECLARE @x NVARCHAR(4000);
SELECT TOP (1) @x = QueryString FROM #tmp;

EXEC sys.sp_executesql @x;
3

WITH PARAMETER NOT WORKING
I have executed following statment
DECLARE @statement NVARCHAR(4000)
DECLARE @parameterDefinition NVARCHAR(500)
SET @statement = (SELECT LTRIM(SQLCODE) FROM [Rule].[SQLCodeBase] SB )
SET @parameterDefinition = N'@Bid int'
execute @i= sp_executesql @statement, @parameterDefinition, @Bid=@BatchID
i got @i=0 value but this @statement not executed

4

The return code from sp_executesql is zero if the job was successful, and non-zero on failure. So @i being zero simply means the query ran successfully.

If you want to get the output from the result of the query, you have to use an OUT parameter. There is a very specific way for doing this. Look at this page, and in particular the example code that I have copied below from that page. Here, @max_titleOUT is the parameter that returns the desired result. Notice how the OUTPUT keyword is specified both in @ParmDefinition and in sp_executesql call for that parameter.

DECLARE @IntVariable int;  
DECLARE @SQLString nvarchar(500);  
DECLARE @ParmDefinition nvarchar(500);  
DECLARE @max_title varchar(30);  
  
SET @IntVariable = 197;  
SET @SQLString = N'SELECT @max_titleOUT = max(JobTitle)   
   FROM AdventureWorks2012.HumanResources.Employee  
   WHERE BusinessEntityID = @level';  
SET @ParmDefinition = N'@level tinyint, @max_titleOUT varchar(30) OUTPUT';  
  
EXECUTE sp_executesql @SQLString, @ParmDefinition, @level = @IntVariable, @max_titleOUT=@max_title OUTPUT;  
SELECT @max_title;
5

what is the business case for having these queries embedded in a sql table?

6

CREATE TABLE SalesDetail
(ID INT IDENTITY(1,1) ,
ITEMNO INT,
AMOUNT MONEY,
BatchID INT
)
GO
INSERT SalesDetail(ITEMNO,AMOUNT,BatchID )
VALUES (1,50.00,1)
INSERT SalesDetail(ITEMNO,AMOUNT,BatchID )
VALUES (2,70.00,1)
INSERT SalesDetail(ITEMNO,AMOUNT,BatchID )
VALUES (3,90.00,1)
GO
CREATE TABLE SQLCODE
(ID INT IDENTITY(1,1) ,
CODE NVARCHAR(MAX)
)
GO

----STEP1 EXECUTE STATAMENT FROM DIRECT QUERY IS UPATING THE SALESDETAIL AMOUNT
DECLARE @SQLCODE NVARCHAR(1000)
DECLARE @statement NVARCHAR(400)
DECLARE @parameterDefinition NVARCHAR(400)
DECLARE @BID INT=1
DECLARE @i int
SET @SQLCODE='UPDATE SALESDETAIL SET AMOUNT=50 WHERE Batchid=@batchid'
SET @statement =@SQLCODE
SET @parameterDefinition = N'@batchid int'
execute @i= sp_executesql @statement, @parameterDefinition, @BatchID=@BID

print @i

--IN THE MESSAGE WINDOW

(3 rows affected)
0
---ABOVE STEP IS WORKING
----STEP2 EXECUTE STATAMENT FROM SQL TABLE IS NOT UPATING THE SALESDETAIL AMOUNT

DECLARE @statement NVARCHAR(400)
DECLARE @parameterDefinition NVARCHAR(400)
DECLARE @BID INT=1
DECLARE @i int
SET @statement = (SELECT [code] FROM [dbo].[SQLCODE])
SET @parameterDefinition = N'@batchid int'
execute @i= sp_executesql @statement, @parameterDefinition, @BatchID=@BID

print @i

--IN THE MESSAGE WINDOW
0
STEP2 NOT UPDATING THE AMOUNT THIS IS THE PROBLEM

7

There is no reason why storing sql code in a table, pulling it out and executing it shouldn't work; whether that is a good idea (as per JamesK & yosiasz) is a different conversation

Looking at your "demo" code the one thing missing is any actual data in the SQLCODE table to execute

Try the following

use tempdb;

DROP TABLE IF EXISTS #wibble
DROP TABLE IF EXISTS #code

-- table to hold "code"
create table #code (
	code varchar(100)
)

-- dummy table with some data
CREATE TABLE #wibble (
	wobble VARCHAR(10)
)
INSERT INTO #wibble 
SELECT * FROM (VALUES ('some'), ('data')) as v(v)

-- set up variables requried
DECLARE @SQL NVARCHAR(100) = 'SELECT * FROM #WIBBLE'
DECLARE @RC INTEGER
-- SQL parameters
DECLARE @parameterDefinition NVARCHAR(400) = N'@batchid int'
DECLARE @BID INT=1

-- run sp_execute sql with just variables
-- OUTPUT, 2 rows
EXEC @RC =  SP_EXECUTESQL @SQL
SELECT @RC

-- prove #code is empty
-- OUTPUT, 0 rows
SELECT * FROM #code

-- get NON existent code from #code and execute
-- OUTPUT 0 rows
SELECT @SQL = (SELECT code from #code)
-- prove @sql is blank
SELECT @SQL AS '@sql is empty'
-- EXECUTE
EXEC @RC =  SP_EXECUTESQL @SQL
SELECT @RC

-- same again but with (spurious) parameters attached, same 0 row output
EXEC @RC =  SP_EXECUTESQL @SQL, @parameterDefinition, @BatchID=@BID
SELECT @RC

-- actually populate #code
INSERT INTO #code
SELECT 'SELECT * FROM #WIBBLE'

-- PROVE #code has ... er ... code
SELECT * FROM #code

-- now get code from #code and execute
-- OUTPUT, 2 rows
SELECT @SQL = (SELECT code from #code)
-- prove @SQL has "code"
SELECT @SQL AS '@sql is not empty'
-- execute 
EXEC @RC =  SP_EXECUTESQL @SQL
SELECT @RC

-- and again, with parameters, 2 rows
EXEC @RC =  SP_EXECUTESQL @SQL, @parameterDefinition, @BatchID=@BID
SELECT @RC
8

thanks a lot uberbloke
your code is working fine
i have used two single quote in my dynamic sql because of that not executing
now i corrected my dynamic sql is working fine