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

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;