Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Stored Procedure to return multiple inputs between commas

Posted on 2008-11-03
7
Medium Priority
?
659 Views
Last Modified: 2012-05-05
I would like to be able to take input from a user that is separated by commas:

for example:

a,b,c,d,e,f,z

Then i want to use the values in a where clause taken from a variable in a stored procedure.

I will make the variable nvarchar (4000) and use it in a like clause in a query. This way i can get multiple values from teh user. Sometimes they may use one or up to 10 values. What is the best way that i can accomplish this goal ?
0
Comment
Question by:Auerelio Vasquez
7 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22871561
with the function like below, you can do it easily:

SELECT * FROM yourtable
WHERE yourfield IN ( SELECT VALUE FROM dbo.ParmsToList('a,b,c,d', ','))
create FUNCTION dbo.ParmsToList (@Parameters varchar(500), @delimiter varchar(10) )
returns @result TABLE (Value varchar(8000))
AS  
begin
    declare @dx varchar(9)
    --declare @loops int
    -- set @loops = 0
 
     DECLARE @TempList table
          (
          Value varchar(8000)
          )
 
     if @delimiter is null  set @delimiter = ' '
     if datalength(@delimiter) < 1 set @delimiter = ' '
     set @dx = left(@delimiter, datalength(@delimiter)-1)
 
     DECLARE @Value varchar(8000), @Pos int
 
     SET @Parameters = @Parameters + @delimiter
     SET @Pos = CHARINDEX(@delimiter, @Parameters, 1)
 
     IF REPLACE(@Parameters, @delimiter, @dx) <> ''
     BEGIN
          WHILE @Pos > 0 --and @Loops < 100
          BEGIN
              -- set @loops = @loops + 1
               SET @Value = LEFT(@Parameters, @Pos - 1)
               IF @Value <> ''
               BEGIN
                    INSERT INTO @TempList (Value) VALUES (@Value) --Use Appropriate conversion
               END
               SET @Parameters = SUBSTRING(@Parameters, @Pos+ datalength(@delimiter),500)
               SET @Pos = CHARINDEX(@delimiter, @Parameters, 1)
 
          END
     END    
     INSERT @result
     SELECT value
        FROM @TempList
     RETURN
END  

Open in new window

0
 
LVL 60

Expert Comment

by:chapmandew
ID: 22871562
0
 
LVL 1

Author Comment

by:Auerelio Vasquez
ID: 22871818
What if the passed in values are variable? For example, they user goes to a reporting services screen, and in teh Text Box, types in A,B,C,D

How can i handle that situation in either of the above situations? It looks like the values are hardcoded, but if i want to pass in those values, it's a little bit confusing in teh above examples:

so my query woudl look like this

select * from table where field_name in dbo.ParmsToList(@input_variable) -- From stored procedure input variable.....will these examples handle this type of input?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 22871906
same, indeed:

SELECT * FROM yourtable
WHERE yourfield IN ( SELECT VALUE FROM dbo.ParmsToList(@inut_variable, ','))

Open in new window

0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22871913
Yes both of the posted solutions will handle that.  I might as well throw my method in the mix too.


You'll have to create the view (vw_Nums) first but that is included in the comments.  But if your database already has a numbers table, you can you that table name in place of vw_Nums and the number column in place of n in my query.
if object_id('[dbo].[fn_DelimitedToTable]') is not null
     drop function [dbo].[fn_DelimitedToTable]
go
create function [dbo].[fn_DelimitedToTable](@DelimitedString nvarchar(max), @Delimiter nvarchar(32))
returns @Values TABLE
     (ident         int not null identity primary key clustered
     ,thePosition   int not null
     ,theValue      nvarchar(max)
     )
as
/************************************************************
*
*    Author:        Brandon Galderisi
*    Last modified: 07-Oct-2008
*    Purpose:       splits an input string (@DelimitedString) 
*                   on a delimiter (@delimiter) and outputs 
*                   a table of values.
*    
*
*************************************************************/
begin
 
insert into @Values (thePosition,theValue)
		select n, substring(@delimiter + @DelimitedString + @delimiter, n + (datalength(@delimiter)/2), charindex(@delimiter, @delimiter + @DelimitedString + @delimiter, n + datalength(@delimiter)/2) - n - datalength(@delimiter)/2) as string_value
		from	dbo.vw_Nums
		where
			n <= (datalength(@delimiter + @DelimitedString + @delimiter)/2) - (datalength(@delimiter)/2)
			and substring(@delimiter + @DelimitedString + @delimiter, n, (datalength(@delimiter)/2)) = @delimiter
 
 
 
return
end
/*
-- The purpose of vw_Nums is if the source database does not have a numbers table.  This view (vw_nums)
-- will allow the parsing of up to 4 billion+ character strings with the above function.  Whether a static
-- table or this view is used for fn_DelimitedToTable, it can only split a string as long as the source 
-- numbers table.
Requires a nunbers table or this view:
create view vw_Nums
as
with   cte0 as (select 1 as c union all select 1), 
       cte1 as (select 1 as c from cte0 a, cte0 b), 
       cte2 as (select 1 as c from cte1 a, cte1 b), 
       cte3 as (select 1 as c from cte2 a, cte2 b), 
       cte4 as (select 1 as c from cte3 a, cte3 b), 
       cte5 as (select 1 as c from cte4 a, cte4 b), 
       nums as (select row_number() over (order by c) as n from cte5)
       select n from nums 
 
 
 
Sample Usage:
select * from [dbo].[fn_DelimitedToTable]('a|%25basdf|%25c|%25d','|%25')
select theValue from [dbo].[fn_DelimitedToTable]('a','|')
select * from [dbo].[fn_DelimitedToTable]('a basdf c d',' ')
*/
GO

Open in new window

0
 
LVL 1

Author Comment

by:Auerelio Vasquez
ID: 23007394
Angelll,

Is there a way to use your function for a wild card? for example:

if they chose %aabnm% as one of the paramaters, and also they may not want to have a wild care liek abcd

they want to be able to use these to search multiple items.... How can i use your function to do so?

Thanks a million.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 23009511
yes:
SELECT t.* FROM yourtable t
JOIN dbo.ParmsToList(@inut_variable, ',') l
  ON t.yourfield LIKE l.value 

Open in new window

0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Suggested Courses

572 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question