Post reply

passing parameter to stored procedure

  • December 9, 2009 at 1:15 pm

    #385920

    I have a stored procedure that I am trying to pass multiple int values into via a single varchar parameter. StageID in the temp table is int data type.

    --Stored Procedure

    @strStageID varchar(256)

    SELECT Name FROM #tmpECOWorkflowStatus

    WHERE StageID = @strStageID

    --Stored Procedure

    I am passing the following from an .NET application (@strStageID):

    cmdSQL.Parameters.Add("@strStageID", SqlDbType.VarChar, 256).Value = StageIDs;

    This is the value of StageIDs:

    Cast('165' AS int) OR StageID = Cast('0' AS int) OR StageID = Cast('1' AS int)

    I get the following error when I invoke the application:

    Conversion failed when converting the varchar value 'Cast('165' AS int) OR StageID = Cast('0' AS int) OR StageID = Cast('1' AS int)' to data type int.

    I have also tried to pass the following with a similiar error:

    165 OR StageID = 0 OR StageID = 1

    Thanks!

  • December 9, 2009 at 1:33 pm

    #1090196

    Would help if you posted your code. Not much help otherwise.

  • December 9, 2009 at 2:01 pm

    #1090210

    Do you always have to check for StageID=0 OR StageID=1??

    If yes, then I would make your StageID parameter passed to the SP as int and change the SP to look like this:

    --Stored Procedure

    @intStageID int

    SELECT Name FROM #tmpECOWorkflowStatus

    WHERE StageID = @intStageID OR StageID=0 OR StageID=1

    --Stored Procedure

    Your .NET Code will look like this (syntax may not be right):

    StageIDs="165";

    cmdSQL.Parameters.Add("@intStageID", SqlDbType.Int).Value = Convert.ToInt32(StageIDs);

    Makes sense?

    HTH,

    Supriya

  • December 9, 2009 at 2:09 pm

    #1090215

    Lynn Pettis (12/9/2009)

    Would help if you posted your code. Not much help otherwise.

    I have worked on .NET a little so tried to provide a solution to the OP. I hope I haven't offended you.

    Regards,

    Supriya

  • December 9, 2009 at 2:10 pm

    #1090217

    Thanks!

    The StageID values are 0 to 200 and can be selected as a single value or multiple values in the ASP application.

    I am trying to pass a single parameter from the ASP to the stored procedure that will work with:

    Select Name From Table Where StageID = 1

    or

    Select Name From Table

    Where StageID = 1

    Or StageID = 23

    Or StageID = 99

    Or StageID = 159

    Thank you!

  • December 9, 2009 at 2:12 pm

    #1090221

    For list-type parameters with multiple values in a single parameter, you're generally better off either passing them as an XML parameter or else using a #temp table instead.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • December 9, 2009 at 2:17 pm

    #1090225

    Oops, I completely missed that this is the SQL Sever 2008 forum! You can just pas the value as a table-valued parameter. That is by far the best solution.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • December 9, 2009 at 2:41 pm

    #1090246

    Would that depend on how the application passing the value back to the database? Not too familiar with ASP.

  • December 9, 2009 at 3:18 pm

    #1090264

    Lynn Pettis (12/9/2009)

    Would that depend on how the application passing the value back to the database? Not too familiar with ASP.

    Oh definitely, but ASP code is just as editable as SQL Server code.

    Based on the OP's first sentence:

    I have a stored procedure that I am trying to pass multiple int values into via a single varchar parameter

    the "trying" implies to me that code change is possible on either side.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • December 11, 2009 at 4:14 am

    #1091069

    You can modify the sql such that it gets parameter with semicon

    select * from <table> where stageId in (<your parameter>)

    Or

    Write the sql with the parameter and assign it to the variable. then execute the sql using executesql

Viewing 10 posts - 1 through 9 (of 9 total)

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