sql where 拼接

最新推荐文章于 2024-09-19 14:20:30 发布

这个是谁呀

最新推荐文章于 2024-09-19 14:20:30 发布

阅读量706

点赞数

分类专栏： SQL Server

本文链接：https://blog.csdn.net/qq_36611278/article/details/88863483

版权

SQL Server 专栏收录该内容

23 篇文章 1 订阅

订阅专栏

sql where 拼接

ALTER procedure  [dbo].[procUser_Role_AllList]
  @session varchar(30),
  @ip varchar(30),
  @u_id int=0,
  @login_code varchar(30),
  @rescode int  output,
  @resdesc varchar(200) output
as
  begin try
	declare @operate_userid bigint=0
	exec spCheck @session,@ip,'p_user_role_alllist',@rescode output,@resdesc output,@operate_userid output
	if @rescode <> 0 raiserror ('验证失败，原因：%s',11,1,@resdesc)

	declare @Sql nvarchar(max)
	declare @SqlWhere nvarchar(max)
	set @SqlWhere=''
	if @u_id <> 0 
	begin
		declare @db_count int
		select @db_count=count(*) from t_user where deleted = 0 and u_id = @u_id 
		if @db_count<=0 raiserror ('该用户不存在或已被删除!u_id=%d',11,3,@u_id);
		set @SqlWhere =' u_id ='+str(@u_id) 
	end
	if @login_code<>''
	begin
		if (@SqlWhere <> '') set @SqlWhere=@SqlWhere+' and '
		set @SqlWhere =' login_code like ''%'+ @login_code +'%'''
	end 
	set @sql = ' select u_id,login_code,r_id,name from v_user_role  where'+@SqlWhere+'' 
	exec sp_executesql @sql

	set @rescode = 0;
	set @resdesc = 'success';
end try	
begin catch
	set @rescode = ERROR_NUMBER();
	if @rescode=50000
		set @rescode=error_state()
	set @resdesc ='获取用户角色列表失败,'+ERROR_MESSAGE();
	select @rescode as rescode,@resdesc as resdesc
end catch