Using SP_EXECUTESQL to execute a "dynamic" query that also contains SP_EXECUTESQL

This shows a technique that uses SP_EXECUTESQL to execute a "dynamic" query that also contains SP_EXECUTESQL.

This is FULLY parameterized - so is SQL injection SAFE!

This table contains a series of SQL statements that will be "selected" by the user. The user selects a TABLENAME - and that is used to build the SQL statement. The user also passes in a lookup value.

Note that the VARIABLE @Lookup is NOT declared in the 5 SQL statements below.

Code:

---

TableName LUType SeqNo        QueryText
Personnel Inquire  1  Declare @Sql nvarchar(max)
Personnel Inquire  2  Declare @Prm nvarchar(max)
Personnel Inquire  3  Set @Sql=N'Exec Acctfiles.dbo.frmPersonnel_Inquire @Payee,'''',1'
Personnel Inquire  4  Set @Prm=N'@Payee varchar(50)' 
Personnel Inquire  5  Execute sp_executesql @Sql, @Prm, @Payee=@Lookup

---

The "trick" here is to build the dynamic SQL, and then have a way to pass the "lookup" value into the dynamic SQL.

Below is the stored procedure that will build the dynamic SQL and pass in the LOOKUP value.

And this SPROC has the line Set @OPrm=N'@Lookup varchar(100)', which declares the variable @Lookup "as a parameter" being passed in.

And @Lookup=@Lookup works because the left-side refers to the "inner" query and the right-side to our outer scope.

Code:

---

CREATE PROCEDURE infFormInquire
        @Form varchar(100)
        ,@Lookup varchar(100)
AS
Set NoCount On
Declare @OSql nvarchar(max)
Declare @OSqlLine nvarchar(max)
Declare @OPrm nvarchar(max)
Declare @SeqNo int

Set @SeqNo=1
While Exists(Select * From AWCLookup_T Where ServerName+'.'+TableName=@Form and LUType='Inquire' and SeqNo=@SeqNo)
Begin
        Set @OSqlLine=(Select QueryText From AWCLookup_T Where TableName=@Form and LUType='Inquire' and SeqNo=@SeqNo)
        Set @OSql=IsNull(@OSql+';'+Char(13),'')+@OSqlLine
        Set @SeqNo=@SeqNo+1
End

Set @OPrm=N'@Lookup varchar(100)'
Execute sp_executesql @OSql, @OPrm, @Lookup=@Lookup

---

Fully Parameterized!