未加星标

Sql Server: Using CASE Statement inside IN Clause (alternative)

字体大小 | |
[数据库(mssql) 所属分类 数据库(mssql) | 发布者 店小二04 | 时间 2017 | 作者 红领巾 ] 0人收藏点击收藏

Introduction : In this article I am going to share best alternative of using CASE statement inside IN operator in sql server with suitable examples.

In previous articles i explained the articles of Using case expression in select statement in sql server and Try catch to handle exception and commit/rollback transaction and Remove duplicate records/data from sql server database table and Create database script and create database from that script and Drop or truncate parent table by dropping all foreign key constraints


Sql Server: Using CASE Statement inside IN Clause (alternative)
Description : While working with sql server database I got the following requirement.

I was to get the permissions according to user type. i.e.

If UserType=1 then permission will be Read & Write, Update and Delete. If UserType=2 then permission will be Read & Write, Update. If UserType=3 then permission will be Read Only. Here UserType=1:Admin, 2: User, 3: Guest User Implementation : Let’s create PermissionType table and insert data in it using following script. CREATE TABLE tbPermissionType ( PermissionId INT IDENTITY ( 1 , 1 ) PRIMARY KEY , PermissonType VARCHAR ( 100 ) ) INSERT INTO tbPermissionType VALUES ( 'Read Only' ) INSERT INTO tbPermissionType VALUES ( 'Read & Write' ) INSERT INTO tbPermissionType VALUES ( 'Update' ) INSERT INTO tbPermissionType VALUES ( 'Delete' ) Check table data SELECT * FROM tbPermissionType PermissionId PermissionType 1 Read Only 2 Read & Write 3 Update 4 Delete To get what I need I first I tried using CASE inside IN Clause that first came up in my mind. DECLARE @UserType TINYINT = 1 ; SELECT PermissonType FROM tbPermissionType WHERE PermissionId IN ( CASE WHEN @UserType = 1 THEN ( 2 , 3 , 4 ) WHEN @UserType = 2 THEN ( 2 , 3 ) WHEN @UserType = 3 THEN ( 1 ) END ) But I got the following error: Incorrect syntax near ','. Then I searched on google and got many ways to get this done. I tried many ways and found the result I need. But then I tried much efficient and clear way without using CASE statement that I am going to share. Let’s try by passing 1, 2, 3 value to the UserType variable and see whether we get the desired result or not. Execute the following by passing UserType=1: DECLARE @UserType TINYINT = 1 ; SELECT PermissonType FROM tbPermissionType WHERE ( @UserType = 1 AND PermissionId IN ( 2 , 3 , 4 )) OR ( @UserType = 2 AND PermissionId IN ( 2 , 3 )) OR ( @UserType = 3 AND PermissionId = 1 ) Result will be as expected. PermissionType Read & Write Update Delete Now execute the following by passing UserType=2 DECLARE @UserType TINYINT = 2 ; SELECT PermissonType FROM tbPermissionType WHERE ( @UserType = 1 AND PermissionId IN ( 2 , 3 , 4 )) OR (

本文数据库(mssql)相关术语:熊片数据库 mssql数据库 oracle数据库 pubmed数据库 access数据库 万方数据库

主题: TIRIMRY
分页:12
转载请注明
本文标题:Sql Server: Using CASE Statement inside IN Clause (alternative)
本站链接:http://www.codesec.net/view/522310.html
分享请点击:


1.凡CodeSecTeam转载的文章,均出自其它媒体或其他官网介绍,目的在于传递更多的信息,并不代表本站赞同其观点和其真实性负责;
2.转载的文章仅代表原创作者观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,本站对该文以及其中全部或者部分内容、文字的真实性、完整性、及时性,不作出任何保证或承若;
3.如本站转载稿涉及版权等问题,请作者及时联系本站,我们会及时处理。
登录后可拥有收藏文章、关注作者等权限...
技术大类 技术大类 | 数据库(mssql) | 评论(0) | 阅读(80)