IF / ELSE depending on result of stored procedure

database, sql, sql-server, stored-procedures, t-sql

Solution

You should not use a stored procedure for this and use a function instead to check if the Admin Exists.

Then in your new stored procedure call the CheckAdminExists function:

CREATE FUNCTION [dbo].[CheckAdminExists] (@SID NVARCHAR(50), @AdminName NVARCHAR(MAX))
RETURNS BIT
AS 
BEGIN
DECLARE @RetVal INT

SELECT @RetVal = COUNT(Administrator.ID)
FROM
    Administrator
WHERE
    Administrator.SID = @SID
    AND Administrator.Name = @AdminName

IF @RetVal > 0 
BEGIN
   RETURN 1
END
    RETURN 0
END

Then in your stored procedure call the function:

DECLARE @AdminExists BIT
SELECT @AdminExists = [dbo].[CheckAdminExists]

IF @AdminExists 
BEGIN
   -- your code 
END
ELSE
BEGIN
  -- your code
END

Problem

I've the following stored procedure: ``` ALTER PROCEDURE [dbo].[CheckAdminExists] @SID NVARCHAR(50), @AdminName NVARCHAR(MAX) AS SELECT Administrator.ID FROM Administrator WHERE Administrator.SID = @SID AND Administrator.Name = @AdminName GO ``` Now I would like to create another SP with a code like that: ``` IF NOT NULL (EXECUTE CheckAdminExists 'S-1','Admin') --do something ELSE --do something else ``` What's the right syntax for doing it?

Original source