Passing CSV to Stored procedure (1 Viewer)

P

Phil_b

Registered User.
Local time
Today, 00:27
Joined
Oct 7, 2005
Messages
30
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
 
FoFa

FoFa

Registered User.
Local time
Yesterday, 18:27
Joined
Jan 29, 2003
Messages
3,672
Here is a UDF string parsing code that returns a table with the values:
Code: 
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
 
Kodo

Kodo

"The Shoe"
Local time
Yesterday, 19:27
Joined
Jan 20, 2004
Messages
707
that's a big one.. here's another one that's a little more organized

Code: 
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,',') )
 
FoFa

FoFa

Registered User.
Local time
Yesterday, 18:27
Joined
Jan 29, 2003
Messages
3,672
Here is a link that talks about various ways to handle multi-value parameters in T-SQL
 
P

Phil_b

Registered User.
Local time
Today, 00:27
Joined
Oct 7, 2005
Messages
30
hi, thanks for your help. I infact had the UDF Kodo posted already but coudnt get it to work. This is my stored procedure:

Code: 
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.
 
Kodo

Kodo

"The Shoe"
Local time
Yesterday, 19:27
Joined
Jan 20, 2004
Messages
707
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
 
P

Phil_b

Registered User.
Local time
Today, 00:27
Joined
Oct 7, 2005
Messages
30
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
 
P

Phil_b

Registered User.
Local time
Today, 00:27
Joined
Oct 7, 2005
Messages
30
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
 
FoFa

FoFa

Registered User.
Local time
Yesterday, 18:27
Joined
Jan 29, 2003
Messages
3,672
Did you check the link I gave in the last post, it had different ways to handle this.
 
P

Phil_b

Registered User.
Local time
Today, 00:27
Joined
Oct 7, 2005
Messages
30
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:

Code: 
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:

Code: 
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 !!
 
Kodo

Kodo

"The Shoe"
Local time
Yesterday, 19:27
Joined
Jan 20, 2004
Messages
707
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.
 
P

Phil_b

Registered User.
Local time
Today, 00:27
Joined
Oct 7, 2005
Messages
30
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...
 
You must log in or register to reply here.

Similar threads

S
How to pass parameter from Access pass-through to SQL stored procedure
Replies
15
Views
7,009
cheekybuddha
cheekybuddha
M
Question Pass-through query vs stored procedure vs SQL view
Replies
3
Views
2,280
Pat Hartman
P
C
pass-through query to make pivotchart/table problem
Replies
1
Views
998
CoffeeGuru
C
A
passing parameter to stored procedure from a form to generate report
Replies
0
Views
486
ayh1
A
A
stored procedure and parameter pass via form to generate report
Replies
1
Views
841
richsql
R

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom