String parameter
-
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?
-
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.
-
See Erland Sommarskog's article on this subject at
http://www.sommarskog.se/arrays-in-sql.html
SQL = Scarcely Qualifies as a Language
-
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.
-
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
Cheers,Ben Sullins
bensullins.com
Beer is my primary key... -
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 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply