Just to start I have read numerous articles on numerous sites in regard to this and none seem to have helped me out...

What I need is the ability to pass three multi value strings to a stored procedure. So it could be:

String1 = Country1, Country2, Country3
String2 = Item1, Item2, Item3
String3 = Region1, Region2, Region3

Now I have a host of problems and have tryed everything from adding quotes so UDF's to other things and im still at a loss !

Any help is appreciated. In addition if I can provide any further info from my side let me know.

Phil

Here is a UDF string parsing code that returns a table with the values:
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
create function fn_ParseText2Table
(
@p_SourceText varchar(8000)
,@p_Delimeter varchar(100) = ',' --default to comma delimited.
)
RETURNS @retTable TABLE
(
Position int identity(1,1)
,Int_Value int
,Num_value Numeric(18,3)
,txt_value varchar(2000)
)
AS
/*
************************************************** ******************************
Purpose: Parse values from a delimited string
& return the result as an indexed table
Copyright 1996, 1997, 2000, 2003 Clayton Groom (Clayton_Groom@hotmail.com)
Posted to the public domain Aug, 2004
06-17-03 Rewritten as SQL 2000 function.
Reworked to allow for delimiters > 1 character in length
and to convert Text values to numbers
************************************************** ******************************
*/
BEGIN
DECLARE @w_Continue int
,@w_StartPos int
,@w_Length int
,@w_Delimeter_pos int
,@w_tmp_int int
,@w_tmp_num numeric(18,3)
,@w_tmp_txt varchar(2000)
,@w_Delimeter_Len tinyint
if len(@p_SourceText) = 0
begin
SET @w_Continue = 0 -- force early exit
end
else
begin
-- parse the original @p_SourceText array into a temp table
SET @w_Continue = 1
SET @w_StartPos = 1
SET @p_SourceText = RTRIM( LTRIM( @p_SourceText))
SET @w_Length = DATALENGTH( RTRIM( LTRIM( @p_SourceText)))
SET @w_Delimeter_Len = len(@p_Delimeter)
end
WHILE @w_Continue = 1
BEGIN
SET @w_Delimeter_pos = CHARINDEX( @p_Delimeter
,(SUBSTRING( @p_SourceText, @w_StartPos
,((@w_Length - @w_StartPos) + @w_Delimeter_Len)))
)

IF @w_Delimeter_pos > 0 -- delimeter(s) found, get the value
BEGIN
SET @w_tmp_txt = LTRIM(RTRIM( SUBSTRING( @p_SourceText, @w_StartPos
,(@w_Delimeter_pos - 1)) ))
if isnumeric(@w_tmp_txt) = 1
begin
set @w_tmp_int = cast( cast(@w_tmp_txt as numeric) as int)
set @w_tmp_num = cast( @w_tmp_txt as numeric(18,3))
end
else
begin
set @w_tmp_int = null
set @w_tmp_num = null
end
SET @w_StartPos = @w_Delimeter_pos + @w_StartPos + (@w_Delimeter_Len- 1)
END
ELSE -- No more delimeters, get last value
BEGIN
SET @w_tmp_txt = LTRIM(RTRIM( SUBSTRING( @p_SourceText, @w_StartPos
,((@w_Length - @w_StartPos) + @w_Delimeter_Len)) ))
if isnumeric(@w_tmp_txt) = 1
begin
set @w_tmp_int = cast( cast(@w_tmp_txt as numeric) as int)
set @w_tmp_num = cast( @w_tmp_txt as numeric(18,3))
end
else
begin
set @w_tmp_int = null
set @w_tmp_num = null
end
SELECT @w_Continue = 0
END
INSERT INTO @retTable VALUES( @w_tmp_int, @w_tmp_num, @w_tmp_txt )
END
RETURN
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

that's a big one.. here's another one that's a little more organized


CREATE FUNCTION dbo.fn_Split
(@text varchar(8000), @delimiter varchar(20) = ' ')

RETURNS @Strings TABLE

(

position int IDENTITY PRIMARY KEY,

value varchar(8000)

)

AS

BEGIN



DECLARE @index int

SET @index = -1



WHILE (LEN(@text) > 0)

BEGIN

SET @index = CHARINDEX(@delimiter , @text)

IF (@index = 0) AND (LEN(@text) > 0)

BEGIN

INSERT INTO @Strings VALUES (@text)

BREAK

END

IF (@index > 1)

BEGIN

INSERT INTO @Strings VALUES (LEFT(@text, @index - 1))

SET @text = RIGHT(@text, (LEN(@text) - @index))

END

ELSE

SET @text = RIGHT(@text, (LEN(@text) - @index))

END

RETURN

END


Select * from table where somefieldname in (select value from fnSplit(@param,',') )

Here (http://www.bizdatasolutions.com/tsql/sqlarrays.asp) is a link that talks about various ways to handle multi-value parameters in T-SQL

hi, thanks for your help. I infact had the UDF Kodo posted already but coudnt get it to work. This is my stored procedure:

ALTER PROCEDURE dbo.sp_ClientQuery
(
@inputRegion varchar(500)
--@inputPub varchar(500),
--@inputCountry varchar(500)
)
AS
BEGIN

SET NOCOUNT ON

DECLARE @SQL varchar(5000)


SET @SQL = 'SELECT tblClient.Country, dbo.tblClient.cName, dbo.tblClient.clientID, dbo.tblClient.Wholename, dbo.tblClient.TelNumberG, dbo.tblClient.TelNumberD, dbo.tblClient.City
FROM dbo.tblOrders INNER JOIN
dbo.tblClient ON dbo.tblOrders.clientID = dbo.tblClient.clientID INNER JOIN
dbo.tblPublication ON dbo.tblOrders.id = dbo.tblPublication.id
WHERE (dbo.tblClient.Region IN (select value from fnSplit(@inputRegion,",") ) AND (dbo.tblPublication.pName IN ("CF")) AND
(dbo.tblClient.Country IN ("United Kingdom"))'

EXEC (@SQL)

END

It keeps saying that I haven't declared @inputRegion... I have kept the other two inputs constant for simplicity. It infact says:

must declare the variable 'inputRegion'.

Thanks again.

you're execing a string
try

SET @SQL = 'SELECT tblClient.Country, dbo.tblClient.cName, dbo.tblClient.clientID, dbo.tblClient.Wholename, dbo.tblClient.TelNumberG, dbo.tblClient.TelNumberD, dbo.tblClient.City
FROM dbo.tblOrders INNER JOIN
dbo.tblClient ON dbo.tblOrders.clientID = dbo.tblClient.clientID INNER JOIN
dbo.tblPublication ON dbo.tblOrders.id = dbo.tblPublication.id
WHERE (dbo.tblClient.Region IN (select value from fnSplit(' + @inputRegion + ',",") ) AND (dbo.tblPublication.pName IN ("CF")) AND
(dbo.tblClient.Country IN ("United Kingdom"))'

EXEC (@SQL)

END

thanks, that worked fine although now I get this error:

"is not a recognized optimizer lock hints option"

All i want to do with this is pass a CSV list to my stored procedure but it seems to be turning into a nightmare. I looked up the following error and it said about changing my SQL server mode from 65 to 70 although its at 80 currently.

Is there any quick and easy way I can pass a string of synamic values to a stored procedure? I never knew it would be this time consuming with so many possible errors along the way !

Thanks again for your time,

Phil

any help with this? If there is an alternative way of passing multiple values to a parameter I would be willing to change my approach.

Phil

Did you check the link I gave in the last post, it had different ways to handle this.

thanks for the reply Fofa. I tried them all (literally) and am no better off. I infact went onto another part of the project but now am coming back to it.

Whatever I do I cant pass a csv string to the IN part of an SQL statement... all i get currently is:

"is not a recognized optimizer lock hints option"

Below are details, Stored Procedure:

ALTER PROCEDURE dbo.sp_ClientQuery
(

@inputRegion varchar(500)
--@inputPub varchar(500)
--@inputCountry varchar(500)
)
AS
BEGIN

SET NOCOUNT ON

DECLARE @SQL varchar(5000)


SET @SQL = 'SELECT dbo.tblClient.Country, dbo.tblClient.cName, dbo.tblClient.clientID, dbo.tblClient.Wholename, dbo.tblClient.TelNumberG, dbo.tblClient.TelNumberD, dbo.tblClient.City
FROM dbo.tblOrders INNER JOIN
dbo.tblClient ON dbo.tblOrders.clientID = dbo.tblClient.clientID INNER JOIN
dbo.tblPublication ON dbo.tblOrders.id = dbo.tblPublication.id
WHERE (dbo.tblClient.Region IN (select value from fnSplit(' + @inputRegion + ',",") ) AND (dbo.tblPublication.pName IN ("CF")) AND
(dbo.tblClient.Country IN ("United Kingdom"))'

EXEC (@SQL)

END

And the fnSplit is as follows:

ALTER FUNCTION dbo.fnSplit
(@text varchar(8000), @delimiter varchar(20) = ' ')

RETURNS @Strings TABLE

(

position int IDENTITY PRIMARY KEY,

value varchar(8000)

)

AS

BEGIN



DECLARE @index int

SET @index = -1



WHILE (LEN(@text) > 0)

BEGIN

SET @index = CHARINDEX(@delimiter , @text)

IF (@index = 0) AND (LEN(@text) > 0)

BEGIN

INSERT INTO @Strings VALUES (@text)

BREAK

END

IF (@index > 1)

BEGIN

INSERT INTO @Strings VALUES (LEFT(@text, @index - 1))

SET @text = RIGHT(@text, (LEN(@text) - @index))

END

ELSE

SET @text = RIGHT(@text, (LEN(@text) - @index))

END

RETURN

END

I cant comprehend how this is so time consuming. Any help appreciated !!

Quotes(") are invalid characters


SELECT dbo.tblClient.Country, dbo.tblClient.cName, dbo.tblClient.clientID, dbo.tblClient.Wholename, dbo.tblClient.TelNumberG, dbo.tblClient.TelNumberD, dbo.tblClient.City
FROM dbo.tblOrders INNER JOIN
dbo.tblClient ON dbo.tblOrders.clientID = dbo.tblClient.clientID INNER JOIN
dbo.tblPublication ON dbo.tblOrders.id = dbo.tblPublication.id
WHERE (dbo.tblClient.Region IN (select value from fnSplit(@inputRegion ,',') ) AND (dbo.tblPublication.pName IN ('CF')) AND
(dbo.tblClient.Country IN ('United Kingdom'))
Try it without the exec.. if that's the only thing you're doing in the sproc , then you don't need to set it to a variable.

I also noticed that you're passing in a text value for a country. You shoud have countries in a table that are numbered. This will adhere to normal forms better.

thanks Kodo !!!

For some reason when I put the SQL statement into a variable and then run it the error occured...

Really glad to have seen the back of this ! Hopefully soon SQL server will add an array datatype...