Professional Web Applications Themes

String Concatenation in Stored Procedures - Microsoft SQL / MS SQL Server

Hi, I've been trying to use a declared variable that has a string concatenation in my Select SQL statement but I've not been able to get the Select Statement to work. Below is my stored procedure: CREATE PROCEDURE getAccessLevel CompanyId as int, EmpRole as varchar(3) AS Declare Role as varchar(7) Set Role = 'AL.' + EmpRole Select Menu_Id from Access_Level AL where Role<>'N' AND AL.CompanyId = CompanyId Print Role I've simplified the Select statement - there is another table that I also need to query from. The results that I get seems to ignore the Role <>'N' part and I ...

  1. #1

    Default String Concatenation in Stored Procedures

    Hi,

    I've been trying to use a declared variable that has a
    string concatenation in my Select SQL statement but I've
    not been able to get the Select Statement to work. Below
    is my stored procedure:

    CREATE PROCEDURE getAccessLevel
    CompanyId as int, EmpRole as varchar(3)
    AS
    Declare Role as varchar(7)
    Set Role = 'AL.' + EmpRole
    Select Menu_Id from Access_Level AL
    where Role<>'N' AND AL.CompanyId = CompanyId
    Print Role

    I've simplified the Select statement - there is another
    table that I also need to query from. The results that I
    get seems to ignore the Role <>'N' part and I get all
    results returned. The Print statement returns the correct
    Role data after the Set statement but I have problems
    using it in the Select statement. I was wondering if I
    have used Role correctly in the Select statement?? Do I
    need to qualify it in a different way since it is a
    declared variable? How should I write the Select
    statement correctly so that Role can be used?

    Any help on this would be great.

    Thanks.

    Peter
    Peter Guest

  2. #2

    Default Re: String Concatenation in Stored Procedures

    Hi Peter,

    The boolean expression role<>'N' will always return TRUE. SQL server will
    compare the string values of role and 'N'.
    If I understand correctly, you are trying to pass the column name as a
    parameter to the SP.
    If there is a fixed number of EmpRoles, use the CASE clause:

    .....WHERE 'N' <>
    CASE EmpRole
    WHEN 'ABC' THEN AL.ABC
    WHEN 'DEF' THEN AL.DEF
    etc..
    END.......

    But that means you will need to refresh your SP code if you add a new
    EmpRole.
    You could also handle this using dynamic SQL (not recommended...) see
    http://www.algonet.se/~sommar/dynamic_sql.html

    HTH

    "Peter Bley" <com> wrote in message
    news:06c101c35e6f$62f0b9b0$gbl... 


    Amy Guest

  3. #3

    Default Re: String Concatenation in Stored Procedures

    Peter,

    First of all, inside your procedure, Role is a string
    beginning with 'AL.' , and that always be different from 'N'.
    Role is not a reference to a table column.

    What's going on here is that you are storing information in
    the names of columns of the Access_Level table, which is
    not a good thing. Now you need to retrieve data based
    on that information, and you're in the messy position of
    needing to specify a column name with a parameter.

    The separation between data and metadata (metadata includes
    column names) is not a good one to ignore.

    So instead of

    create table Access_Level (
    CompanyID int primary key,
    REC char, -- indicates receptionists' access level
    MGR char, -- managers' access level
    EXC char, -- executives' access level
    CEO char -- ceo's access level
    )

    you would have

    create table Access_Level (
    Company ID int, -- company
    EmpRole varchar(3), -- role
    hasAccess char, -- access level for that role in that company
    primary key (CompanyID, EmpRole)
    )

    Then your procedure is much simpler:

    select Menu_Id from Access_Level
    where EmpRole = EmpRole
    and CompanyID = CompanyID
    and hasAccess <> 'N'

    -- Steve Kass
    -- Drew University
    -- Ref: B910B053-1DCE-4C08-A74E-43DFBF2BDD12

    Peter Bley wrote:
     

    Steve Guest

  4. #4

    Default Re: String Concatenation in Stored Procedures

    Thanks Amy and Steve. The CASE statement, as Amy pointed
    will work well. Currently I've resorted to using IF
    statements. The separation of Data and Metadata will be a
    little bit more difficult at this point since other
    programs use the table as well. However, could you
    explain a little bit more on why it would be a good idea
    to separate out data and metadata? I am not good at
    designing the tables so it'd be great if you could explain
    it. The way I see it you will either have more rows or
    columns depending on how you structure the table. In this
    particular case the Roles are pretty set - there are only
    3 roles. But it'd be great if you could explain to me the
    differences.

    Thanks,
    Peter
     
    from 'N'. 
    in 
    includes 
    company [/ref]
    Below [/ref]
    I [/ref]
    correct 
    >
    >.
    >[/ref]
    Peter Guest

  5. #5

    Default Re: String Concatenation in Stored Procedures

    Even with only three fixed roles, roles are entities, I'd think,
    and deserve to be values within a table. By storing them
    as column names, you end up with three columns each storing
    identical kinds of information: a value in any of the three
    represents the fact that a particular role in a particular company
    has a certain level of access.

    Rows and columns server very different purposes in a
    table. Presumably what you need to know about a person
    in order to find out their access level is what company they
    are with and what role they have.

    I'm not sure what else to say. Maybe if you search Google Groups
    for kass+sqlserver+metadata you'll find I did a better job explaining
    it on another occasion...

    SK


    Peter Bley wrote:
     
    >from 'N'.
    >

    >in
    >

    >includes
    >

    >company
    >
    > [/ref]
    >Below
    >
    > [/ref]
    >I
    >
    > [/ref]
    >correct
    >

    >>.
    >>
    >>
    >>[/ref][/ref]

    Steve Guest

Similar Threads

  1. string concatenation
    By Rinoa in forum Macromedia ColdFusion
    Replies: 1
    Last Post: April 22nd, 01:05 PM
  2. String concatenation qn
    By Wolf Blaum in forum PERL Beginners
    Replies: 4
    Last Post: January 26th, 12:21 PM
  3. String Concatenation Bug
    By ecow in forum PHP Development
    Replies: 1
    Last Post: August 18th, 02:14 AM
  4. Maximum Length of String For Dynamic Stored Procedures
    By Lawrence in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: July 31st, 05:46 AM
  5. String concatenation with .= <FH>
    By Jay Tilton in forum PERL Miscellaneous
    Replies: 3
    Last Post: July 13th, 01:53 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139