T-Sql: How to Use Parameters in Dynamic SQL

T-SQL: How to use parameters in dynamic SQL?

You must pass in the parameters to sp_executesql. See MSDN for details.

...
 WHERE 
    CreatedBy = @p
...

EXECUTE sp_executesql @sql, N'@p UNIQUEIDENTIFIER', @p = @p_CreatedBY

SQL Server 2017 - How to pass a parameter in a SELECT inside a dynamic SQL

when you do:

set @sql = '
select ' + @Name + ' = colName from dbo.TestABC where colID = ''3''
'

the actual value inside variable @Name is null. And according to sql server logic string + null is null.

to do what you want you need:

DECLARE @sql nvarchar(max)
DECLARE @Name nvarchar(max)
set @sql = '
select @someVariable = colName from dbo.TestABC where colID = ''3''
'
EXECUTE sp_executesql @sql, N'@someVariable varchar(max) OUTPUT', @someVariable = @Name OUTPUT;
select @Name; -- this will print your colName

and do not use (max) as your default its a bad practice and might slow your queries down.

Dynamic SQL with parameters

-- 50 is too short for sure; you may try 1000 or different number
DECLARE @SQLQUERY AS NVARCHAR(MAX)

-- for debug purpose
PRINT @SQLQUERY

-- params
EXEC SP_EXECUTESQL @SQLQUERY, N'@Value NVARCHAR(50), @sku NVARCHAR(50)`, @Value, @sku

REPLACE is not good in case of strings with quotes and so on which would brake the @sqlquery code.

Assign result from dynamic SQL in a parameter to another parameter

You can use parameters with sp_executesql

DECLARE @emailParm NVARCHAR(1000) 
DECLARE @emailOut NVARCHAR(MAX)
SET @emailParm = N'SELECT COALESCE(@email+'','' ,'''') + '''''''''''' + EMAIL + '''''''''''' 
FROM (SELECT DISTINCT EMAIL FROM ' + @PolicyTempTable + ') d'

EXEC sp_executesql @emailParm, N'@email VARCHAR(1000) OUTPUT', @email = @emailOut OUTPUT

Then you can build your second dynamic sql

DECLARE @StrSQLEmail VARCHAR(8000)
SET @StrSQLEmail = 'SELECT * FROM OPENQUERY(ATOM,''Select * from ATOMS.EMAILS WHERE EMAIL IN (' + @emailOut + ')'')'

How to use Table -Valued Parameter with Dynamic qry

Inside dynamic query, you cannot use table variables declared outside. Use temp table instead. Also you have complicated it little too much, here is a cleaner version

DECLARE @SyncFlag AS NVARCHAR(1) ='S'
DECLARE @SelectCommand AS NVARCHAR(1200)

CREATE TABLE #Details_N(Pk INT)

INSERT INTO #Details_N(Pk)
SELECT PK
FROM   @Details

SET @SelectCommand = 'Update A ' + ' set A.Sync_Flag = @SyncFlag 
                        From '+ Quotename(@tblName) + ' A 
                        inner join #Details_N B '+ 'on A.PK =' + 'B.PK'

EXEC Sp_executesql
  @SelectCommand,
  N'@SyncFlag NVARCHAR(1)',
  @SyncFlag 

Start using INNER JOIN syntax, old style comma separated join is deprecated

Convert string from column to parameter in dynamic SQL?

While agreeing with the issue of dynamic prone to injection attacks, and assuming some random values for your non initialized variables, here is how I approach

The trick is

SELECT @Condition = REPLACE(@Condition, '@FieldName', @FieldName )
SELECT @Condition = REPLACE(@Condition, '@CustName', @CustName )

So the main Query will be

declare 
    @TableName as nvarchar(10) = 'MyTbl',
    @FieldName as nvarchar(20) = 'FldName',
    @CustName as Nvarchar(50) = 'C1' ,
    @Condition as NVARCHAR(MAX) 
    
SELECT @Condition = (SELECT o.Condition FROM CustomerConditions o WHERE o.[Group] = @CustName)
SELECT @Condition = REPLACE(@Condition, '@FieldName', @FieldName )
SELECT @Condition = REPLACE(@Condition, '@CustName', @CustName )

declare @strSQL as NVARCHAR(MAX)
SET @strSQL = 'DECLARE @FieldName as nvarchar(20),       
@CustName as nvarchar(50)
;WITH NewCTE AS (
                    SELECT Tab1.Group, '+@FieldName+'
                    FROM (
                   SELECT
                    '+@Condition+' 
                    '+@FieldName+'
                    FROM '+@TableName+' as c) as Tab1)
            
SELECT * FROM NewCTE'

Select(@strSQL)

First provide proper values to your variables and execute this.
Instead of executing the query, what I did was create the query to see whether it got created as you want.

To be certain, you can copy paste the outcome and execute that.
Then you can change the last line to Execute ...

Safely execute a dynamic sql query

Although I have covered much of this in the comments, I felt it worthwhile giving an answer to give more of an explanation.

Firstly, as I have mentioned, this isn't a route you should be going down. Yes, you can have procedures that do use dynamic SQL, but these shouldn't be handling such basic things as inserting data into a table, or updating said data.

When using dynamic SQL, you need to first ensure that you are properly quoting your dynamic objects. For this that isn't too hard, you can just have a parameter for the object's schema and name and then when you inject them wrap them in QUOTENAME. The real problem comes from the latter, the "dynamic" columns.

Firstly, you seem to want a dynamic number of parameters; this is a major problem. You can't trivially, or even easily, parametrise dynamic parameters. You won't be able to pass these parameters as their correct type either; you wouldn't be able to pass a date as a date for example. I can imagine a solution that uses dynamic dynamic SQL (yes, I said dynamic twice), and the sql_variant object type, but should you be doing that? No. If you understood how to maintain such a solution I don't for one second think you would have asked the question you have; you would have something that is on it's way there, but needed some help.

So, what is the solution? Well, again, like I said in the comments, you should have separate procedures for each table. You might also want separate ones for INSERT and UPDATE operations, but you could also use a single one and implement "UPSERT" logic; there is plenty of good articles on how to do this so I won't cover it here.

As I mentioned in the comments as well, that means updating your procedures when you update your objects. That is normal. I routinely update procedures when an underlying table is updated to have more columns.

At the same time your application developers will then need to update their application code to ensure that pass the new parameters to your procedure. Good devops and relationships between your DBAs, SQL Developers and Application Developers is key, but that's all. Keep those communication channels open and active. When you or your DBA alters the table, adding the new column(s) and amended the objects indexes (if needed) in your development environment, and has informed you the SQL developer, you can then ALTER the needed procedures. You can then inform the Application Developer, and they can update the application code.

After that, complete your internal testing, fixe any bugs/unexpected behaviour/performance issues and then progress to the test environment. Get your users to confirm it works as required, and then off it goes to production. In other words, follow the basics of a good develop cycle.

---

TL;DR: The route you want is wrong, and is never going to scale. Stick to a normal development cycle, and update your database and application code in sync so that new functionality can be provided.

Using a pre-declared variable in another variable declaration in SQL

You can use EXEC sp_executesql and pass variable like below.

DECLARE @FRUIT VARCHAR(10) = 'apple'

-- Use @FRUIT itself and not its value
DECLARE @sql3 NVARCHAR(MAX) = 'select @FRUIT'    

EXEC sp_executesql @sql3, N'@FRUIT VARCHAR(10)', @FRUIT

---

Alternatively if you want to use only value from that variable and don't want to pass variable to exec then try wrapping your @FRUIT value with single quote. You will need to write ' twice for escape sequence. Try like below.

DECLARE @FRUIT VARCHAR(10) = 'apple'

-- Wrap value of @FRUIT with ''
DECLARE @sql3 NVARCHAR(MAX) = CONCAT('select ''', @FRUIT, '''') 

EXEC(@sql3)

---

Related Topics