SQL Stored Procedure set variables using SELECT

Question

I have a stored procedure in SQL Server 2005 with multiple variables and I want to set the values of these variables using a select statement  All three variables come from a same table and there should be a way to set them using one select statement instead of the way I currently have as shown below  Please help me to figure it out   DECLARE  currentTerm nvarchar max   DECLARE  termID int  DECLARE  endDate datetime  SET  currentTerm         Select CurrentTerm from table1 where IsCurrent   1    SET  termID         Select TermID from table1 where IsCurrent   1    SET  endDate         Select EndDate from table1 where IsCurrent   1

User · Accepted Answer

select  currentTerm   CurrentTerm   termID   TermID   endDate   EndDate     from table1     where IsCurrent   1

User · Answer

One advantage your current approach does have is that it will raise an error if multiple rows are returned by the predicate  To reproduce that you can use   SELECT  currentTerm   currentterm          termID   termid          endDate   enddate FROM   table1 WHERE  iscurrent   1  IF    ROWCOUNT  lt  gt  1     BEGIN       RAISERROR   Unexpected number of matching rows                    16                   1         RETURN   END

[sql-server-2005] SQL Stored Procedure set variables using SELECT

Examples related to sql-server-2005

Examples related to stored-procedures

Examples related to select