If the RowCount is one, can you ...?

[donutman]

Is there a way to "get" access to more than just one column from a result set that has only 1 row (without running the query multiple times)? Something similar to Select @V1=aColumn, @V2=anotherColumn from aTable where aCondition (which unfortunately doesn't work).
-Karl

 

[roccorocks]

So please clarify. For every one row of data, you want a row for each column in the original row. If you had 40 columns in that one row, you would want 40 rows of data?

Tim R

 

[donutman]

No, I want to run the following statement (which doesn't work):
Select Top 1 @V1=Column1, @V2=Column2 from MyTable

without doing this:

Select Top 1 @V1=Column1 from MyTable
Select Top 1 @V2=Column2 from MyTable

One reason you might want this is because you can't be sure that the same row is being retrieved each time aside from the inefficience of running it twice.
-Karl

 

[SQLSister]

That should work. This worked for me and it is essentially the same syntax:

declare @test as varchar (12)
declare @test2 as varchar (50)

select @test = ProposalNumber, @test2 = Owner from Proposals where proposalnumber = '0'

Are you sure that there was only one result to the select statement if you didn't assign values to variables?

 

[donutman]

OMG, I've been working under the assumption that that construct would not work! I guess that old saw about assumptions applies here As you suggest I probably tried it once and when it failed (because of reccount>1) I filed it away in my brain that it never works!
-Karl