Declaring a List
-
I would like to create a list in SQL from a Declare statement.
I have the following script:
Select *
From MyTable
Where ID in (1, 2, 3)
I would like to declare a variable to represent the list (1, 2, 3)
Note: 1, 2, 3 are just arbitrary numbers. They could be 27, 42, 99, etc...
Thank you all!
-
You have two options:
1. Use dynamic sql
DECLARE @myList varchar(100)
SET @myList = '27,42,99'
EXEC(' SELECT * FROM MyTable WHERE ID in (' + @myList + ')' )
Be aware of all the standards issues with dynamic sql (see http://www.sommarskog.se/dynamic_sql.html)
2. Use a function to parse a comma-delimited list. There are a bunch of implementations of such a function -- here's one: http://www.novicksoftware.com/UDFofWeek/Vol1/T-SQL-UDF-Volume-1-Number-29-udf_Txt_SplitTAB.htm
DECLARE @myList varchar(100)
SET @myList = '27,42,99'
SELECT *
FROM MyTable
WHERE ID in (select item from dbo.udf_txt_SplitTAB(@MyList, ',') )
Especially if this should be highly performant, you might find the function method to work better. I know I do in my applictions, primarily because I do this type of things in stored procedures and the dynamic sql doesn't have to be compiled on each call.
Hope this helps,
Scott Thornburg
-
I had found that link by searching through the archives. Thanks.
I think that this approach is going to be overkill for my stored procedure.
What I wanted to do was declare my list at the top of my procedure, where it would be easy to find/modify.
I was hoping there was a cheap (performance-cost) and easy way to do this. I think that I will stick with "hard-coding" my lists in the actual select statements.
Such is the Life of a programmer.
-
To be honest that is a BAD choise.
When you have to hardCode that means that maintainability and Complexity will exponentially grow. I SQL is a better approach to create a table and add rows to it (to pu it very raw
) If you have that "list" on a table:
1. A simple join will fillter rows for you
2. Adding/Updating/Deleting data from it will relfect immediately on the application and no code changes needs to happen and no Dynamic SQL needed
3.Can sooner than later become into a Lookup which is a concept that programmers understand better
4. Normalization Rules and data integrity can be applied to guarantee data quality
I am just giving you here an example on how to think when you want to solve a problem in the DBRMS world and not just port programing practices to force a solution into SQL.
hth
* Noel -
See if this helps: http://www.sommarskog.se/arrays-in-sql.html
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Bryan
If you read the precedent comments and besides them you still insist on hardcoding our list, since it is length variable you can use a temp. table to store its values.
CREATE TABLE #MyList (Value int)
INSERT INTO #MyList VALUES ( 1 )
INSERT INTO #MyList VALUES ( 2 )
...
INSERT INTO #MyList VALUES ( n )
and then use simply
WHERE Value IN (SELECT Value FROM #MyList)
but, I must insist... it's a bad practice to hardcoding this kind of values thinking on "easy" changes... it's better to maintain a real table... or pass the values as a parameter (maybe into a varchar parameter, and parse it and store it into a temp table into the store)
AND... if you don't use the list more than once.... let it stay hardcoded directly in the sentence... it's no sense to loose performance for just one use of the list...
Salu2
Nicolas Donadio
SW Developer
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply