Link to home
Start Free TrialLog in
Avatar of thomasm1948
thomasm1948Flag for United States of America

asked on

How to pass mutiple string value from list box to SQL 2005

Hi,

I have question in concerns with passing multiple string value to a SQL stored procedure variable

So far my code will only allow me to delete one entry from a VB.NET listbox and I need it to be able to delete multiple items fromt he list box.  Attached is my current stored procedure.  Also I need just simple help in gathering my list values and put them into a string sperated by a comma.  I figure I should be able to parse that in SQL and then delete the items; I just do not know how
Alter procedure [dbo].[Remove_AllUsersFromGroup]
 
@gname varchar(32)
 
as
 
delete from dbo.GroupNotification
where GroupName = @gname

Open in new window

Avatar of Aneesh
Aneesh
Flag of Canada image

Alter procedure [dbo].[Remove_AllUsersFromGroup]
 
@gname varchar(MAX)
 
as
 
EXEC('delete from dbo.GroupNotification
where GroupName IN ('+@gname+')'  )
ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of thomasm1948

ASKER

I got this one to work.  In VB.NET I created a hidden textbox and when a user selected more than one entry then it would add the useris and a a comma "," before it.  when the user hit submit then it sent the string to SQL for parsing.  I am not sure if this is the most  proficient way, but it seems to work

Thank you all for your help
set ANSI_NULLS OFF
set QUOTED_IDENTIFIER OFF
GO
ALTER procedure [dbo].[Remove_AllUsersFromGroup]
@UserID varchar(255)
as
 
declare @test varchar(255)
 
set @test  = @UserID
 
select * from dbo.SecurityIncidents 
where charindex(eventType,@UserID,0)>0

Open in new window

or you could create a CLR method similar to Angels' example.
Then you could do easy string.split() and return a table.

http://www.codeplex.com/MSFTEngProdSamples/Wiki/Print.aspx?title=SS2008!String%20Split%20Table-Valued%20Function%20(TVF)&version=6&action=Print
> where charindex(eventType,@UserID,0)>0

is a dangereous method.

@userid = 1
would match anythink like
1
10
11
12
13...
51
61...

you should keep the method in your .net to pass the string comma-delimited, but in the procedure use the method I suggested to ensure you get the 100% match...