March 30, 2008 at 7:46 pm
Hello all,
I am having a difficult time trying to get this to work. I want to be able to pass multiple values into a parameter(?) but I can't seem to get the syntax correct. I'm not sure if this is even possible.
I listed a simple example below of what I'm trying to do.
use northwind
declare @IdList int
set @IdList = (1,25,33,64,77)
Select * from products
where productid in (@IdList)
Ulitmately, I would like to get the same results from the above query that I would with the query listed below.
use northwind
Select * from products
where productid in (1,25,33,64,77)
In this example I want to pass a list of IDs that are integers but I want to figure out how to get this work no matter what type of value it is.
For example I may want to search in the "ProductName" filed for ('Chai','NuNuCa Nuß-Nougat-Creme','Geitost','Wimmers gute Semmelknödel','Original Frankfurter grüne Soße')
use northwind
declare @ProductList varchar (50)
set @ProductList = ('Chai','NuNuCa Nuß-Nougat-Creme','Geitost','Wimmers gute Semmelknödel','Original Frankfurter grüne Soße')
Select * from products
where productid in (@ProductList )
Either way I can't figure out the proper way to do it. Any help would be appreciated.
Thanks!
March 30, 2008 at 9:03 pm
1st you need to assign the variable value correctly.
Try this:
use northwind
declare @ProductList varchar (50)
set @ProductList = ('Chai','NuNuCa Nuß-Nougat-Creme','Geitost','Wimmers gute Semmelknödel','Original Frankfurter grüne Soße')
--unchanged so far
select @ProductList
_____________
Code for TallyGenerator
March 30, 2008 at 9:15 pm
You want to look at dynamic SQL for this.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 30, 2008 at 10:14 pm
What you want to do is not directly possible. However, as Young says you can get the results with dynamic sql. You can also get them without dynamic sql by
declare @vals varchar(20)
set @vals = '1,22,333,4444'
then parse @vals into a table variable or temp table (
http://www.sqlservercentral.com/Forums/Topic457395-338-1.aspx
has 2005 code for this and a link to another article with a SQL2K-ok parser)
and join the table var/temp table
select *
from products p
join @tbl t on p.id = t.id
March 31, 2008 at 9:34 pm
ksullivan (3/30/2008)
What you want to do is not directly possible.
Heh... not quite true... 😉 And the Tally Table method always wins the race when it comes to this type of split...
USE Northwind
DECLARE @IdList VARCHAR(8000)
SET @IdList = '1,25,33,64,77'
SELECT *
FROM Products p,
(--==== Derived table "s" returns split values
SELECT Val = SUBSTRING(','+@IdList+',', t.N+1, CHARINDEX(',', ','+@IdList+',', t.N+1)-t.N-1)
FROM dbo.Tally t
WHERE SUBSTRING(','+@IdList+',', t.N, 1) = ','
AND t.N < LEN(','+@IdList+',')) s
WHERE p.ProductID = s.Val
Dunno what a Tally Table is? Please visit the following URL...
http://www.sqlservercentral.com/scripts/Advanced+SQL/62486/
--Jeff Moden
Change is inevitable... Change for the better is not.
March 31, 2008 at 9:39 pm
Thanks all for the suggestions.
Sergiy, I was able to assign the values by using your example but I still couldn't pass them into the query.
rbarryyoung, I didn't know much about dynamic sql, so I found a couple of tutorials online. I wish I would have learned about dynamic sql sooner. I ended up going that route. Here's what I came up with:
use northwind
declare @IdList varchar (50)
select @IdList = '1,25,33,64,78'
declare @sql varchar(100)
select @sql = 'Select * from products '
select @sql = @sql + 'WHERE ProductID in (' + @IdList +')'
exec (@sql)
--print @sql
ksullivan, I will check out the other posts. I would like to learn how do this without using the dynamic sql.
Thanks again!
March 31, 2008 at 9:41 pm
Jeff, I must have just missed your reply. I will read up on the tally table. thanks!
March 31, 2008 at 10:09 pm
The only thing that can be faster than the Tally Table is Dynamic SQL... but that's not a split and it does have some security issues if it's public facing.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 31, 2008 at 10:24 pm
Jeff Moden (3/31/2008)
The only thing that can be faster than the Tally Table is Dynamic SQL... but that's not a split and it does have some security issues if it's public facing.
I created a tally table using the script from the tally table article. I ran the code you posted. It works great! I have to admit at first I a hard time understanding how that script does what it does. After I ran the derived query alone, I think I have a better idea now how a tally table works. It's very interesting, I'm excited to see what else I can use the tally table for. Thanks!
March 31, 2008 at 10:33 pm
You'd be amazed what it can do... as you've seen, it'll do splits. It'll also fill in missing dates, generate multiple rows for each row according to a "QTY", can be used to make a "Proper Case" function, can be used to remove unwanted data (ie: remove Alpha charaters from mixed characters), make time/day bins for reporting, generate test data (millions of rows if you want it), etc, etc, etc. I think you can even get it to pour you a drink if you work at it a bit 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
April 1, 2008 at 11:05 am
Another suggestion: Enter in a working table the list of your productid's.
Example:
CREATE TABLE myTempSelectionList
. (
. pk INT NOT NULL PRIMARY KEY bigint,
. IntegerID INT NULL,
. StringID VARCHAR(20) NULL, -- IF YOU WANT TO RETRIEVE FROM A NAME
. OwnerID VARCHAR(38)
. )
The "Temp" in "myTempSelectionList" means that the information lifespan is very limited. The table is a permanent table of the database, not a # temp table private to the connection.
Thus, the selection list is available for stored procedures or other SQL calls from your application.
Follow up with:
[font="Courier New"]DECLARE @ls_OwnerID VARCHAR(38)
SET @ls_OwnerID = NEWID()
INSERT INTO myTempSelectionList (IntegerID , OwnerID) -- snippet #1
SELECT productid, @ls_OwnerID
FROM products
WHERE the records match the selection criteria (how you got your list of productid's)[/font]
@ls_OwnerID allows sharing the same permanent table without interference from concurrent users.
And, AT LAST:
Select * from products
where productid in (@IdList)
Becomes
[font="Courier New"]SELECT PR.* -- snippet #2
FROM myTempTable S
INNER JOIN products PR ON (PR.ProductID = S.IntegerID)
WHERE (S.OwnerID = @ls_OwnerID)
DELETE FROM myTempSelectionList
WHERE OwnerID = @ls_OwnerID[/font]
While this is not the most performing way of doing things, the selection is available to any other process. For instance, bulk printing of the product specification sheet for each of your articles. For instance, you can paste snippet #2 in a stored procedure and set this sp as the data source for a report using Crystal Reports.
snippet #1 is however you managed to produce your comma-delimited list of productid's.
April 1, 2008 at 11:36 am
Are you going to do that from a GUI where parameters are normally passed as CSV's?
--Jeff Moden
Change is inevitable... Change for the better is not.
April 1, 2008 at 2:15 pm
Jeff Moden (4/1/2008)
Are you going to do that from a GUI where parameters are normally passed as CSV's?
Er, is the question addressed to "Rags" or to "J" ?
April 1, 2008 at 2:25 pm
J (4/1/2008)
Jeff Moden (4/1/2008)
Are you going to do that from a GUI where parameters are normally passed as CSV's?Er, is the question addressed to "Rags" or to "J" ?
Sorry... I meant "J"...
--Jeff Moden
Change is inevitable... Change for the better is not.
April 1, 2008 at 2:28 pm
Rags (3/31/2008)
Jeff Moden (3/31/2008)
The only thing that can be faster than the Tally Table is Dynamic SQL... but that's not a split and it does have some security issues if it's public facing.I created a tally table using the script from the tally table article. I ran the code you posted. It works great! I have to admit at first I a hard time understanding how that script does what it does. After I ran the derived query alone, I think I have a better idea now how a tally table works. It's very interesting, I'm excited to see what else I can use the tally table for. Thanks!
By the way, Rags... outstanding that you're taking the code apart! I love to see people with "intellectual curiosity"... rare thing now adays. Lots of folks would just "black box" the code and be on their merry way learning nothing but how to black box code on the way. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 31 total)
You must be logged in to reply to this topic. Login to reply