locked
Passing Multiple Values to Stored Procedure as a parameter RRS feed

  • Question

  • I have two tables

    in one table i have a RecordID and RecordIdentifier columns..

    Every recordID has different RecordIdentifers like:

    01             Abc

    01             EFG

    In the other table i have RecordIdentifier and Record Description.

    I need to join this two tables to get recordId and RecordIdentifier and pass the output values to a stored procedure as a parameter.

    In my stored procedure i need to get the record description by joining these two tables and in the where clause passing these parameter values... My idea is like this:

    Declare @RecID char(2), @RecIdentifier varchar(30)

    set @recId = (select RecId from table A join table B on B.RecIdentifier = A.RecIdentifier where status = 'Pending)

    set @recIdentifier = (select RecIdentfier from table A join table B on B.RecIdentifier= A.RecIdentifier where status = 'pending' )

    Exec spname @rid = @RecID,@RecIden = @RecIdentifier...

    This is my plan...

    But i am getting error when i am running the query as subquery returned more than 1 value... I understood the error.. But is there any other way to fix this..

    Kindly let me know your views how to approach this..


    Thanks, Anji

    Tuesday, July 24, 2012 9:19 PM

Answers

  • You need to pass in parameters into the sp to get the unique row you are looking for:

    create proc test

    @UniqueID

    AS

    Declare @something as varchar (50)

    BEGIN

    Set  @something  =

    (       Select B.Name

            FROM TableA A 

            LEFT JOIN TABLEB B

            ON A.ID = B.ID

            WHERE A.ID = @UniqueID)

    SELECT @something

    END

    Something like that should do it... other than that use logic to work through the table each row at a time

    Good Luck!

    • Proposed as answer by SQLSpecialist Tuesday, July 24, 2012 9:35 PM
    • Marked as answer by Iric Wen Wednesday, August 1, 2012 9:12 AM
    Tuesday, July 24, 2012 9:35 PM

All replies

  • You need to pass in parameters into the sp to get the unique row you are looking for:

    create proc test

    @UniqueID

    AS

    Declare @something as varchar (50)

    BEGIN

    Set  @something  =

    (       Select B.Name

            FROM TableA A 

            LEFT JOIN TABLEB B

            ON A.ID = B.ID

            WHERE A.ID = @UniqueID)

    SELECT @something

    END

    Something like that should do it... other than that use logic to work through the table each row at a time

    Good Luck!

    • Proposed as answer by SQLSpecialist Tuesday, July 24, 2012 9:35 PM
    • Marked as answer by Iric Wen Wednesday, August 1, 2012 9:12 AM
    Tuesday, July 24, 2012 9:35 PM
  • But i am getting error when i am running the query as subquery returned more than 1 value... I understood the error.. But is there any other way to fix this..

    This is because the join query returns more than one row.  You can do like what you are trying to do only when the join query returns only one row.  However, you cannot guarantee that unless until you use a column that is unique in the table in the WHERE clause. Otherwise there are always chances of getting multiple rows.

    If you would like to call the SP for multiple rows, then use cursors for the same and call the SP from the cursor.

    Moreover, You can assign the variable values using a single select statement as mentioned below.

    select @recId = RecId, @recIdentifier = RecIdentfier  from table A join table B on B.RecIdentifier = A.RecIdentifier where status = 'Pending.

    • Edited by Murali_CHN Wednesday, July 25, 2012 3:45 AM
    Wednesday, July 25, 2012 3:41 AM