Join together two Dynamic SQL variables

Question

Post reply

Join together two Dynamic SQL variables

GBeezy

SSCarpal Tunnel

Points: 4639
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: 100262 More actions · Answer 1

ScottPletcher

SSC Guru

Points: 100262

September 29, 2021 at 7:09 pm

#3934783

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) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

GBeezy SSCarpal Tunnel Points: 4639 More actions · Answer 2

GBeezy

SSCarpal Tunnel

Points: 4639

September 29, 2021 at 7:16 pm

#3934784

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: 100262 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) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

ScottPletcher SSC Guru Points: 100262 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) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

Jeffrey Williams SSC Guru Points: 89773 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
Problems are opportunities brilliantly disguised as insurmountable obstacles.

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

Jeff Moden SSC Guru Points: 1000539 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)
Intro to Tally Tables and Functions

ScottPletcher SSC Guru Points: 100262 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) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!