Post reply

How to execute sp in select case statement

  • March 20, 2014 at 3:17 am

    #284760

    Hi All,

    I am a junior dba not a developer. So I'm just trying to get use to write code in T-SQL.

    Anyways, I have a table which is dba.dbhakyedek.

    Columns are

    dbname, username, class_desc, object_name, permission_name, state_desc

    I have statement

    select case

    when class_desc='OBJECT_OR_COLUMN' then 'GRANT '+permission_name+' ON '+'['+left(object_name,3)+'].'+'['+substring(object_name,5,len(object_name))+ '] TO '+username

    WHEN class_desc='DATABASE_ROLE' THEN EXEC sp_addrolemember N'object_name', N'MC'

    end

    from dba.dbhakyedek

    where username='MC'

    This statement was running successfully until exec sp_addrolemember thing. I just learned that i can't call a sp in select case but i couldnt figure out how to do it. Any suggestions

    Thanks in advance

  • March 20, 2014 at 3:19 am

    #1698600

    A proc can't be called as part of a select. What are you trying to do here?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • March 20, 2014 at 3:24 am

    #1698603

    What I am trying to do is;

    If my class_desc returns OBJECT_OR_COLUMN, I want to get the grant statement which works. However if class_desc returns DATABASE_ROLE then i want to execute sp_addrolemember. It's like

    EXEC sp_addrolemember N'object_name(which comes from dba.dbhakyedek)', N'MC'

  • March 20, 2014 at 6:59 am

    #1698664

    Use dynamic SQL to set a character string with the CASE

  • March 20, 2014 at 7:20 am

    #1698675

    If you change that to either get the GRANT or get the EXEC, then it's easy. Something like this (didn't test, so quotes may be off)

    select case

    when class_desc='OBJECT_OR_COLUMN' then 'GRANT '+permission_name+' ON '+'['+left(object_name,3)+'].'+'['+substring(object_name,5,len(object_name))+ '] TO '+username

    WHEN class_desc='DATABASE_ROLE' THEN 'EXEC sp_addrolemember N''' + object_name + ''', N''MC'''

    end

    from dba.dbhakyedek

    where username='MC'

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • March 20, 2014 at 7:24 am

    #1698677

    I want to execute it, I dont want to get the statement itself. Thanks for your response

  • March 20, 2014 at 7:44 am

    #1698688

    You can't execute a procedure as part of a select.

    You're getting the GRANT commands listed out, why do you want one set of commands printed (I assume for later execution) and the other executed immediately?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply