0

I got A cursor with some ifs like this one:

 IF @ROLE='RW' 
            BEGIN
                SET @COMMAND ='
                USE '+QUOTENAME(@DATABASE)+'
                CREATE USER '+QUOTENAME(@LOGIN_ACESSO)+' FOR LOGIN '+QUOTENAME(@LOGIN_ACESSO)+' WITH DEFAULT_SCHEMA=[DBO]'+CHAR(13)+CHAR(10)+'
                USE '+QUOTENAME(@DATABASE)+'
                ALTER ROLE [DB_DATAREADER] ADD MEMBER '+QUOTENAME(@LOGIN_ACESSO)+'
                ALTER ROLE [DB_DATAWRITER] ADD MEMBER '+QUOTENAME(@LOGIN_ACESSO)
            END

PRINT @COMMAND
EXECUTE (@COMMAND)

UPDATE TBL_LOGINPERMISSAO SET CRIADO =1 WHERE [ID]=@ID

But obviously, even if the user was not created, I update TBL_LOGINPERMISSAO. How can I update only if the user was created? even copying this update on all ifs, it does not work, because even not creating the user will reach the UPDATE.

is this a case for try/catch?

1

You can perform the UPDATE only if the user EXISTS like this:

SET @COMMAND = '
    IF EXISTS (
      SELECT 1 FROM ' + QUOTENAME(@DATABASE) + '.sys.sysusers
      WHERE name = ''' + @LOGIN_ACESSO + '''
    )
    BEGIN
      UPDATE TBL_LOGINPERMISSAO SET CRIADO = 1 WHERE [ID] = ' + CAST(@ID AS nvarchar(max)) + '
    END'
EXEC (@COMMAND)

You'll need to add quotes around the @ID part if it not a numeric value, e.g. (n)varchar or guid.

1

Taking into account that you need to check the user for a specific database, you could do something like this?

SET @COMMAND =
'USE '+@DATABASE+' 
 SELECT name FROM sys.sysusers WHERE name = '''+@LOGIN_ACESSO+''''
DECLARE @t TABLE (LOGIN_ACESSO NVARCHAR(255))
INSERT INTO @t 
EXECUTE  (@COMMAND)
IF exists (select LOGIN_ACESSO from @t)
BEGIN
    UPDATE TBL_LOGINPERMISSAO SET CRIADO =1 WHERE [ID]=@ID
END

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.