sp_executesql动态拼接参数及参数值

原创已于 2023-08-21 17:21:57 修改 · 479 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#sql #sqlserver

于 2023-08-10 14:56:37 首次发布

sql 同时被 2 个专栏收录

3 篇文章

订阅专栏

sqlserver

3 篇文章

订阅专栏

文章介绍了如何创建一个名为pr_call_sql3的SQLServer存储过程，接受SQL语句和JSON格式参数，动态构建并执行SQL。涉及参数验证和错误处理。

废话不多说，直接上代码

create proc [dbo].[pr_call_sql3] @sql nvarchar(max),@jsonMap nvarchar(max)=null 
as
begin try
	if(isnull(@sql,'')='')
	begin
		select 0 as success,'传入的参数@sql为空' as msg, ERROR_MESSAGE() as sql_msg
		return
	end
	declare @参数值对照 TABLE ([field] [nvarchar](500),[value] [nvarchar](max),[type] int)
	BEGIN TRY
		 if(@jsonMap<>'')
		 begin
			set @jsonMap= REPLACE(@jsonMap,'''','''''')
			insert @参数值对照([field],[value],[type])  SELECT [key],[value],[type] FROM OPENJSON(@jsonMap)
			if exists(select 1 from  @参数值对照 where [field]='action' and [value]<>'login' and [value]<>'verify' and ((isnull([field],'')='loginUser' and isnull([value],'')='') or (isnull([field],'')='loginCompany' and isnull([value],'')='')))
			begin
				select 0 as success,'登录信息不完整' as msg, ERROR_MESSAGE() as sql_msg
				return
			end
		 end
	END TRY
	BEGIN CATCH
			select 0 as success,'传入的参数@jsonMap有误' as msg, ERROR_MESSAGE() as sql_msg
			return
	END CATCH
	set @sql= REPLACE(@sql,'''','''''')

	declare @Param nvarchar(max),@ParamValue nvarchar(max),@SetParamValue nvarchar(max)
	select @Param=stuff((select ',@'+[field]+(case when [type]=2 and  [value]=ROUND([value],0) then ' bigint' when [type]=3 then 'bit' else ' nvarchar(1000)' end)   from @参数值对照  for xml path('')),1,1,'')
	select @ParamValue=stuff((select ',@'+[field] from @参数值对照  for xml path('')),1,1,'')
	--select @SetParamValue=(select ' set @'+[field]+(case when [value] is null then '=null' else  '='''+[value]+'''' end) from @参数值对照  for xml path(''))
	select @SetParamValue=(select ' set @'+[field]+(case when [value] is null then '=null' else  (case when ([type]=2 and  [value]=ROUND([value],0)) or [type] =3 then '='+[value]+''  else  '='''+[value]+'''' end)     end) from @参数值对照  for xml path(''))
	declare @declSql nvarchar(max),@exeSql nvarchar(max)
	set @declSql='declare @sql nvarchar(max)'+ (case when isnull(@Param,'')<>'' then ','+@Param else ''end)
	set @exeSql=@SetParamValue + ' set @sql='''+@sql+'''  exec sp_executesql @sql'+(case when isnull(@Param,'')<>'' then ',N'''+@Param+''','+@ParamValue+'' else '' end)
	set @declSql=@declSql + @exeSql
	--select @declSql;
	exec(@declSql)
end try
begin catch 
	select 0 as success,'数据库操作失败,请联系管理员' as msg, ERROR_MESSAGE() as sql_msg
	return
end catch

存储过程参数说明

@sql：带参数的SQL语句

@jsonMap：参数键值对，格式为Json字符串

实例

查询：

EXEC [dbo].[pr_call_sql3] 
    @sql = N'select * from student'
  --@jsonMap = NULL

返回结果：

添加：

EXEC [dbo].[pr_call_sql3] 
		@sql = N'insert into student(name,age,birthday,weight)values(@name,@age,cast(@birthday as date),cast(@weight as float))',
		@jsonMap =  N'{"name":"tom3","age":15,"birthday":"2008-09-12","weight":60}'


EXEC [dbo].[pr_call_sql3] 
    @sql = N'select * from student'

查询结果：