Pass a list of strings to a stored procedure
-
Hello folks.
I've spent a few hours trying to suss this one out to no avail. There are lost of articals on how to do this with numeric values, but I cant find a single one related to strings.
Let me explain. I basically have some tables related to users and the groups they are members of.
I have a stored procedure which will gather details on these users based on criteria passed in.
I need to be able to collect a result set where the users are within a of groups.
Heres the crunch. The group_id's are VARCHAR
(damn) and use non numeric characters in all there glory. I have no control over the table structure, so couldn't change this.So, My query looks something like this:
SELECT
User.fields_i_want
FROM User
WHERE
User_id IN (SELECT User_id FROM Group_member WHERE Group_id IN (@Search_param))
ORDER BY User_id ASC
No matter what I try, I just cant get my execute statement to work.
My execute looks something like this:
EXEC My_user_sp ''Group1', 'Group2', 'Group3''
As you can see, the single quotes mess this up. If I take the outside quotes out, each item in the is treated as a seporate parameter. I've tried all sorts, and my head hurts.
Can anyone tell me if theres a way to do this?
Thanks for reading this,
Allan.
-
Try this ...
SELECT '''Group1'', ''Group2'', ''Group3'''
MK
-
Hi Allan,
Would it not be easier to build up the whole of the sql into a string and exec it?
This works perfectly, depends if you can build it......
DECLARE @sql varchar(8000),
@varNames varchar(8000)
SET @varNames = '''Roger''' + ', ' + '''Allan''' + ', ' + '''David'''
PRINT @varNames
SET @sql = 'SELECT NameFirst, NameMiddle, NameLast FROM tblNames WHERE NameFirst IN (' + @varNames + ')'
PRINT @sql
EXEC (@SQL)
Have fun
Steve
We need men who can dream of things that never were.
-
Make it a Dynamic stored procedure it should work...
CREATE Stored Procedure My_user_sp
@Search_Parm VARCHAR(100)
AS
DECLARE @sql VARCHAR(3000)
SET @sql = 'SELECT
User.fields_i_want
FROM User
WHERE
User_id IN (SELECT User_id FROM Group_member WHERE Group_id IN (' + @Search_param + '))
ORDER BY User_id ASC'
EXEC (@SQL)
EXEC My_user_sp '''Group1'', ''Group2'', ''Group3'''
MK
-
This is great, I'm making progress now. It works when I put the query into a variable first.
However, my problem is a little more complex. I Use embedded CURSOR's in my stored procedures. First I fetch the users, then the devices, the the timeframes for those devices.
The above dosn't seem to work for the query in my cursor, so I created a variable called @Qry, and tried to do the following:
DECLARE Qrecipient CURSOR FOR @Qry.
I get incorrect syntax. on the same line. Is there a way I can use the @Qry variable as my CURSOR?
I'm pretty sure this would solve my problem.
Thanks again,
Allan.
-
Can you post the query which you are trying to use.
-
Sure thing, OK the first query looks like this while filtering the users by groups ( the group id's are in the @Search_param):
SELECT
Recipient.Rcpt_id,
Recipient.Forename,
Recipient.Surname,
Recipient.Delete_ind,
Security_group.Security_group_desc,
Site.Site_name
FROM Recipient
INNER JOIN Site ON Site.Site_id=Recipient.Site_id
INNER JOIN Security_group ON Security_group.Security_group_id=Recipient.Security_group_id
WHERE
Recipient.Rcpt_id IN (SELECT Rcpt_id FROM Group_member WHERE Group_id IN (@Search_param))
ORDER BY Rcpt_id ASC
I call the sp like this
EXEC Rpt_gmd_rcpt 'grps', '''Group1'',''Group2'', ''etc''', 'id'
where the first parameter says use the above query, the second is the @Search_params, and the last is what I want the final result set to be ordered by.
As stated, the query returns good resuls when executed as a string in query analyser, however, as a CURSOR like above, it always returns nothing.
Allan.
-
If you're doing this in a stored proc you could try, instead of:
DECLARE Qrecipient CURSOR FOR @Qry.
the following:
DECLARE @Curs varchar(8000)
SET @Curs = 'DECLARE Qrecipient CURSOR FOR ' + @Qry
EXEC (@Curs)
HTH
Luigi
-
Some of the finest SQL heads around have contributed to this article:
http://www.sommarskog.se/arrays-in-sql.html
Although it's a quite long article, it might give you some ideas.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
i've been using stuff like this recently
patindex('%,' + @debt_type_cd + ',%', pa.DebtType) != 0
where @debt_type_cd is some value and pa.DebtType code is a comma delimited list like this
,something,somethingElse,morethings,
note the comma on the end
so you could use something like this
patindex('%,' + ColumnUsedForSearching + ',%', @CommaDelimitedParameterList) != 0
i read that it's one to avoid in the link fank posted, but i've never had any performance issues with this method maybe because i'm using variables and not columns...
hth
s
-
Everyone, you have been brill, and a credit to this site.
My problem is solved, which left me to get on with the task of finishing off the stored procedure.
So, here it is, in its entirity. Theres a lot of specific stuff related to the contents of the database, but some of it should help anyone else stumbling along this path:
CREATE PROCEDURE [dbo].[Rpt_gmd_rcpt]
@search_type VARCHAR(3),
@search_param VARCHAR(500),
@order_by VARCHAR(40)
AS
-- recipient info --
DECLARE @Rcpt_id NUMERIC(10)
DECLARE @Forename VARCHAR(40)
DECLARE @Surname VARCHAR(40)
DECLARE @Delete_ind NUMERIC(5)
DECLARE @Security_group_desc VARCHAR(40)
DECLARE @Site_name VARCHAR(40)
-- device info --
DECLARE @Device VARCHAR(80)
DECLARE @Device_id NUMERIC(9)
DECLARE @Device_type VARCHAR(3)
DECLARE @Device_desc VARCHAR(80)
DECLARE @Device_order NUMERIC(5)
DECLARE @Area_code VARCHAR(20)
DECLARE @Phone_number VARCHAR(20)
DECLARE @Email_address VARCHAR(80)
DECLARE @Attempt_limit NUMERIC(5)
DECLARE @Requeue_time NUMERIC(5)
DECLARE @Type_desc VARCHAR(80)
DECLARE @Use_desc VARCHAR(80)
-- timeframe info --
DECLARE @timeframe VARCHAR(80)
DECLARE @Device_timeframe_id NUMERIC(9)
DECLARE @Unavailable_ind NUMERIC(5)
DECLARE @Timeframe_start VARCHAR(8)
DECLARE @Timeframe_end VARCHAR(8)
DECLARE @Timeframe_desc VARCHAR(40)
-- the result table --
Declare @tbl table (
Rcpt_id NUMERIC(10),
Forename VARCHAR(40),
Surname VARCHAR(40),
Delete_ind NUMERIC(5),
Security_group_desc VARCHAR(40),
Site_name VARCHAR(40),
Device_id NUMERIC(9),
Device VARCHAR(80),
Device_timeframe_id NUMERIC(9),
timeframe VARCHAR(80)
)
DECLARE @q VARCHAR(8000)
SET @search_param=REPLACE(@Search_param,'|', '''')
IF @search_type='grp'
BEGIN
SET @q=' SELECT
Recipient.Rcpt_id,
Recipient.Forename,
Recipient.Surname,
Recipient.Delete_ind,
Security_group.Security_group_desc,
Site.Site_name
FROM Recipient
INNER JOIN Site ON Site.Site_id=Recipient.Site_id
INNER JOIN Security_group ON Security_group.Security_group_id=Recipient.Security_group_id
WHERE
Recipient.Rcpt_id IN (SELECT Rcpt_id FROM Group_member WHERE Group_id IN ('+@search_param+'))
ORDER BY Rcpt_id ASC'
END
ELSE
BEGIN
SET @q=' SELECT
Recipient.Rcpt_id,
Recipient.Forename,
Recipient.Surname,
Recipient.Delete_ind,
Security_group.Security_group_desc,
Site.Site_name
FROM Recipient
INNER JOIN Site ON Site.Site_id=Recipient.Site_id
INNER JOIN Security_group ON Security_group.Security_group_id=Recipient.Security_group_id
WHERE
CASE WHEN '+@search_type+'=''fn'' THEN
CONVERT(char(50),Recipient.Forename)
WHEN '+@search_type+'=''sn'' THEN
CONVERT(char(50),Recipient.Surname)
WHEN '+@search_type+'=''id'' THEN
CONVERT(char(50),Recipient.Rcpt_id)
END
LIKE ('+@search_param+') ORDER BY Rcpt_id ASC'
END
SET @q = 'DECLARE Qrecipient CURSOR READ_ONLY FOR ' + @q
EXEC (@q)
OPEN Qrecipient
FETCH NEXT FROM Qrecipient INTO @Rcpt_id, @Forename, @Surname, @Delete_ind, @Security_group_desc, @Site_name
WHILE @@FETCH_STATUS = 0
BEGIN
-- collect the device info --
DECLARE Qdevice CURSOR FOR
SELECT
Device.Device_id,
Device.Device_type,
Device.Device_desc,
Device.Device_order,
Device.Area_code,
Device.Phone_number,
Device.Email_address,
Device.Attempt_limit,
Device.Requeue_time,
Device_type.Device_desc AS Type_desc
FROM Device
INNER JOIN Device_type ON Device_type.Device_type=Device.Device_type
WHERE Rcpt_id=@Rcpt_id
ORDER BY Device_order ASC
OPEN Qdevice
FETCH NEXT FROM Qdevice INTO @Device_id,@Device_type, @Device_desc, @Device_order, @Area_code, @Phone_number, @Email_address, @Attempt_limit, @Requeue_time, @Type_desc
WHILE @@FETCH_STATUS = 0
BEGIN
-- create the device string --
SET @Use_desc=@Device_desc
IF @Device_desc IS NULL SET @Use_desc=@Type_desc
IF @Device_desc='' SET @Use_desc=@Type_desc
IF @Device_type = 'EML' SET @Device = @Use_desc+')'+@Email_address+'.';
IF @Device_type = 'PHN' SET @Device = @Use_desc+')'+@Area_code+@Phone_number+', with '+CAST(@Attempt_limit AS VARCHAR(9))+' Attempts requeued after '+CAST(@Requeue_time AS VARCHAR(9))+' minutes.';
IF @Device_type = 'TXT' SET @Device =@Use_desc+')'+@Phone_number+'.';
SET @Device = CAST(@Device_order AS VARCHAR(9))+': ('+ltrim(@Device);
-- collect the timeframe information for this device --
DECLARE Qtimeframe CURSOR FOR
SELECT
Device_timeframe.Device_timeframe_id,
Device_timeframe.Unavailable_ind,
Device_timeframe.Timeframe_start,
Device_timeframe.Timeframe_end,
Timeframe_type.Timeframe_desc
FROM Device_timeframe
INNER JOIN Timeframe_type ON Timeframe_type.Timeframe_type=Device_timeframe.timeframe_type
WHERE <A href="mailto
evice_id=@Device_id">Device_id=@Device_idOPEN Qtimeframe
FETCH NEXT FROM Qtimeframe INTO @Device_timeframe_id, @Unavailable_ind, @Timeframe_start, @Timeframe_end, @Timeframe_desc
WHILE @@FETCH_STATUS = 0
BEGIN
-- create the timeframe string --
IF @Unavailable_ind=0 SET @timeframe='Available ';
IF @Unavailable_ind=1 SET @timeframe='Unavailable ';
IF @Timeframe_desc='Any Day' SET @timeframe=@timeframe+'any day' ELSE SET @timeframe=@timeframe+' '+@Timeframe_desc+'s';
SET @timeframe=@timeframe+' from '+@Timeframe_start+' to '+@Timeframe_end;
-- now we have all the details put them in the table --
INSERT INTO @tbl VALUES (
@Rcpt_id, @Forename, @Surname, @Delete_ind, @Security_group_desc, @Site_name,
@Device_id,@Device,
@Device_timeframe_id, @timeframe
 
FETCH NEXT FROM Qtimeframe INTO @Device_timeframe_id, @Unavailable_ind, @Timeframe_start, @Timeframe_end, @Timeframe_desc
END
CLOSE Qtimeframe
DEALLOCATE Qtimeframe
FETCH NEXT FROM Qdevice INTO @Device_id,@Device_type, @Device_desc, @Device_order, @Area_code, @Phone_number, @Email_address, @Attempt_limit, @Requeue_time, @Type_desc
END
CLOSE Qdevice
DEALLOCATE Qdevice
FETCH NEXT FROM Qrecipient INTO @Rcpt_id, @Forename, @Surname, @Delete_ind, @Security_group_desc, @Site_name
END
CLOSE Qrecipient
DEALLOCATE Qrecipient
select * from @tbl ORDER BY CASE
WHEN @order_by='fn' THEN CONVERT(char(50), Forename )
WHEN @order_by='sn' THEN CONVERT(char(50), Surname )
WHEN @order_by='id' THEN CONVERT(char(50), Rcpt_id)
END
ASC
Return
GO
Thanks to you all,
Allan.
-
Oh my god, I cannot believe how long of a solution you've made of this!
Why didn't you first do a search on "Delimeted". I personally have posted a User Defined Function which you pass a String to and it returns a Table so that your queries are very fast.Example:
Select * From Users Where FirstName IN(Select * From dbo.udf_GetDelimetedValues('Bob,Jim,Mary,Albert'))
Code:
CREATE FUNCTION
dbo.udf_GetDelimetedValues
(
@Values
VarChar(2000), @Delimeter VarChar(1) = ','
)
RETURNS
@Table Table(Value VarChar(256))
AS
BEGIN
-- Variables
Declare @Position SmallInt
Set @Position = 0
If (Right(@Values, 1) <> @Delimeter) Set @Values = @Values + @Delimeter
While (CharIndex(@Delimeter, @Values, @Position + 1) > 0)
BEGIN
Insert Into @Table
Values(
LTrim(RTrim(SubString(@Values, @Position, CharIndex(@Delimeter, @Values, @Position + 1) - @Position)))
)
Set @Position = CharIndex(@Delimeter, @Values, @Position + 1) + 1
END
RETURN
END
-
Yes, you really need to read the article I've reference. In the meantime what about?
SET NOCOUNT ON
SELECT
TOP 256 Number = IDENTITY(INT)
INTO
MyNumbers
FROM
Sysobjects S1
CROSS JOIN
Sysobjects S2
DECLARE @s-2 varchar(256)
SET @s-2 = 'Frank,,Foo,,,Bar,,'
SELECT
RIGHT(LEFT(@s,Number-1),CHARINDEX(',',REVERSE(LEFT(','+@s,Number-1))))
FROM
-- master..spt_values
MyNumbers
WHERE
-- Type = 'P'
--AND
Number BETWEEN 1 AND LEN(@s)
AND
SUBSTRING(@s,Number,1) = ',' AND SUBSTRING(@s,Number-1,1) <> ','
DROP TABLE MyNumbers
SET NOCOUNT OFF
Sometimes this numerical helper table works miracles.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
At the risk of saying another "Why didn't you read..."
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=143932#bm143942
Viewing 14 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply