CODEWITHSUNDEEP
sqlsql-serverstored-procedures
Moelbeck
Moelbeck

Reputation: 640

SQL Set variable to select result

I was wondering if it is possible to set a declared variable to a return value from a select result? Something like:

@WatchedSeconds
SET @WatchedSeconds = 200
DECLARE @SelectedVideo int
SET @SelectedVideo = (SELECT TOP 1 * FROM Video v WHERE v.VideoID = 12)

IF @SelectedVideo IS NOT NULL
   BEGIN
      IF @SelectedVideo.VideoLength = @WatchedSeconds
        BEGIN
           --DO SOMETHING
        END
      IF @SelectedVideo.SomeOtherColumn = @SomethingElse
        BEGIN
        END
   END

It's for using some information from the SELECT result multiple places in a Stored Procedure. I know that I can set a variable to e.g, a integer, and set it to the selected result, if it returns a integer, e.g:

DECLARE @VideoSeconds int
SET @VideoSeconds = (SELECT v.Length FROM Video v WHERE v.VideoID = @VideoID)

This way I have to make multiple variables, and multiple SELECT calls if I need to use more values from the Video result. And that's what I want to avoid.

Upvotes: 3

Views: 13836

Answers (4)

Matt Allwood
Matt Allwood

Reputation: 1438

If what you're trying to get is similar to returning a dataset in a procedural language (so you can type something like Result.Field1 = 'Test') then I don't think this is possible. You'll just need to declare multiple variables and make the SELECT call as 

SELECT TOP 1 @var1=col1, @var2=col2, @var3=col3, [...] FROM YourTable WHERE YourFilter

as @Shnugo suggests

The 'dataset' equivalent structure in SQL is cursors, but they require variables to be set up as well, so there's no benefit there.

Upvotes: 0

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67321

You can try something like

(declare variables first...)

SELECT TOP 1 @var1=col1, @var2=col2, @var3=col3, [...] FROM YourTable WHERE YourFilter

EDIT: All together this seems not to be the best approach... With SQL you should not think in values and single rows but rather in result sets (set based programming). Your thinking leads to many tiny selects, while loops, cursors and all this stuff one should avoid.

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1270873

You can store the results in a temporary table or table variable:

SELECT TOP 1 *
INTO #SelectedVideo
FROM Video v
WHERE v.VideoID = 12;

Then you can assign values from the table later in your code. Something like:

IF ( (SELECT VideoLength FROM #SelectedVideo) = @WatchedSeconds)

However, for your particular example, if you have an index on video(VideoId), then there is little to be gained performance-wise from using a temporary table.

Upvotes: 0

Radu Gheorghiu
Radu Gheorghiu

Reputation: 20509

You can do this simply by running:

SELECT @videoSeconds = v.Length FROM Video v WHERE v.VideoID = @VideoID

so as to not add the SET part.

Also, you must make sure that only 1 row is being returned by the query, otherwise it will generate an error.

Upvotes: 4

Related Questions