Check if select query has results inside SQL Server stored procedure

Question

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.

Answer 1

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

Answer 2

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

Answer 3

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.)