存储过程解析字符串 在线的，解决后100分奉上

[pinhailinfeng]

参数@depatrment = '%信息安全科%'or all_path_name like'%易泰%'
在存储过程中如何转换为
select * from table where all_path_name like '%信息安全科%'or all_path_name like'%易泰%'

在线的，解决后100分奉上，谢谢。

[claro]

帮顶

[pinhailinfeng]

执行：

USE [cusomp]
GO

DECLARE	@return_value int

EXEC	@return_value = [dbo].[GET_DEPARTMENT_NOCOMPLETE_TASK_RankName]
		@ORG_ID = N'%信息安全科%'' or all_path_name like ''%易泰%'

SELECT	'Return Value' = @return_value

GO

存储过程用到部分

declare @depatrment varchar(40),@sql varchar(8000)
set @depatrment='all_path_name'
set @sql='select * from #monitorRecordInfo where '+@depatrment+''+@ORG_ID
print @sql
exec(@sql) 
GO

报错

消息 102，级别 15，状态 1，第 1 行
'%' 附近有语法错误。

(1 行受影响)
消息 102，级别 15，状态 1，第 1 行
'%信息安全科%' 附近有语法错误。
消息 105，级别 15，状态 1，第 1 行
字符串 '' 后的引号不完整。

[pinhailinfeng]

print @sql：

select * from #monitorRecordInfo where all_path_name%信息安全科%' or all_path_name like '%易泰%

[pinhailinfeng]

谢谢楼上各位解决啦这就给分

[pinhailinfeng]

分不够可以加，急等解决

[子陌红尘]

declare @depatrment varchar(40),@sql varchar(8000)
set @depatrment='all_path_name'
set @sql='select * from table where '+@depatrment+' like '''%信息安全科%'''or all_path_name like'''%易泰%''''
exec(@sql) 

[子陌红尘]

引号处理有点问题，修改一下：

declare @depatrment varchar(40),@sql varchar(8000)
set @depatrment='all_path_name'
set @sql='select * from table where '+@depatrment+' like ''%信息安全科%'' or all_path_name like''%易泰%'''
exec(@sql) 

[bennyyyyy]

动态拼字符

[新丁11111]

declare @depatrment varchar(500)
set @depatrment= '''%信息安全科%''or all_path_name like''%易泰%'''

declare @sqltext varchar(500)

set @sqltext='select * from table where all_path_name like '

set @sqltext=@sqltext+@depatrment

select @sqltext

[flairsky]

declare @depatrment varchar(max)
set @depatrment = '''%信息安全科%'''+'or all_path_name like'+'''%易泰%'''
declare @cmd varchar (max)
set @cmd = N'
select * from table where all_path_name '+ @depatrment

[新丁11111]

exec @sqltext

[dobear_0922]

create procedure P1 
	@depatrment nvarchar(1000)
as
begin
	declare @sql nvarchar(1000)
	set @sql= 'select * from table ' 
				+ isnull('where all_path_name like '+quotename(@depatrment ,''''),'')
	print @sql
	--exec(@sql)
end
go

exec P1 '''%信息安全科%'' or all_path_name like ''%易泰%'''
/*
select * from table where all_path_name like '''%信息安全科%'' or all_path_name like ''%易泰%'''
*/

drop procedure P1

[dobear_0922]

如果要执行的话：

create procedure P1 
    @depatrment nvarchar(1000)
as
begin
    declare @sql nvarchar(1000)
    set @sql= 'select * from table ' 
                + isnull('where all_path_name like '+quotename(@depatrment ,''''),'')
    --print @sql
    exec(@sql)
end
go