May 7, 2007 at 9:41 am
Good mornin all-
I need to pass a parameter to a function that would be a comma delimited list of codes, to be used in a filter clause such as -
@param = '81.80','81.82','81.85','82.20'
Select x,y from z where x in (@param)
What would be some methods of accomplishing this?
May 7, 2007 at 9:47 am
Not going into any details, as I would need more information regarding your stored prc, it looks like you will need to build your query in the stored procedure dynamically and execute it using sp_executesql or the execute statement.
If you need additional help, please be prepared to post additional info regarding what you are trying to do.
May 7, 2007 at 9:58 am
See Erland Sommarskog's article on this subject at
http://www.sommarskog.se/arrays-in-sql.html
SQL = Scarcely Qualifies as a Language
May 7, 2007 at 10:03 am
I need to pick up all codes and descriptions for each user record. So the results would look like
id codes+desc
1 81.51-desc8151,81.52-desc8152.....
id from tableA, codes+desc from tableB. Because an id can have one to 30 codes, and I only want certain ones if they exists, and only one row per id. I'm looking to catatenate the codes.
May 7, 2007 at 10:05 am
For this I've used a function I found on a technet forum from Bruce L. (MVP) which parses the values in the string and returns a table that you join to. Here's what it looks like...
/*********************** CODE ***********************/
CREATE FUNCTION charlist_to_table(
@list ntext,
@delimiter nchar(1) = N',')
RETURNS @tbl TABLE (
listpos int IDENTITY(1, 1) NOT NULL,
str varchar(4000),
nstr nvarchar(2000)
) AS
BEGIN
DECLARE @pos int,
@textpos int,
@chunklen smallint,
@tmpstr nvarchar(4000),
@leftover nvarchar(4000),
@tmpval nvarchar(4000)
SET @textpos = 1
SET @leftover = ''
WHILE @textpos 0
BEGIN
SET @tmpval = ltrim(rtrim(left(@tmpstr, @pos - 1)))
INSERT @tbl (str, nstr) VALUES(@tmpval, @tmpval)
SET @tmpstr = substring(@tmpstr, @pos + 1, len(@tmpstr))
SET @pos = charindex(@delimiter, @tmpstr)
END
SET @leftover = @tmpstr
END
INSERT @tbl(str, nstr) VALUES (ltrim(rtrim(@leftover)),ltrim(rtrim(@leftover)))
RETURN
END
GO
/*********************** CODE ***********************/
/*********************** EXAMPLE ***********************/
declare @str varchar(4000)
set @str = 'phoenix, boston, chicago, denver, san diego'
select
*
from
UScities as c
inner join charlist_to_table(@str, Default) as x on
x.str = c.city
/*********************** EXAMPLE ***********************/
In this example there is no where clause becuase the inner join is handling this by comparing the 'city' field to all the values passed in by using our charlist_to_table function.
Hope this Helps...
-Ben
Ben Sullins
bensullins.com
Beer is my primary key...
May 7, 2007 at 3:34 pm
This is a great example of how to use a numbers table in combination with a table valued split function. Do a search on SSC for split or number table and you'll get plenty of info and examples.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy