How to pass Variable to an IN statement in a stored procedure
-
Hello,
I have written a stored procedure that has an in statement. I would very much like to make that in statement a variable and pass the list as a variable. Can this be done?? Can anyone help?
From Outside SQL - VB.Net
@MyVariable = "'MSFT','IBM','Hello World'"
EXEC MyStoredProcedure '08/31/07',@MyVariable
Create Procedure sp__MyProcedure(
@ReportDate datetime,
@Ticker varchar(70)=NULL
)as
Select * from MyTable MT
WHERE MT.Code IN (@Ticker)
-
Search this forum for "split function".
Choose one of them and use like this:
Select *
from MyTable MT
INNER JOIN dbo.ChosenSplitFunction (@Ticker) V ON MT.Code = V.Value
_____________
Code for TallyGenerator -
Serqiy is spot on... a split function will make this easy and quite fast even without indexes. But... before we can make a decent split funtion, we need a little utility table that has dozens of uses and should probably become a part of your SQL performance arsenal... the table is nothing more than a single column of well indexed sequential numbers... some call it a "Numbers" table... I call it a "Tally" table 'cause it sound cooler and helps me count or "tally" things up. Here's how to make one...
--===== Create and populate the Tally table on the fly SELECT TOP 11000 --equates to more than 30 years of dates IDENTITY(INT,1,1) AS N INTO dbo.Tally FROM Master.dbo.SysColumns sc1, Master.dbo.SysColumns sc2
--===== Add a Primary Key to maximize performance ALTER TABLE dbo.Tally ADD CONSTRAINT PK_Tally_N PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100
--===== Allow the general public to use it GRANT SELECT ON dbo.Tally TO PUBLIC
Now, that makes creating a "Split" function very easy, indeed...
CREATE FUNCTION dbo.SplitCSV (@CSV VARCHAR(8000)) RETURNS @Return TABLE (ReturnValue VARCHAR(8000)) AS BEGIN SET @CSV = ','+@CSV+','
INSERT INTO @Return (ReturnValue) SELECT SUBSTRING(@CSV,t.N+1,CHARINDEX(',',@CSV,t.N+1)-t.N-1) AS ReturnValue FROM dbo.Tally t WHERE t.N < LEN(@CSV) AND SUBSTRING(@CSV,t.N,1) = ',' RETURN ENDOk... let's do a little performance testing... in order to do such testing, we need data... lot's of it... the following code builds my "Standard Million Row Test Table"... read the comments in the code, please
--===== Create and populate a 1,000,000 row test table. -- Column RowNum has a range of 1 to 1,000,000 unique numbers -- Column "SomeInt" has a range of 1 to 50,000 non-unique numbers -- Column "SomeString" has a range of "AA" to "ZZ" non-unique 2 character strings -- Column "SomeNumber has a range of 0.0000 to 99.9999 non-unique numbers -- Column "SomeDate" has a range of >=01/01/2000 and <01/01/2010 non-unique date/times
-- Takes about 47 seconds to execute. SELECT TOP 1000000 RowNum = IDENTITY(INT,1,1), SomeInt = CAST(RAND(CAST(NEWID() AS VARBINARY))*50000+1 AS INT), SomeString = CHAR(STR(RAND(CAST(NEWID() AS VARBINARY))*25+65)) + CHAR(STR(RAND(CAST(NEWID() AS VARBINARY))*25+65)), SomeCSV = CAST('Part01,Part02,Part03,Part04,Part05,Part06,Part07,Part08,Part09,Part10' AS VARCHAR(80)), SomeNumber = CAST(RAND(CAST(NEWID() AS VARBINARY))*100 AS MONEY), SomeDate = CAST(RAND(CAST(NEWID() AS VARBINARY))*3653.0+36524.0 AS DATETIME) INTO dbo.JBMTest FROM Master.dbo.SysColumns sc1, Master.dbo.SysColumns sc2 --Lack of join criteria makes this a CROSS-JOIN--===== A table is not properly formed unless a Primary Key has been assigned ALTER TABLE dbo.JBMTest ADD PRIMARY KEY CLUSTERED (RowNum)
... notice... no index on the SomeString column for this first test... and the test code would be...
DECLARE @MyVariable VARCHAR(100) SET @MyVariable = 'AA,BB,CC,DD'
SELECT src.* FROM JBMTest src INNER JOIN dbo.SplitCSV(@MyVariable) v ON src.SomeString = v.ReturnValue
Ok... now add an index to the test table using the code below and then run the test code above, again...
CREATE INDEX IDX_JBMTest_SomeString ON JBMTest (SomeString)
Now... let's put it back in your terms... Do notice the format required from VB should be as follows (according to your original post)...
From Outside SQL - VB.Net
@MyVariable = "MSFT,IBM,Hello World" EXEC MyStoredProcedure '08/31/07',@MyVariable
CREATE PROCEDURE MyProcedure ( @ReportDate DATETIME, @Ticker VARCHAR(70)=NULL ) AS SELECT * FROM MyTable mt INNER JOIN dbo.SplitCSV(@MyVariable) s WHERE mt.Code = s.ReturnValue
Also... very bad idea to name sprocs starting with "sp_"... tells SQL Server to look in the Master DB first... wastes just a bit of time and should be considered a "reserved word" that you shouldn't use. If you insist on using some form of Hungarian notation for your sprocs, try something like spMyProcedure (no underscore).
Lemme know if you have any questions...
--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) -
It's a bad idea to use Hungarian notation for objects in database at all.
Hungarian notation is good for compilers, but T-SQL is interpreter.
It just inflates system indexes, slows interpreting of queries down and may confuse developers when you need to replace, say, a table with a view having the same set of columns.
_____________
Code for TallyGenerator -
... and, it makes beer taste bad
--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) -
What is Hungarian notation?
-
-
It's a naming convention... see the following for more details...
http://en.wikipedia.org/wiki/Hungarian_notation
--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) -
Thank You! I started naming my sprocs with sp__ (two underscores) to prevent naming conflicts in the future in the event Microsoft happened to provide a sproc in the future with the same name. If I use sp with two underscores - will that still go to the master database first?
I guess there is no way to pass an array into an IN statement without using a function or temporary table. My problem isn't really about parsing the data but I am trying to avoid using an inner join or temporary table as my variable will more than likely not contain more than 6 values. I was going to try and use a case statemet as such:
Where Ticker IN(Case When @Ticker IS NUll THEN Ticker Else @Ticker End) The purpose: If they parameter is null it will default to the predefined values otherwise it would use the parameter. Bad logic?
Thanks for all your replies!
-
Try flipping that around. As in, something like....
Where (Case When @Ticker IS Null THEN Ticker Else @Ticker End) like '%'+ticker+'%'
Or - with being a little more thorough
Where (Case When @Ticker IS Null THEN Ticker Else @Ticker End) like ticker+',%'
or (Case When @Ticker IS Null THEN Ticker Else @Ticker End) like '%,'+ticker
(Case When @Ticker IS Null THEN Ticker Else @Ticker End) like '%,'+ticker+',%'
Now....this performance will tend to suck since we're forcing it to do row scans, so unless the dataset is small, your function idea is probably still better performing. But it CAN be done.....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again? -
Yes... sp__ will still go to MASTER first because it still contains sp_ as the first 3 characters.
Yes, you can do this without a function... it'll be a bit (maybe a LOT) slower because there's no chance of an Index SEEK...
SELECT * FROM MyTable WHERE ','+@MyVariable+',' LIKE '%,'+Code+',%'
--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) -
Can this be done with an in versus a like?
Never realized the sp_ - thanks!
-
Only if you use dynamic SQL...
--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) -
Hello Jeff,
I created a tally table and added the split function to my database. I was just trying to test it before I add it to my stored proc and did the following:
declare
@MyVariable varchar(8000)
set
@MyVariable="MSFT,IBM"
select
* from operations..fn__SplitCSV(@MyVariable)
I am receiving an error message saying invalid column name 'MSFT,IBM'?
-
Try single quotes instead of those double quotes.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply