Join together two Dynamic SQL variables

Question

Join together two Dynamic SQL variables

GBeezy

SSCarpal Tunnel

Points: 4723
More actions
September 29, 2021 at 7:07 pm

#3934782
All,
I feel like I have done this in the past, but cant remember what I did for it and even worse, I cant find the right combination of words for a good Google search. I essentially have two dynamic SQL variables and want to combine them in the execute statement.
```
DECLARE @SQL NVARCHAR(4000)
        ,@SQL1 NVARCHAR(4000);

SELECT @SQL = 'SELECT * FROM TableA'
SELECT @SQL1 = 'SELECT * FROM TableB'

EXEC sp_executesql @SQL + @SQL1
 
```
This is very simplified version of what I have. The issue is the last line with the + sign.
Like I mentioned, I feel like I have done something similar in the past.
Is this in fact possible? What am I doing wrong here?
Thank you!

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

ScottPletcher SSC Guru Points: 101118 More actions · Answer 1

You left off the parens (and something to separate the new SELECT from the old command, either a space or a semi-colon):

EXEC sp_executesql (@SQL + ';' + @SQL1)

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

GBeezy SSCarpal Tunnel Points: 4723 More actions · Answer 2

Thanks for the reply, Scott. That unfortunately is not working. It moves the "incorrect syntax" line from the plus sign to the to the first @sql. Below is what I tried:

EXEC sp_executesql(@SQL +';' + @SQL1) as well as EXEC sp_executesql(@SQL +'' + @SQL1)

Thank you

ScottPletcher SSC Guru Points: 101118 More actions · Answer 3

Oh, sorry, I didn't look closely enough, I thought it was EXEC() and not EXEC sys.sp_executesql.

For sp_executesql, you must combine code before running the proc.

DECLARE @sql NVARCHAR(4000)

,@SQL1 NVARCHAR(4000);

SELECT @sql = 'SELECT * FROM TableA'

SELECT @SQL1 = @sql + ';' + 'SELECT * FROM TableB'

EXEC sys.sp_executesql @SQL1

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

ScottPletcher SSC Guru Points: 101118 More actions · Answer 4

Just an example of concatenation when using EXEC():

DECLARE @sql1 varchar(1000)

DECLARE @sql2 varchar(1000)

SET @sql1 = 'SELECT GETDATE() AS today'

SET @sql2 = 'SELECT COUNT(*) AS object_count FROM sys.objects'

EXEC(@sql1 + ';' + @sql2)

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jeffrey Williams SSC Guru Points: 90304 More actions · Answer 5

You should also not use SELECT to set the variable - use SET instead:

DECLARE @SQL NVARCHAR(4000);

    SET @SQL = 'SELECT * FROM TableA; ';
    SET @SQL += 'SELECT * FROM TableB;';

EXECUTE sys.sp_executesql @SQL;

You can also get rid of having separate variables by using += to add to the existing variable. Another way of constructing the code - so you have something that is easy to read when you need to print it out to validate:

DECLARE @SQL NVARCHAR(4000);

    SET @SQL = '
 SELECT * 
   FROM TableA;';

    SET @SQL += '
 SELECT * 
   FROM TableB;';

EXECUTE sys.sp_executesql @SQL;

Not only will the dynamic SQL print with the format - but the code itself will be easier to read and maintain.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 6

Jeffrey Williams wrote:

You should also not use SELECT to set the variable - use SET instead:

WHY? What do you base that recommendation on?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ScottPletcher SSC Guru Points: 101118 More actions · Answer 7

Jeff Moden wrote:

Jeffrey Williams wrote:
You should also not use SELECT to set the variable - use SET instead:
WHY? What do you base that recommendation on?

I remember someone testing and saying that it was marginally more efficient to use SET vs SELECT for a single variable. Then again, that was some time ago, and, at any rate, I can't imagine it's a big performance diff.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".