How to modify dynamic query in SQL Server?

Question

I have a table which stores SQL queries. I am reading that and executing it by using EXECUTE sp_executesql. I was wondering that, if I want to modify this query, how can I do that?

If I have to add a variable in the end, I can do something like this:

DECLARE @sql1 NVARCHAR(MAX), @var NVARCHAR(20)

SET @sql1 = (SELECT Query FROM Table WHERE ID = 123)
SET @var = ' AND CONDITION2'

PRINT @sql1 + @var

Result:

UPDATE Table SET VALUE1 WHERE CONDITION1 AND CONDITION2

But if I want to add something in between, I'm not sure how to do that.

Query:

DECLARE @sql NVARCHAR(MAX)
SET @sql = (SELECT Query FROM Table WHERE ID = 123)
PRINT @sql

Actual result:

UPDATE Table SET VALUE1 WHERE CONDITION1

Expected:

UPDATE TOP (100) Table SET VALUE1 WHERE CONDITION1

Without some actual details nobody else knows how to do this either. Mostly because we have no idea what you are trying to do. You should start with table definitions and sample data. Then include what you are trying to do. — Sean Lange
– Sean Lange, Commented May 20, 2016 at 19:19

Juan Carlos Oropeza · Accepted Answer · 2016-05-20 19:19:57Z

3

Just change your original query to include a placeholder

 SET @sql =  'UPDATE {0} Table SET VALUE1 WHERE CONDITION1';
 SET @top_condition = 'TOP(100)';
 SET @sql = REPLACE(@sql, 
                    '{0}', 
                    CONVERT(varchar(max), @top_condition)
                   );

You also can remove the top option if you want it

 SET @top_condition = '';

answered May 20, 2016 at 19:19

Juan Carlos Oropeza

48.4k14 gold badges87 silver badges128 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

GThree Over a year ago

This is exactly what I wanted.

Collectives™ on Stack Overflow

How to modify dynamic query in SQL Server?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related