Get output of SQL Procedure and assign it to a variable used in WHERE Clause

• ms-techie

  SSChasing Mays

  Points: 627

  More actions

  March 25, 2014 at 3:26 am

  #305581

  Get output of SQL Procedure and assign it to a variable used in WHERE Clause

  Later I want to use this variable in my code in the WHERE Clause

  Declare @ProjectNo nvarchar(10)

  --Now I want to assign it to output of a storedprocedure which returns only 1 value and use it in the below SELECT query.

  SELECT ID from TABLEA where Project in (@ProjectNo)

  How to do it. How to assign @ProjectNo to output of storedProcedure called 'GetProjNumber'

  Please help

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  March 25, 2014 at 3:30 am

  #1699759

  How does the procedure return that? Return code, output variable or resultset?

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• djj

  SSCoach

  Points: 18831

  More actions

  March 25, 2014 at 6:43 am

  #1699837

  ms-techie (3/25/2014)

  ---

  Get output of SQL Procedure and assign it to a variable used in WHERE Clause

  Later I want to use this variable in my code in the WHERE Clause

  Declare @ProjectNo nvarchar(10)

  --Now I want to assign it to output of a storedprocedure which returns only 1 value and use it in the below SELECT query.

  SELECT ID from TABLEA where Project in (@ProjectNo)

  How to do it. How to assign @ProjectNo to output of storedProcedure called 'GetProjNumber'

  Please help

  Is @ProjectNo a single value?

• SQLBlimp

  SSCertifiable

  Points: 6340

  More actions

  March 25, 2014 at 2:30 pm

  #1700063

  ms-techie (3/25/2014)

  ---

  Get output of SQL Procedure and assign it to a variable used in WHERE Clause

  Later I want to use this variable in my code in the WHERE Clause

  Declare @ProjectNo nvarchar(10)

  --Now I want to assign it to output of a storedprocedure which returns only 1 value and use it in the below SELECT query.

  SELECT ID from TABLEA where Project in (@ProjectNo)

  How to do it. How to assign @ProjectNo to output of storedProcedure called 'GetProjNumber'

  Please help

  Assuming that your select will always return 1 ID is not safe.

  This is what I would write:

  CREATE PROCEDURE GetProjNumber ( @ProjectNo int, @ID int OUTPUT)

  AS

  BEGIN

  SELECT @ID = TOP 1 ID from TABLEA where project in (@ProjectNo);

  END;