CODEWITHSUNDEEP
sqlsql-servert-sqlstored-procedures
Blair
Blair

Reputation: 21

Check if select query has results inside SQL Server stored procedure

I've done this before but can't find where :/

I want to create a variable inside a stored proc and return its value which will be set depending on whether or not other selects return results.

basically something like this:

@myVar   int = 0

BEGIN
IF
  SELECT SomeThing FROM SomeTable
    @myVar = 1
ELSE IF
  SELECT SomeOther From SomeOtherTAble
  @myVar = 2
ELSE
  @myVar = 0
  SELECT @myVar
END

tried that and no dice...and I can't for the life of me remember where I did this in the past.

Upvotes: 2

Views: 15646

Answers (3)

Philip Kelley
Philip Kelley

Reputation: 40319

Sounds like output parameters to me...?

CREATE PROCEDURE dbo.MyProc
   @MyVar  int  output
AS
BEGIN

/*
Your code goes here. Do whatever you like, and set @MyVar as desired.
*/
RETURN

Call it like so:

DECLARE @MyVar  int

EXECUTE dbo.MyProc  @MyVar output

You can set @MyVar before passing it in, like any other variable (it's initialized as null). If you don't have the "output" clause on both the call and the parameter statement, the value will not be passed back. Books Online will have further detail and minutiae.

(You can also use a RETURN value, but in general this should be reserved for error handling.)

Upvotes: 0

anishMarokey
anishMarokey

Reputation: 11397

Are you looking for this

    @myVar   int = 0

    Declare @var int;
    set  @var = 1
    BEGIN
    IF
     BEGIN
      SELECT SomeThing FROM SomeTable
        @myVar = 1
    ELSE IF
      SELECT SomeOther From SomeOtherTAble
      @myVar = 2
    ELSE
      @myVar = 0
      SELECT @myVar
    END
  Return @var
  END

Upvotes: 0

Raj More
Raj More

Reputation: 48024

See if this works for you

Declare @myVar   int
set @myVar = 0

SELECT * FROM dbo.Table1
if @@RowCount <> 0 SET @myVar = 1

If @myVar = 0
begin
    SELECT * FROM dbo.Table2
    if @@RowCount <> 0 SET @myVar = 2
end

If @myVar = 0
begin
    SELECT * FROM dbo.Table3
    if @@RowCount <> 0 SET @myVar = 3
end

Upvotes: 3

Related Questions