USING the IN in STORED PROCEDURE

Question

I have a problem in passing parameters on stored procedure using the IN in the were clause as below

ALTER PROCEDURE SELECT_MULTIPLE
@MULTI VARCHAR(54)
AS
SELECT MSISDN, IMSI, HLR_NO
FROM HLR
WHERE MSISDN IN (@MULTI)
ORDER BY MSISDN

WHEN I EXECUTE THE PROCEDURE ON QUERIES ON MSACCESS NO OUTPUT WHERE PRODUCED AND data entered is as follows

'639229380968','639229485075','639229346127','639229416465'

I remove the quote but I can get an output only when one value is entered.

What I am missing? please help?

Vic

 

 

 

Answer 1

Hi,

arrays (as you posted them below) are not supported as an input variable, you could split them to a table value function to join them, otherwise I would suggest you to read:

http://www.sommarskog.se/arrays-in-sql.html

I wrote afunction for that sometime ago:

CREATE FUNCTION dbo.Split
(
        @String VARCHAR(200),
        @Delimiter VARCHAR(5)
)
RETURNS @SplittedValues TABLE
(
  OccurenceId SMALLINT IDENTITY(1,1),
  SplitValue VARCHAR(200)
)
AS
BEGIN
DECLARE @SplitLength INT

WHILE LEN(@String) > 0
BEGIN
        SELECT @SplitLength = (CASE CHARINDEX(@Delimiter,@String) WHEN 0 THEN
LEN(@String) ELSE CHARINDEX(@Delimiter,@String) -1  END)

        INSERT INTO @SplittedValues
        SELECT SUBSTRING(@String,1,@SplitLength)

        SELECT @String = (CASE (LEN(@String) - @SplitLength) WHEN 0 THEN ''
ELSE RIGHT(@String, LEN(@String) - @SplitLength - 1) END)
END
RETURN
END


This would equal to your code:

SELECT MSISDN, IMSI, HLR_NO
FROM HLR
INNER JOIN dbo.Split((@MULTI,',') SplittedValues
ON HLR.MSISDN  = SplittedValues.SplitValue
ORDER BY MSISDN

HTH, jens Suessmeyer.

---
http://www.sqlserver2005.de
---

Answer 2

Hi jens,

 

Tried this code but am getting this error messages.

Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near 'FUNCTION'.
Server: Msg 170, Level 15, State 1, Line 24
Line 24: Incorrect syntax near '@SplittedValues'.

am using SQL 7.0

Vic

 

 

Answer 3

Hi,

SQL 7 doesn´t have a clue about UTFs, because they were introduced in SQL2k, you can use this in a SP instead.

CREATE PROCEDURE spSplit
(
        @String VARCHAR(200),
        @Delimiter VARCHAR(5)
)
AS
BEGIN

CREATE TABLE #SplittedValues
(
  OccurenceId SMALLINT IDENTITY(1,1),
  SplitValue VARCHAR(200)
)

DECLARE @SplitLength INT

WHILE LEN(@String) > 0
BEGIN
        SELECT @SplitLength = (CASE CHARINDEX(@Delimiter,@String) WHEN 0 THEN
LEN(@String) ELSE CHARINDEX(@Delimiter,@String) -1  END)

        INSERT INTO @SplittedValues
        SELECT SUBSTRING(@String,1,@SplitLength)

        SELECT @String = (CASE (LEN(@String) - @SplitLength) WHEN 0 THEN ''
ELSE RIGHT(@String, LEN(@String) - @SplitLength - 1) END)
END

Select * from #SplittedValues

END

Called:

CREATE TABLE #SplittedValues
(
  OccurenceId SMALLINT,
  SplitValue VARCHAR(200)
)

INSERT INTO #SplittedValues
EXEC spSplit @MULTI,','

SELECT MSISDN, IMSI, HLR_NO
FROM HLR
INNER JOIN #SplittedValues SplittedValues
ON HLR.MSISDN  = SplittedValues.SplitValue
ORDER BY MSISDN


(Untested)

HTH, Jens Suessmeyer.

---
http://www.sqlserver2005.de
---

Answer 4

Thanks jens

Answer 5

I'm surprised nobody has suggested patindex, like this:

Suppose you have a table T with column C

C has some values:

xx

yy

zz

Your stored proc or function takes a string of things you'd like to match on:

@match = '''xx'',''yy'''

select * from T where patindex('%''' + C + '''%',@match) > 0

Note that you have to make sure your @match string has appropriate delimiters, which forces a match on the entire C when C is surrounded by the same delimiter.

Interestingly, if you have been passing an IN clause into SQL from code, that's probably exactly what you're doing.

Probably not really high performance, but useful in a pinch.

 

 

Answer 6

I'm surprised nobody has suggested patindex, like this:

Suppose you have a table T with column C

C has some values:

xx

yy

zz

Your stored proc or function takes a string of things you'd like to match on:

@match = '''xx'',''yy'''

select * from T where patindex('%''' + C + '''%',@match) > 0

Note that you have to make sure your @match string has appropriate delimiters, which forces a match on the entire C when C is surrounded by the same delimiter.

Interestingly, if you have been passing an IN clause into SQL from code, that's probably exactly what you're doing.

Probably not really high performance, but useful in a pinch.

 

 

I like the above workaround , it's simple, you don't need to create any additional sp or function, and it works well, the only drawback is if you cannot control the format of the string with the IDs it can become complicated, but if you do, it's cool. Thanks

Answer 7

Nice solution however I run into a problem. "Argument data type sql_variant is invalid for argument 2 of patindex function."

here's the code I've got:

C#;

_dataTableUsers = tableAdapterUsers.GetDataByApprovals(Approvals) ;// String Approvals = "'''4689'',''4799'''";

SQL:

WHERE        (PATINDEX('%''' + CAST(id AS Varchar(20)) + '''%', @param) > 0)

Answer 8

 It worked!!!!!!

Thanks a lot.

Answer 9

Nice :-)