Set Output Parameter to equal result of Select statement

Question

I work with a MS-Access 2010 frontend, where I called the stored procedures and MS- SQl server 2008 backend, where I apply the scripts for the stored procedures. I created a stored procedure that retrieves the TestID where the input parameters match the values in two joined tables.My issue is trying to capture what the select statement returns with an output parameter. Here is my SQL stored procedure

CREATE PROCEDURE upGetTestIDForAnalyte @WOID nvarchar(60), @SampleID nvarchar(60),@Analyte nvarchar(60), @RecordsAffected int OUT
AS

SELECT TestID = t1.TestID
FROM tblWOSampleTest t1
JOIN tblTest t2
ON t1.TestID=t2.TestID; 
WHERE @WOID = t1.WOID AND @SampleID = t1.SampleID AND @Analyte = t2.Analyte
set @RecordsAffected = @@Identity

GO

I researched some ways to do it and it seems I should use a SET statement but I don't know really how to capture the value. This example always returns zero.

I was thinking maybe something like this would work:

CREATE PROCEDURE upGetTestIDForAnalyte @WOID nvarchar(60), @SampleID nvarchar(60),@Analyte nvarchar(60), @RecordsAffected int OUT
    AS

    SELECT @RecordsAffected = (SELECT TestID = t1.TestID
    FROM tblWOSampleTest t1
    JOIN tblTest t2
    ON t1.TestID=t2.TestID; 
    WHERE @WOID = t1.WOID AND @SampleID = t1.SampleID AND @Analyte = t2.Analyte)


    GO

But that didn't work either. Is there a way to store the return values of a SELECT statement to a variable?

Is there only one testId returned for each condition [@WOID] = t1.WOID AND [@SampleID] = t1.SampleID AND [@Analyte] = t2.Analyte? Are you looking for the testID or the count of rows that match the criteria. — TMNT2014, Jun 24 '14 at 19:36
There is only one testId returned. I am looking for that particular testID — VictoriaJay, Jun 24 '14 at 20:08

TMNT2014 · Accepted Answer · 2014-06-24T19:50:08.670

If you are looking at returning the testId and provided you have only one testId for the match criteria - you can just use the following as your select statement -

SELECT @RecordsAffected = t1.TestID
FROM tblWOSampleTest t1
     JOIN tblTest t2 ON t1.TestID=t2.TestID; 
WHERE @WOID = t1.WOID AND @SampleID = t1.SampleID AND @Analyte = t2.Analyte

In addition, you would probably want to rename your Output parameter as something like (@OutputTestId or @TestId) since the name @RecordsAffected gives a wrong indication of what its actually meant for.

Set Output Parameter to equal result of Select statement

1 Answers1