June 16, 2011 at 5:59 am
sql stored Procedure pass single Parameter @Where
How to Pass more than one value from .cs(asp.net) in single parameter @Where....
June 16, 2011 at 6:04 am
You can pass single value at a time using a single variable.
Ryan
//All our dreams can come true, if we have the courage to pursue them//
June 16, 2011 at 6:06 am
No more than one value pass in single parameter
June 16, 2011 at 9:22 am
You could be sneaky and build a string that is sent to the stored procedure as the parameter.
EG
declare @string varchar(100)
set @string = 'value1;value2'
exec mystoredproc @string
Then in the stored proc, you can split the string using a function and the ';' as a delimiter. Let @string = @where
CREATE FUNCTION dbo.fnSplit(
@sInputList VARCHAR(8000) -- List of delimited items
, @sDelimiter VARCHAR(8000) = ',' -- delimiter that separates items (default)
) RETURNS @List TABLE (item VARCHAR(8000))
BEGIN
DECLARE @sItem VARCHAR(8000)
WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0
BEGIN
SELECT
@sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,@sInputList,0)-1))),
@sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList))))
IF LEN(@sItem) > 0
INSERT INTO @List SELECT @sItem
END
IF LEN(@sInputList) > 0
INSERT INTO @List SELECT @sInputList -- Put the last item in
RETURN
END
GO
Call the function inside the stored procedure
select * from dbo.fnSplit(@where, ';')
The above function will return
Item
value1
value2
You could modify the function to make the results easier to work with.
Hope this helps
taybre
HTH :-):-)
taybre
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
June 16, 2011 at 12:56 pm
You can also create a table variable which can hold multiple rows and may be passed as a parameter.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
June 16, 2011 at 11:07 pm
Thank For ur suggestion......
but More than one value Pass in .cs(asp.net with c#)single parameter @Where
June 17, 2011 at 4:17 am
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply