Problem in Passing Parameter

  • sql stored Procedure pass single Parameter @Where

    How to Pass more than one value from .cs(asp.net) in single parameter @Where....

  • 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//

  • No more than one value pass in single parameter

  • 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/

  • 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

  • Thank For ur suggestion......

    but More than one value Pass in .cs(asp.net with c#)single parameter @Where

  • create a table data type and use that as the parameters data type for the procedure.

    something like

    create type abcd table ( columns and definitions)

    grant the execute permission on the type

    then

    create proc something @where abcd

    as

    BEgin

    end

    Jayanth Kurup[/url]

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply