news:1193063897.500911.279560@dotnet.itags.org.v23g2000prn.googlegr oups.com...
Quote:
Originally Posted by
ConsultantID In (Select * From
dbo.CSV_IntegersToSingleColumn(@dotnet.itags.org.ConsultantIDs))
What happens if you want *all* records irrespective of ConsultantID...?
--
Mark Rae
ASP.NET MVP
http://www.markrae.netOn 22 Oct, 15:56, "Mark Rae [MVP]" <m...@.markNOSPAMrae.netwrote:
Quote:
Originally Posted by
"mark4asp" <mark4...@.gmail.comwrote in message
>
news:1193063897.500911.279560@.v23g2000prn.googlegr oups.com...
>
Quote:
Originally Posted by
ConsultantID In (Select * From
dbo.CSV_IntegersToSingleColumn(@.ConsultantIDs))
>
What happens if you want *all* records irrespective of ConsultantID...?
>
--
Mark Rae
ASP.NET MVPhttp://www.markrae.net
Mark, I don't understand your question. Is the question a reference to
the
snippet of code I wrote. I just wrote something to put the function
in context.
I should have read the OPs first post (again) than I would've given
him the
query he wanted.
The function just converts a string of CSV integers to a column so
that it
can be used in a subquery (or even a JOIN). It works with SQL Server
2005
but maybe not with 2000. It depends on whether 2000 allows functions
to return a table. I suspect it doesn't. Play around with it. I've
never had
a problem with it in practice.
"mark4asp" <mark4asp@.gmail.comwrote in message
news:1193067457.364145.28130@.z24g2000prh.googlegro ups.com...
Quote:
Originally Posted by
Mark, I don't understand your question.
Supposing you have a stored procedure which takes the "array" parameter -
it's impossible to nullify this without writing dynamic SQL in the SP...
E.g. supposing you have a table with 1,000 rows, and the second column is an
integer called StatusID.
You write a stored procedure with an "array" parameter containing one or
more StatusID values in a CSV string.
1,9,13 returns all the rows where the StatusID is 1, 9 or 13 - obviously.
But what if you want *all* the rows? You can't pass a NULL value into the
"array" parameter without writing some dynamic SQL in the stored
procedure...
You could pass in an array containing all the StatusID values, I suppose,
but that would be extremely inefficient...
--
Mark Rae
ASP.NET MVP
http://www.markrae.net
"mark4asp" <mark4asp@.gmail.comwrote in message
news:1193067457.364145.28130@.z24g2000prh.googlegro ups.com...
Quote:
Originally Posted by
On 22 Oct, 15:56, "Mark Rae [MVP]" <m...@.markNOSPAMrae.netwrote:
Quote:
Originally Posted by
"mark4asp" <mark4...@.gmail.comwrote in message
news:1193063897.500911.279560@.v23g2000prn.googlegr oups.com...
Quote:
Originally Posted by
ConsultantID In (Select * From
dbo.CSV_IntegersToSingleColumn(@.ConsultantIDs))
What happens if you want *all* records irrespective of ConsultantID...?
--
Mark Rae
ASP.NET MVPhttp://www.markrae.net
>
Mark, I don't understand your question. Is the question a reference to
the
snippet of code I wrote. I just wrote something to put the function
in context.
I should have read the OPs first post (again) than I would've given
him the
query he wanted.
>
The function just converts a string of CSV integers to a column so
that it
can be used in a subquery (or even a JOIN). It works with SQL Server
2005
but maybe not with 2000. It depends on whether 2000 allows functions
to return a table. I suspect it doesn't. Play around with it. I've
never had
a problem with it in practice.
That function works fine in SQL 2000. Personally I prefer the XML approach
since it has wider application but XML is slower and awkward under 2000.
--
Anthony Jones - MVP ASP/ASP.NET
"Anthony Jones" <Ant@.yadayadayada.comwrote in message
news:ubvnIDVFIHA.1388@.TK2MSFTNGP05.phx.gbl...
Quote:
Originally Posted by
Personally I prefer the XML approach since it has wider application
Me too...
--
Mark Rae
ASP.NET MVP
http://www.markrae.net
0 comments:
Post a Comment