Solved

How do I pass multiple values thought One Variable

Posted on 2007-11-20
5
1,466 Views
Last Modified: 2010-05-18
Hi,

I have this stored procedure below which at present only filters on one variable @Product_Code1.

Its it possible to pass multiple values to this variable e.g.

exec test '1633,1891'

I've tried the above, but it interprets it as one string.
CREATE PROCEDURE test 
 
@Product_Code1 nvarchar(100)
 
AS
SELECT     dbo.cart.Customer_URN, dbo.TRIM(dbo.customer.Customer_Title) As Title, dbo.TRIM(dbo.customer.Customer_Forename) As Forename, dbo.TRIM(dbo.customer.Customer_Surname) As Surname
FROM         dbo.cart INNER JOIN
                      dbo.cartitem ON dbo.cart.Cart_URN = dbo.cartitem.Cart_URN INNER JOIN
                      dbo.product ON dbo.cartitem.Product_URN = dbo.product.Product_URN INNER JOIN
                      dbo.customer ON dbo.cart.Customer_URN = dbo.customer.Customer_URN
WHERE     (dbo.cart.Cart_Order_DateTime BETWEEN CONVERT(DATETIME, '2007-01-15 00:00:00', 102) AND CONVERT(DATETIME, '2007-11-15 00:00:00', 102)) 
                      AND (dbo.cart.Cart_Status = N'Order') AND (dbo.product.Product_Code) in (@Product_Code1)
GROUP BY dbo.cart.Customer_URN, dbo.customer.Customer_Title, dbo.customer.Customer_Forename, dbo.customer.Customer_Surname, 
                      dbo.customer.Customer_Marketing
HAVING      (NOT (dbo.cart.Customer_URN IN
                          (SELECT     dbo.cart.Customer_URN
                            FROM          dbo.cart INNER JOIN
                                                   dbo.cartitem ON dbo.cart.Cart_URN = dbo.cartitem.Cart_URN INNER JOIN
                                                   dbo.product ON dbo.cartitem.Product_URN = dbo.product.Product_URN
                            WHERE      (dbo.cart.Cart_Order_DateTime BETWEEN CONVERT(DATETIME, '2007-10-15 00:00:00', 102) AND CONVERT(DATETIME, 
                                                   '2007-11-15 00:00:00', 102)) AND (dbo.cart.Cart_Status = N'ORDER')
                            GROUP BY dbo.cart.Customer_URN))) AND (dbo.customer.Customer_Marketing <> N'DEAD')
                            ORDER BY Surname

Open in new window

0
Comment
Question by:BenthamLtd
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 8

Expert Comment

by:digital_thoughts
ID: 20320891
What is the data type of your Product_Code field?
0
 

Author Comment

by:BenthamLtd
ID: 20320915
The data type is nvarchar
0
 
LVL 8

Accepted Solution

by:
doobdave earned 125 total points
ID: 20321226
Hi there,

as I see it, you have two viable options:

1) use dynamic SQL.  This involves concatenating the string passed as a parameter into your select statement and using EXEC or sp_executesql to execute it (look those up in BOL). This is subject to SQL Injection attacks, can be poorly performing, and does not provide compile-time syntax checking

2) Create a table-valued function (see code snippet) to split a string into a table variable.  The table variable will contain a row for each value. You can then use your original SQL with an IN clause, using the function as follows:

SELECT ... FROM ... WHERE colname IN (SELECT SplitValues FROM dbo.ftn_DelimitedListToIntTableVariable)

Note that you will need to grant SELECT permission on the function.

I highly recommend option 2 as it's more flexible.

Best Regards,

David
CREATE FUNCTION [dbo].[ftn_DelimitedListToIntTableVariable]
(
	@DelimitedList varchar(max),
	@Delimiter varchar(1)
)
RETURNS @tbl table(SplitValues int)
 
AS
/*
	Accepts a character-delimited string of values.
	Returns a table variable, containing one row for each value in the string that was passed in.
	Values are split according to the character supplied for the @Delimiter variable.
	Values are converted to integer data type and stored in the returned table.
*/
 
BEGIN
	DECLARE @DelimiterPos int
	-- Find the first comma
	SET @DelimiterPos = PATINDEX( '%,%', @DelimitedList)
	
	-- If a delimiter was found, @DelimiterPos will be > 0.
	WHILE @DelimiterPos > 0
		BEGIN
			-- Insert the value between the start of the string and the first delimiter, into the table variable.
			INSERT INTO @tbl(SplitValues) SELECT CAST(LTRIM(RTRIM((SUBSTRING(@DelimitedList, 1, @DelimiterPos -1)))) AS int)
			
			-- Trim the string of the first value and delimiter.
			SET @DelimitedList = SUBSTRING(@DelimitedList, @DelimiterPos +1, LEN(@DelimitedList) - @DelimiterPos)
			
			-- Look for the next delimiter in the string.
			SET @DelimiterPos = PATINDEX( '%,%', @DelimitedList)
		END
	
	-- Ensure the last / only value in the @DelimitedList string gets inserted into the table variable.
	INSERT INTO @Tbl(SplitValues) SELECT CAST(LTRIM(RTRIM((@DelimitedList))) AS int)
	RETURN
END;

Open in new window

0
 
LVL 8

Expert Comment

by:doobdave
ID: 20321237
sorry, I just noticed you're using SQL Server 2000, you will need to change the input paramter for the function to be varchar(8000) instead of varchar(max)
0
 

Author Comment

by:BenthamLtd
ID: 20323484
Thanks for your suggestion.

I have made a work around using a built table as the productcode selection.
0

Featured Post

Ready to get started with anonymous questions?

It's easy! Check out this step-by-step guide for asking an anonymous question on Experts Exchange.

Question has a verified solution.

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

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

635 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