I am trying to create a stored procedure through Azure pipeline, however I did not succeed.
Here is my script:
IF NOT EXISTS (SELECT ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME = 'sp_checknpapermission'
AND ROUTINE_TYPE = 'PROCEDURE')
BEGIN
SET QUOTED_IDENTIFIER ON
EXEC('
CREATE PROCEDURE sp_checknpapermission
AS
BEGIN
SET NOCOUNT ON;
DECLARE @UserName NVARCHAR(128);
DECLARE user_cursor CURSOR FOR
SELECT name
FROM sys.database_principals
WHERE name LIKE '%@web.onmicrosoft.com' AND type_desc = 'EXTERNAL_USER';
BEGIN TRY
OPEN user_cursor;
FETCH NEXT FROM user_cursor INTO @UserName;
WHILE @@FETCH_STATUS = 0
BEGIN
IF NOT EXISTS (
SELECT 1
FROM sys.database_role_members AS rm
JOIN sys.database_principals AS r ON rm.role_principal_id = r.principal_id
JOIN sys.database_principals AS u ON rm.member_principal_id = u.principal_id
WHERE u.name = @UserName AND r.name = 'db_owner'
)
BEGIN
PRINT 'The user ' + @UserName + ' does not have db_owner role';
END
ELSE
BEGIN
PRINT 'The user ' + @UserName + ' already has the db_owner role';
END
FETCH NEXT FROM user_cursor INTO @UserName;
END
CLOSE user_cursor;
DEALLOCATE user_cursor;
END TRY
BEGIN CATCH
ROLLBACK;
PRINT ERROR_MESSAGE();
END CATCH;
END
')
END
This is the error I get:
Msg 102, Level 15, State 1, Server sqlserver-rba-dev, Line 17
Incorrect syntax near ‘%’.
Any idea what is wrong here?
11
The error is telling you the problem, you do have syntax errors in your code; many syntax errors.
In truth, there’s no need for the syntax you are using (checking the proc exists and then executing the CREATE in a deferred batch). All supported versions so SQL Server permit the CREATE OR ALTER syntax, and you are using Azure SQL Database, which is based on the latest version of the data engine (and sometimes the next one).
So just use CREATE OR ALTER (and get rid of the sp_ prefix):
CREATE OR ALTER PROCEDURE dbo.checknpapermission
AS BEGIN
SET NOCOUNT ON;
DECLARE @UserName nvarchar(128);
DECLARE user_cursor CURSOR FOR
SELECT name
FROM sys.database_principals
WHERE name LIKE '%@web.onmicrosoft.com'
AND type_desc = 'EXTERNAL_USER';
BEGIN TRY
OPEN user_cursor;
FETCH NEXT FROM user_cursor
INTO @UserName;
WHILE @@FETCH_STATUS = 0 BEGIN
IF NOT EXISTS (SELECT 1
FROM sys.database_role_members rm
JOIN sys.database_principals r ON rm.role_principal_id = r.principal_id
JOIN sys.database_principals u ON rm.member_principal_id = u.principal_id
WHERE u.name = @UserName
AND r.name = 'db_owner') BEGIN
PRINT 'The user ' + @UserName + ' does not have db_owner role';
END;
ELSE BEGIN
PRINT 'The user ' + @UserName + ' already has the db_owner role';
END;
FETCH NEXT FROM user_cursor
INTO @UserName;
END;
CLOSE user_cursor;
DEALLOCATE user_cursor;
END TRY
BEGIN CATCH
ROLLBACK; --Why is this here...?
PRINT ERROR_MESSAGE();
END CATCH;
END;
Better yet, as well, bin that CURSOR; it’ll be awfully slow. We can also get rid of the TRY...CATCH and ROLLBACK (what are you attempt to roll back here?), and PRINTing errors is an anti-pattern You can easily achieve the same results with some simple string aggregation.
CREATE OR ALTER PROCEDURE dbo.checknpapermission
AS BEGIN
SET NOCOUNT ON;
DECLARE @PrintStatement varchar(8000),
@LF char(1) = CHAR(10)
SELECT @PrintStatement = STRING_AGG('The user ' + u.name + ' ' + CASE WHEN r.principal_id IS NULL THEN 'does not have the db_owner role'
ELSE 'already has the db_owner role' END, @LF)
FROM sys.database_principals u
LEFT JOIN sys.database_role_members rm
JOIN sys.database_principals r ON rm.role_principal_id = r.principal_id
AND r.name = 'db_owner'
ON u.principal_id = rm.member_principal_id
WHERE u.name LIKE '%@web.onmicrosoft.com'
AND u.type_desc = 'EXTERNAL_USER';
PRINT @PrintStatement;
END;
2