possible in tsql? return one row then access individual fields from it?

Question

Hello,
Let say within a stored procedure you need to get several values that you need to be able to reference individually, but all of these values live within one record of a table. Instead of writting several individual statements like this:

declare @sampleVar1 nvarchar(50)
set @sampleVar1 = (select sampleField1 from sampleTable1 where PKID = 999)

declare @sampleVar2 nvarchar(50)
set @sampleVar2 = (select sampleField2 from sampleTable1 where PKID = 999)

declare @sampleVar3 nvarchar(50)
set @sampleVar3 = (select sampleField3 from sampleTable1 where PKID = 999)

etc...

is it possible to just use one select statement and store the whole row in a variable that you could subsequently access individual fields from? something like:

declare @theRow (not sure on datatype if this is possible)
set @theRow = (select * from sampleTable1 where PKID = 999)

declare @sampleOutput nvarchar(max) = 'some labels ' + @theRow.sampleField1 + ' some more text ' + @theRow.sampleField2

etc..

?

Answer 1

You can't access the row this way. If you need to get part of the @TheRow you have to use something like substring.
Also, I see that you are retrieving three values from three different columns in the following statement and not just one or part of a row. What are you trying to do may be we can help out.


declare @sampleVar1 nvarchar(50)
set @sampleVar1 = (select sampleField1 from sampleTable1 where PKID = 999)

declare @sampleVar2 nvarchar(50)
set @sampleVar2 = (select sampleField2 from sampleTable1 where PKID = 999)

declare @sampleVar3 nvarchar(50)
set @sampleVar3 = (select sampleField3 from sampleTable1 where PKID = 999)

---

Abdallah, PMP, MCTS

Answer 2

One way to obtain your values:

DECLARE @v1 nvarchar(50), @v2 nvarchar(50);
SELECT
    @v1 = sampleField1,
    @v2 = sampleField2
FROM
    sampleTable1
WHERE
    (PKID = 999)
;

Answer 3

One way to obtain your values:

DECLARE @v1 nvarchar(50), @v2 nvarchar(50);

SELECT

    @v1 = sampleField1,

    @v2 = sampleField2

FROM

    sampleTable1

WHERE

    (PKID = 999)

;

Radu, he is already doing that. His question is to see if he can create some kind of a variable and hold the enitre dataset that comes back from the table in that one variable. More like a datareader in .Net.

---

Abdallah, PMP, MCTS

Answer 4

You could simply use @theRow as a table variable, perhaps?

Whether it's simply one row or a table with one row, there doesn't seem to be a whole lot of difference in execution.

declare @theRow table (sample1 int, sample2 bit, sample3 varchar(30))

insert into @theRow
select sample1, sample2, sample3 from sampleTable where ID = 5

declare @sampleOutput varchar(max)

select @sampleOutput = 'stuff' + r.sample1 + 'more stuff' + r.sample2 from @theRow as r


Although, you can skip the variable part altogether and just select @sampleOutput = 'stuff' + r.sample1 from sampleTable where ID = 5

Unless I'm missing what you're trying to do (which is certainly possible).

Answer 5

Have you tried the table data type? Like


DECLARE @theRow TABLE

---

Abdallah, PMP, MCTS

Answer 6

thank you both for the input. To clarify, I was really looking to see if there was a way to get at all the data I needed with one call to the database rather than the multiple individual calls I was making... I only had the 3 individual calls there as an example but it is more like a dozen or so I will be making. It occurred to me that this may be ineffecient, as I typically only to very basic tsql queries and don't have much experience with the language beyond that. So I think Radu Negru's first response is right on the money... having all the data accessible from one variable is not required, its just the first thing I thought of coming from a .net background...

Answer 7

Hi,

I would use XML for a situation like this. Other solutions are appreciated.

Declare @xmlData XML

Set @xmlData = 
(
select
	[col1] "@Col1"
  , [col2] "@Col2"
  , [col3] "@Col3"
from 
	#test
Where
	[id] = 1
For XML Path
)

declare @sampleOutput nvarchar(max)

set @sampleOutput = 
(
SELECT
	'some labels ' + @xmlData.query('data(row/@Col1)').value('.','varchar(100)')
	+ ' more text ' + @xmlData.query('data(row/@Col2)').value('.','varchar(100)')
	+ ' some more text ' + @xmlData.query('data(row/@Col3)').value('.','varchar(100)')
	AS Val
)
print @sampleOutput

Just a rough code, can definitely be made better. There are good resources in http://blog.beyondrelational.com/ for you to get started with XML.

Cheers

Padmanabhan

Answer 8

I'm glad you find your answer c0pe! Now, help us help you. What is that you are trying to do? I have a feeling that it's not really intented to T-SQL.

Answer 9

Hi,

you should be able to declare a variable of type table and then store the values in that table.