34,593
社区成员
发帖
与我相关
我的任务
分享
USE tempdb
GO
IF OBJECT_ID('test') IS NOT NULL DROP TABLE test
GO
create table test
(
codeA nvarchar(20),
codeB nvarchar(20)
)
GO
SET NOCOUNT ON
insert into test (codeA,codeB) values ('A1','1')
insert into test (codeA,codeB) values ('A2','2')
insert into test (codeA,codeB) values ('B3','3')
insert into test (codeA,codeB) values ('A4','4')
insert into test (codeA,codeB) values ('B5','5')
insert into test (codeA,codeB) values ('B6','6')
insert into test (codeA,codeB) values ('A8','10')
GO
IF OBJECT_ID('test111') IS NOT NULL DROP PROC test111
GO
create PROCEDURE [dbo].[test111]
@cDefineA_in nvarchar(255)='',
@cDefineB_in nvarchar(255)=''
as
begin
declare @sql nvarchar(max)
declare @CTBB nvarchar(255)
declare @ParamString NVARCHAR(255)
set @CTBB='%'+@cDefineA_in+'%' --这里多加了 '', 现在这样就可以了
set @ParamString =N'@cDefineA nvarchar(255),@cDefineB nvarchar(255)'
--定义动态语句的输入参数
set @sql=N'
SELECT * from test
where
((@cDefineB = '''') or (CodeA = @cDefineB)) '
IF @cDefineA_in > '' --相当于 isnull(@c,'')!=''
BEGIN
PRINT 1
SET @sql=@sql+' and ((@cDefineA = '''') or (CodeB like @cDefineA))'
END
PRINT @sql;
EXEC sp_executesql @sql,@ParamString,@cDefineA=@CTBB,@cDefineB=@cDefineB_in
end
GO
exec test111 '1',''
/*
codeA codeB
A1 1
A8 10
*/
USE tempdb
GO
IF OBJECT_ID('test') IS NOT NULL DROP TABLE test
GO
create table test
(
codeA nvarchar(20),
codeB nvarchar(20)
)
insert into test (codeA,codeB) values ('A1','1')
insert into test (codeA,codeB) values ('A2','2')
insert into test (codeA,codeB) values ('B3','3')
insert into test (codeA,codeB) values ('A4','4')
insert into test (codeA,codeB) values ('B5','5')
insert into test (codeA,codeB) values ('B6','6')
insert into test (codeA,codeB) values ('A8','10')
GO
IF OBJECT_ID('test111') IS NOT NULL DROP PROC test111
GO
create PROCEDURE [dbo].[test111]
@cDefineA_in nvarchar(255)='',
@cDefineB_in nvarchar(255)=''
as
begin
declare @sql nvarchar(max)
declare @CTBB nvarchar(255)
declare @ParamString NVARCHAR(255)
set @CTBB='''%'+@cDefineA_in+'%'''
set @ParamString =N'@cDefineA nvarchar(255),@cDefineB nvarchar(255)'
--定义动态语句的输入参数
set @sql=N'
SELECT * from test
where
((@cDefineB = '''') or (CodeA = @cDefineB)) '
IF @cDefineA_in > '' --相当于 isnull(@c,'')!=''
BEGIN
SET @sql=@sql+' and ((@cDefineA = '''') or (CodeB like @cDefineA))'
END
EXEC sp_executesql @sql,@ParamString,@cDefineA=@CTBB,@cDefineB=@cDefineB_in
end