?
Solved

Fill multiple variables with only one select

Posted on 2009-04-16
5
Medium Priority
?
357 Views
Last Modified: 2012-05-06
Hello dear experts,

I am wondering if it is possible to fill multiple variables at the same time in T-SQL.

i.e:
SELECT @FBText = (SELECT ProjectFeedbackText FROM Project_Feedback WHERE FeedbackId = @FBId)
SELECT @FBDate = (SELECT PostDate FROM Project_Feedback WHERE FeedbackId = @FBId)
SELECT @FBSenderId = (SELECT UserId FROM Project_Feedback WHERE FeedbackId = @FBId)

Is it possible to fill the 3 variables with only one select, without using a cursor ?
Thanks
0
Comment
Question by:EPCOS
5 Comments
 
LVL 60

Accepted Solution

by:
chapmandew earned 2000 total points
ID: 24160115
sure:

SELECT @FBText =ProjectFeedbackText, @FBDate = PostDate, @FBSenderId = UserId
 FROM Project_Feedback WHERE FeedbackId = @FBId
0
 
LVL 25

Expert Comment

by:reb73
ID: 24160121
Yes -

SELECT  TOP 1
             @FBText = ProjectFeedbackText
            ,@FBDate = PostDate
            ,@FBSenderId = UserId
FROM
         Project_Feedback WHERE FeedbackId = @FBId


(Using Top 1 is to ensure there are not multiple values being returned which can cause an error)
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 24160133
if FeedbackId is unique,no reason to use TOP 1
0
 

Author Closing Comment

by:EPCOS
ID: 31571101
Thank you :)
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 24160165
Also, more than one record being returned will NOT result in an error with reb's example.  It will just only return one set of values, not all.

SELECT  TOP 1
             @FBText = ProjectFeedbackText
            ,@FBDate = PostDate
            ,@FBSenderId = UserId
FROM
         Project_Feedback WHERE FeedbackId = @FBId



If more than one record could be returned for a single feedbackID, then this would result in an error:

SELECT @FBText = (SELECT ProjectFeedbackText FROM Project_Feedback WHERE FeedbackId = @FBId)
SELECT @FBDate = (SELECT PostDate FROM Project_Feedback WHERE FeedbackId = @FBId)
SELECT @FBSenderId = (SELECT UserId FROM Project_Feedback WHERE FeedbackId = @FBId)
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question