28

So, I'm writing this Stored Proc and I really suck at SQL.

My Question to you guys is:

Can I select an entire row and store it in a variable?

I know I can do something like:

declare @someInteger int
select @someInteger = (select someintfield from sometable where somecondition)

But can I select the entire row from sometable and store it in a variable?

EJC
  • 2,062
  • 5
  • 21
  • 33

3 Answers3

45

You can select the fields into multiple variables:

DECLARE @A int, @B int

SELECT
  @A = Col1,
  @B = Col2
FROM SomeTable
WHERE ...

Another, potentially better, approach would be to use a table variable:

DECLARE @T TABLE (
  A int,
  B int
)
INSERT INTO @T ( A, B )
SELECT
  Col1,
  Col2
FROM SomeTable
WHERE ...

You can then select from your table variable like a regular table.

Bennor McCarthy
  • 11,415
  • 1
  • 49
  • 51
  • yeah i guess i could do that... That might be the best solution in this case... I'm basically trying to clean up my atrocious SQL code and this would make it a little cleaner, right now I basically have the same code in the if block that's in the else block... this would at least get rid of some repeated code... – EJC Sep 22 '10 at 21:23
  • @EJC: "right now I basically have the same code in the if block that's in the else block" -- note your current approach may be the better from a performance point of view but obviously you'd need to test. – onedaywhen Sep 23 '10 at 08:53
8

You could create a table variable that matches your table schema and store the single row in it:

declare @myrow table(field0 int,field1 varchar(255))
insert into @myrow
select field0,field1 from mytable where field0=1
brendan
  • 29,308
  • 20
  • 68
  • 109
  • This comes closest to answering the OP requirement (store an entire row in 1 variable) but to make this answer useful, it would be helpful to see how to refer to the individual columns of that row once it is populated. It would be nice to be able to refter to @myrow.field0 and \@myrow.field1 but I doubt that is possible as \@myrow is a table, not a row. It is a table that only has one row but still a table. I have heard of scalar variables and table variables but I have not heard of a row variable. So how would I access a table variable of 1 row as a row variable? – Ted Cohen Oct 31 '19 at 11:01
0

Please see/via:

MSSQL Select statement with incremental integer column... not from a table

SELECT ROW_NUMBER() OVER( ORDER BY Column1, Column2 ) AS 'rownumber',*
FROM YourTable
webMac
  • 185
  • 1
  • 2
  • 11