sql server - Set Output Parameter to equal result of Select statement -

sql server - Set Output Parameter to equal result of Select statement -

i work ms-access 2010 frontend, called stored procedures , ms- sql server 2008 backend, apply scripts stored procedures. created stored procedure retrieves testid input parameters match values in 2 joined tables.my issue trying capture select statement returns output parameter. here sql stored procedure

create procedure upgettestidforanalyte @woid nvarchar(60), @sampleid nvarchar(60),@analyte nvarchar(60), @recordsaffected int out select testid = t1.testid tblwosampletest t1 bring together tbltest t2 on t1.testid=t2.testid; @woid = t1.woid , @sampleid = t1.sampleid , @analyte = t2.analyte set @recordsaffected = @@identity go

i researched ways , seems should utilize set statement don't know how capture value. illustration returns zero.

i thinking maybe work:

create procedure upgettestidforanalyte @woid nvarchar(60), @sampleid nvarchar(60),@analyte nvarchar(60), @recordsaffected int out select @recordsaffected = (select testid = t1.testid tblwosampletest t1 bring together tbltest t2 on t1.testid=t2.testid; @woid = t1.woid , @sampleid = t1.sampleid , @analyte = t2.analyte) go

but didn't work either. there way store homecoming values of select statement variable?

if looking @ returning testid , provided have 1 testid match criteria - can utilize next select statement -

select @recordsaffected = t1.testid tblwosampletest t1 bring together tbltest t2 on t1.testid=t2.testid; @woid = t1.woid , @sampleid = t1.sampleid , @analyte = t2.analyte

in addition, want rename output parameter (@outputtestid or @testid) since name @recordsaffected gives wrong indication of meant for.

sql-server stored-procedures output-parameter sql