SQL UNION Operator

Hi Expert,

Code is here

SET @SQL_MAIN = @SQL_MAIN + @SQL_1

EXECUTE SP_EXECUTESQL @SQL_MAIN

----------SQL_MAIN  is main query, @SQL_1 inner join table A, @SQL_2 inner join table B

I want Union  @SQL_2, how to do it?

thanks in advance
LVL 1
dshi15Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
Advertise Here
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
that looks fine, actually, in the basics, so it should work
the question is: why you want/need dynamic sql.
with CTE, you would avoid dynamic sql which may give you issues at other levels.

;WITH main AS ( Select * from tableA inner join table B ON ... )
SELECT * from main join table_c on  ...
UNION ALL
SELECT * from main join table_d on  ...

Open in new window

0
 
lcohanConnect With a Mentor Database AnalystCommented:
I suggest you rather use CTE instead of @sql2 - just put all that query or even both in CTE and use them in a UNION afterwards.

http://msdn.microsoft.com/en-us/library/ms190766(v=sql.105).aspx
here's one with UNION ALL but you can just drop the ALL

http://stackoverflow.com/questions/8985609/union-all-in-cte

more complicated recursive CTE examples here:
http://stackoverflow.com/questions/3159192/sql-server-2008-cte-recursion
0
 
dshi15Author Commented:
Why  "use CTE instead of @sql2" ?

in my query string, I pass a lot of variable to the query.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
as it's not clear (to me) what you want to achieve, can you please show examples of the values of @main, @sql1, @sql2 and the requested end result
0
 
dshi15Author Commented:
@main=select * from table A inner join table B

@sql1= @main + " inner join table C"

@sql2= @main + " inner join table D"

Something like that.

I got Union All and result correct, i don't know why use CTE? any better?

Thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.