-
July 16th, 2008, 04:02 AM
#1
Passing value to the parameter "IN (@par)"
I have wrote a stored procedure which like
CREATE PROCEDURE P1
(
@par1 string,
)
as
select * from table1 where column1 in (@par1)
So, here the Value which i send to the parameter is "value1,value2" like.
Its not possible for me to pass the parameter.
Could you suggest how to do this with the same way or different way.
Thanks in advance,
Regards,
Sivakumar.kr.
Edit/Delete Message
Sivakumar Kuduva Ramamoorthy
-
July 16th, 2008, 04:16 AM
#2
Re: Passing value to the parameter "IN (@par)"
You do not specify which database it is, but for SQL Server there is a way of doing this - but it isn't "pretty", so I would much rather advice you to rework the method.
However, basically - you need to execute the a string so you can parse in a comma separated string.
Something along this line:
Code:
CREATE PROCEDURE P1
(
@par1 varchar(max)
)
as begin
exec('select * from table1 where column1 in (' + @par1 + ')')
end
And then call the sproc with an input string which looks something like:
Code:
EXEC P1 '''value1'',''value2'''
But it isn't a "pretty" way of doing this, and I wouldn't really recommend doing it.
-
July 16th, 2008, 04:33 AM
#3
Re: Passing value to the parameter "IN (@par)"
Sivakumar Kuduva Ramamoorthy
-
July 16th, 2008, 05:53 AM
#4
Re: Passing value to the parameter "IN (@par)"
Best way is to use a function that turns a delimited string into a table, and in your sp, fill a temp table with your values, and use this temp table in your where clause, or as inner join in your query.
here's a function to turn a bunch of parameters into a table.
Code:
CREATE FUNCTION dbo.fnSplit (
@vcDelimitedString varchar(8000),
@vcDelimiter varchar(100) )
RETURNS @tblArray TABLE
(
ElementID smallint IDENTITY(1,1), --Array index
Element varchar(1000) --Array element contents
) AS
BEGIN
DECLARE
@siIndex smallint,
@siStart smallint,
@siDelSize smallint
SET @siDelSize = LEN(@vcDelimiter)
WHILE LEN(@vcDelimitedString) > 0
BEGIN
SET @siIndex = CHARINDEX(@vcDelimiter, @vcDelimitedString)
IF @siIndex = 0
BEGIN
INSERT INTO @tblArray VALUES(@vcDelimitedString)
BREAK
END
ELSE
BEGIN
INSERT INTO @tblArray VALUES(SUBSTRING(@vcDelimitedString, 1,@siIndex - 1))
SET @siStart = @siIndex + @siDelSize
SET @vcDelimitedString = SUBSTRING(@vcDelimitedString, @siStart , LEN(@vcDelimitedString) - @siStart + 1)
END
END
RETURN
END
On Error Kill(User)
-
July 16th, 2008, 10:56 AM
#5
Re: Passing value to the parameter "IN (@par)"
Which dbms are you using?
If mysql, there is a function called Find_In_Set() which works with a delimited string.
Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rich Cook
0100 1101 0110 1001 0110 0011 0110 1000 0110 0001 0110 0101 0110 1100 0010 0000 0100 0101 0110 1100 0110 1100 0110 0101 0111 0010
-
July 19th, 2008, 07:53 AM
#6
Re: Passing value to the parameter "IN (@par)"
you can also turn it around:
SELECT * FROM table WHERE @par LIKE '%,'+ column+',%'
Pass a string like:
,123,456,789,
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|