当前位置: 动力学知识库 > 问答 > 编程问答 >

stored procedures - Value Comparison with a multivalued column in SQL Database Table

问题描述:

Suppose there is a table A which has a column AccessRights which is multivalued( Eg of values in it in this format STOLI,HELP,BRANCH(comma separated string)

Now a stored procedure is written against this table to fetch records based on a AccessRight parameter sent to the SP.

Let that parameter be @AccessRights, this is also a comma separated string which may have a value like STOLI,BRANCH,HELPLINE etc

Now I want to compare individual values from the parameter @AccessRights with the column AccessRights.

Current Approach is I split the Comma Separated string(@AccessRights) using a User Defined Function Split.

And I get Individual values in a Table variable(Contains only one column "accessGroup"), the individual values are in a Table variable under the column name accessGroup and I use following code in the SP for comparison

Where AccessRights like '%'+accessGroup+'%'

Now if the user passes the parameter (HELP, OLI) instead of( HELP,STOLI) the SP will give the output.

What should be done for comparison so that that subststring OLI does not give the output for STOLI

网友答案:

Following trick should do it (self-documented):

WHERE (',' + AccessRights + ',') LIKE ('%,' + accessGroup  + ',%')

But ideally split both sides into tables tables and compare values.

网友答案:

Split both collections, store them on temporary #tables and join them.

分享给朋友:
您可能感兴趣的文章:
随机阅读: