T-SQL 动态 sql

动态sql语句 节省了大量的sql语句

use master
go

if exists (select * from sys.databases where name='Test')
    drop database Test
go

create database Test
go

use Test
go

create table UserInfo 用户信息表
(
    UserID int primary key identity(1,1),
    LoginName varchar(50) not null,
    Password varchar(50) not null,
    UserRole int check(UserRole=1 or UserRole=2)
)

create table Leave 事务表
(
    LeaveID int primary key identity(1,1),
    Title varchar(100) not null,
    BeginDate datetime not null,
    EndDate datetime not null,
    LeaveReason varchar(200) not null,
    ApplyTime datetime default(getdate()) not null,
    ApproveState int default(1) check(ApproveState=1 or ApproveState=2),
    ApproveTime datetime null,
    ApproveResult varchar(10) check(ApproveResult='同意' or ApproveResult='不同意'),
    UserID int references UserInfo(UserID)
)
insert into UserInfo values('宋仲基',1,1)
insert into UserInfo values('宋慧乔',1,2)
insert into UserInfo values('李珍基',1,2)

insert into Leave values('我要回家','2018-12-27','2018-12-28','我要回家考驾照',GETDATE(),2,GETDATE(),'同意',3)
insert into Leave values('我要请三天假','2018-12-27','2018-12-30','我要陪小孩',GETDATE(),1,null,null,2)
insert into Leave values('我要回家','2018-12-27','2018-12-29','我要回家办身份证',GETDATE(),1,null,null,3)

动态sql语句

if exists (select * from sys.procedures where name='proc_sel')
    drop proc sel_all
go
create proc sel_all (@UserID int=0,@ApproveState int=0,@LoginName varchar(50)='')
as

    创建一个最大长度的字符串变量
    declare @sql varchar(max)=''

    给这个字符串赋初始值 调用时根据第一个参数 进行模糊查询
    set @sql='select LoginName,Title,BeginDate,EndDate,ApplyTime,ApproveState,ApproveTime,ApproveResult,LeaveID from
    Leave,UserInfo where Leave.UserID=UserInfo.UserID and LoginName like ''%'+@LoginName+'%'''

    判断 第二个参数 是否为默认值0 
    if(@UserID<>0)

    如果不是默认值 给@sql 加上后续的sql条件查询语句  and 前面记得要有空格 和@sql的默认语句组成一个新的sql语句 如果参数是字符 记得要用 ‘ 单引号进行转义
    set @sql+=' and UserInfo.UserID='+CONVERT(varchar,@UserID)

    判断第二个参数
    if(@ApproveState<>0)
    set @sql+=' and ApproveState='+CONVERT(varchar,@ApproveState)

    执行sql语句
    exec (@sql)
go

 

 

    调用 sql存储过程

    exec proc_sel

 

    查询的结果 和

select LoginName,Title,BeginDate,EndDate,ApplyTime,ApproveState,ApproveTime,ApproveResult,LeaveID from
    Leave,UserInfo where Leave.UserID=UserInfo.UserID and LoginName like '%%'

语句的结果一样

exec proc_sel 0,0,'李珍基' 

就是执行下面的sql语句

select LoginName,Title,BeginDate,EndDate,ApplyTime,ApproveState,ApproveTime,ApproveResult,LeaveID from
    Leave,UserInfo where Leave.UserID=UserInfo.UserID and LoginName like '%张三%'

exec proc_sel 3

 

select LoginName,Title,BeginDate,EndDate,ApplyTime,ApproveState,ApproveTime,ApproveResult,LeaveID from
    Leave,UserInfo where Leave.UserID=UserInfo.UserID and LoginName like '%%' and UserInfo.UserID=3