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
DECLARE @GrantSql INT
EXEC @GrantSql = sp_addsrvrolemember @LoginName, 'sysadmin'
IF GrantSql = 0
BEGIN
INSERT INTO...
END
sp_addsrvrolemember only returns two values, but be aware that the return type of stored procedures is INT, not BIT.
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.
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
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