How Do I....

• Tim OPry

  SSCrazy

  Points: 2988

  More actions

  January 26, 2003 at 3:37 pm

  #80190

  How do I return multiple columns from a single row Select to be used in variables within some TSQL script?

  What I need to accomplish is the equivalent of:

  DECLARE @Var1 money, @Var2 money

  SELECT Column1, Column2 FROM Table WHERE ID=1

  SET @Var1=Column1

  SET @Var2=Column2

  Of course, the two SET statements fail with unknown column names.

  While SET @Var1=(SELECT Column1 FROM Table WHERE ID=1)

  I haven not been able to find a method for doing this for multiple columns within a single SELECT or UDF.

  Currently, I'm using an sProc with several OUTPUT params that in turn uses a cursor to FETCH the column values and return them. This doesn't seem efficient and I cannot help but think there is a better/easier way.

  Any suggestions would be appreciated.

• jpipes

  SSChampion

  Points: 12230

  More actions

  January 27, 2003 at 6:22 am

  #447266

  quote:

  ---

  How do I return multiple columns from a single row Select to be used in variables within some TSQL script?

  would be appreciated.

  ---

  Try:

  SELECT @Var1 = Column1, @Var2 = Column2 FROM Table WHERE ID=1

• Tim OPry

  SSCrazy

  Points: 2988

  More actions

  January 27, 2003 at 7:20 am

  #447273

  Thanks - thought for sure I tried those variations - obviously not.

  So much for long weekends of banging on TSQL - I need to stick to .Net...