ADO parameterised queries and UNION statements

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

Other Development Tools

ADO parameterised queries and UNION statements

Author

Topic

CtrlAltDel
Starting Member

17 Posts

Posted - 2005-06-27 : 04:34:31

Is it possible in ADO to have a parameterised SQL statement that includes a UNION between tables? It looks like ADO.NET can, because the syntax is

SELECT * FROM table1 WHERE col1 = @col1 UNION SELECT * FROM table2 WHERE col1 = @col1

so you can a parameter thusly

parameters.Add("@col1"...);

But ADO's syntax goes like this, if I understand correctly:

SELECT * FROM table1 WHERE col1 = ? UNION SELECT * FROM table2 WHERE col1 = ?

in which case, when you add your parameter

.CreateParameter("@col1"...)

it'll populate the first parameter, but not the second automagically, because it doesn't know they are the same one. I suppose you could add it a second time, but that seems a very inefficient way of doing things. Does anyone have a better suggestion (and stored procedures are not the answer in this case - this is a very dynamic query!)

--
"Life is like a sewer - what you get out of it depends on what you put into it"; Tom Lehrer

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2005-06-27 : 12:02:47

Code is:

SELECT * FROM table1 WHERE col1 = @col1 
UNION ALL
SELECT * FROM table2 WHERE col1 = @col1

CtrlAltDel.Parameters.Append CtrlAltDel.CreateParameter("'something goes here")

Afrika

CtrlAltDel
Starting Member

17 Posts

Posted - 2005-06-28 : 10:30:55

So ADO is happy to use the same syntax as ADO.NET? All the examples I've seen have only used question marks as placeholders...

--
"Life is like a sewer - what you get out of it depends on what you put into it"; Tom Lehrer

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources