Can Sproc return values be used in a query (update

• John King

  Say Hey Kid

  Points: 705

  More actions

  May 5, 2002 at 3:51 pm

  #78616

  Hi:

  This may be a reach, but here goes:

  I have a stored procedure that returns a list of values as an output parameter:

  @strLicenseSNHistory = 'SN1, SN2, SN3'

  Can I use that stored procedure in a correlated query, specifically to update a field with the value of the output parameter?

  Something like the following SELECT query

  SELECT T1.Serial_No,

  (SET @strLicenseSNHistory = EXEC LicenseSNHistory

  @strSN = T1.Serial_No,@strLicenseSNHistory = @strLicenseSNHistory OUTPUT)

  FROM AccountLicenseDetail AS T1

  This is probably something better done using User-Defined Functions, but I only have access to SQL 7.0 for now. I believe that UDFs are only available in SQL 2000.

  A related question:

  Can I use a correleted query to provide a Stored Procedure with input values? Or do I have to handle that on a row by row basis, whether through SQL cursors or ADO?

  Thanks for your time.

  JK

• Antares686

  SSC Guru

  Points: 125444

  More actions

  May 5, 2002 at 7:59 pm

  #428477

  You can mix varible setting with select output at the same time.

  You can

  SELECT @var = colx FROM tblx

  Or

  SELECT colx FROM tblx

  but not

  SELECT colx, @var = colx FROM tblx

  nor can you do a select with a call to an sp in it, must be UDF.

  "Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

• Eve

  SSCrazy

  Points: 2480

  More actions

  May 6, 2002 at 9:08 am

  #428503

  quote:

  ---

  Hi:

  This may be a reach, but here goes:

  I have a stored procedure that returns a list of values as an output parameter:

  @strLicenseSNHistory = 'SN1, SN2, SN3'

  Can I use that stored procedure in a correlated query, specifically to update a field with the value of the output parameter?

  Something like the following SELECT query

  SELECT T1.Serial_No,

  (SET @strLicenseSNHistory = EXEC LicenseSNHistory

  @strSN = T1.Serial_No,@strLicenseSNHistory = @strLicenseSNHistory OUTPUT)

  FROM AccountLicenseDetail AS T1

  This is probably something better done using User-Defined Functions, but I only have access to SQL 7.0 for now. I believe that UDFs are only available in SQL 2000.

  A related question:

  Can I use a correleted query to provide a Stored Procedure with input values? Or do I have to handle that on a row by row basis, whether through SQL cursors or ADO?

  Thanks for your time.

  JK

  ---

• Antares686

  SSC Guru

  Points: 125444

  More actions

  May 6, 2002 at 8:21 pm

  #428551

  Epol if I understand you right then thru a cursor or While loop. You cannot use call a procedure in a SELECT statement.

  "Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)