CODEWITHSUNDEEP
sql-servert-sql
AznDevil92
AznDevil92

Reputation: 554

If executing the variable fails then stop query - SQL

How would I convert this logic into a SQL statement? 

IF @GrantSQL is successful, then continue to insert, if not stop query

This is the stored procedure: 

BEGIN
    DECLARE @GrantSql NVARCHAR(500)

     SET @GrantSql = 'EXEC sp_addsrvrolemember [' + @LoginName + '], ''sysadmin'''

     EXEC sp_executesql @GrantSql

     -- IF @GrantSQL is successful, then continue to insert, if not stop query
     BEGIN
         INSERT INTO....
     END
END

Upvotes: 0

Views: 438

Answers (4)

Mikhail Lobanov
Mikhail Lobanov

Reputation: 3026

DECLARE @GrantSql INT

EXEC @GrantSql = sp_addsrvrolemember @LoginName, 'sysadmin'

IF GrantSql = 0
BEGIN
    INSERT INTO...
END

Upvotes: 3

GarethD
GarethD

Reputation: 69769

You can use an output parameter with sp_executesql to capture the return value of a stored procedure (0 for success, 1 for failure):

DECLARE @GrantSql NVARCHAR(500);
DECLARE @ReturnValue INT;
SET @GrantSql = 'EXEC @ReturnValue = sp_addsrvrolemember [' + @LoginName + '], ''sysadmin'';';
EXEC sp_executesql @GrantSql, N'@ReturnValue INT OUTPUT', @ReturnValue OUT;

IF @ReturnValue = 0
BEGIN
    ....
END

But of course, you don't need dynamic SQL, you could simply use:

DECLARE @ReturnValue INT;

EXEC @ReturnValue = sp_addsrvrolemember @LoginName = @LoginName, @rolename = 'sysadmin';
IF @ReturnValue = 0
BEGIN
    ....
END

It also seems very unlikely that you would need to bulk add people to the sysadmin role. I don't know what you are trying to achieve, but it is probably not the right approach

Upvotes: -1

Alan Burstein
Alan Burstein

Reputation: 7918

Something like:

BEGIN
    DECLARE @GrantSql NVARCHAR(500)

     SET @GrantSql = 'EXEC sp_addsrvrolemember [' + @LoginName + '], ''sysadmin'''


     BEGIN TRY
       EXEC sp_executesql @GrantSql
     END TRY
    --'IF @GrantSQL is successful than continue to insert if not stop query'
    BEGIN CATCH
      PRINT 'Oh man, this happened: '+ @@ERROR
      GOTO allDone
    END

    -- no errors. We're good to continue...
    BEGIN
        INSERT INTO....
    END

allDone:
END

Upvotes: -1

criticalfix
criticalfix

Reputation: 2870

Try:

IF @@ERROR <> 0

See the documentation for @@ERROR. Also be aware that @LoginName could contain something bad, such as '; drop table students;'. That's a SQL injection vulnerability, and in general you'll want to avoid sp_executesql and parameterize your queries. See Lobanov's answer, which is better.

Upvotes: 1

Related Questions