CREATE PROCEDURE SP_CustUserListSearchItem @strUserName nvarchar(100), @strAccountId nvarchar(50), @strAccountName nvarchar(100), @strAccountType nvarchar(6), @numcustid numeric, @strContrStatus nvarchar(6) AS SELECT numUserId FROM tbCustUserInfo where 1=1 IF (@strUserName != '' ) --如果if和end不加是可以的 and strUserName like '% @strUserName %'IF @@ERROR!=0 BEGIN RAISERROR 20000 'SP_CustUserListSearchItem: Cannot load data on tbCustUserInfo' RETURN(1) END END GO
--可以用Case when CREATE PROCEDURE SP_CustUserListSearchItem @strUserName nvarchar(100), @strAccountId nvarchar(50), @strAccountName nvarchar(100), @strAccountType nvarchar(6), @numcustid numeric, @strContrStatus nvarchar(6) AS BEGIN SELECT numUserId FROM tbCustUserInfo where 1=1 and strUserName like case when @strUserName !='' then '% @strUserName %' END IF @@ERROR!=0 BEGIN RAISERROR 20000 'SP_CustUserListSearchItem: Cannot load data on tbCustUserInfo' RETURN(1) END END GO --也可以用动态SQL,然后拼接条件!
CREATE PROCEDURE SP_CustUserListSearchItem @strUserName nvarchar(100), @strAccountId nvarchar(50), @strAccountName nvarchar(100), @strAccountType nvarchar(6), @numcustid numeric(10), @strContrStatus nvarchar(6) AS BEGIN SELECT numUserId FROM tbCustUserInfo where 1=1 and (ISNULL(@strUserName,'') != '' ) and strUserName like '% @strUserName %'IF @@ERROR!=0 BEGIN RAISERROR 20000 'SP_CustUserListSearchItem: Cannot load data on tbCustUserInfo' RETURN(1) END END GO
SELECT numUserId FROM tbCustUserInfo Where 1=1 and strUserName like case when @strUserName !='' then '% @strUserName %' else '%' END在潇潇的方法中再加上 -----else '%' -----或者这样,用动态SQLDeclare @sql varchar(1000) --len随意 Set @sql = 'SELECT numUserId FROM tbCustUserInfo Where 1=1' If @strUserName != '' --如果还有其他字段,也可以仿造此方法 Begin Set @sql = @sql + ' and strUserName like '''+'%' +@strUserName+'%'+ ''' --这句话中的"'"可能会有少的情况,具体我也不太记得了,Query Analyzer又没开 :) 自己测试一下吧 End
尽量不要使用动态SQL。CREATE PROCEDURE SP_CustUserListSearchItem @strUserName NVARCHAR(100), @strAccountId NVARCHAR(50), @strAccountName NVARCHAR(100), @strAccountType NVARCHAR(6), @numcustid NUMERIC, @strContrStatus NVARCHAR(6) AS BEGIN SELECT numUserId FROM tbCustUserInfo WHERE (strUserName LIKE '% ' + @strUserName + ' %' OR @strUserName = '')
IF @@ERROR!=0 BEGIN RAISERROR 20000 'SP_CustUserListSearchItem: Cannot LOAD data ON tbCustUserInfo' RETURN(1) END END GO
谢谢各位 最后用动态SQl做的
IF len(@strUserName )>0 begin select .... end else begin select .... end
@strUserName nvarchar(100),
@strAccountId nvarchar(50),
@strAccountName nvarchar(100),
@strAccountType nvarchar(6),
@numcustid numeric,
@strContrStatus nvarchar(6)
AS SELECT
numUserId
FROM tbCustUserInfo
where 1=1 IF (@strUserName != '' ) --如果if和end不加是可以的
and strUserName like '% @strUserName %'IF @@ERROR!=0
BEGIN
RAISERROR 20000 'SP_CustUserListSearchItem: Cannot load data on tbCustUserInfo'
RETURN(1)
END
END
GO
CREATE PROCEDURE SP_CustUserListSearchItem
@strUserName nvarchar(100),
@strAccountId nvarchar(50),
@strAccountName nvarchar(100),
@strAccountType nvarchar(6),
@numcustid numeric,
@strContrStatus nvarchar(6)
AS
BEGIN
SELECT
numUserId
FROM tbCustUserInfo
where 1=1
and strUserName like case when @strUserName !='' then '% @strUserName %' END
IF @@ERROR!=0
BEGIN
RAISERROR 20000 'SP_CustUserListSearchItem: Cannot load data on tbCustUserInfo'
RETURN(1)
END
END
GO
--也可以用动态SQL,然后拼接条件!
@strUserName nvarchar(100),
@strAccountId nvarchar(50),
@strAccountName nvarchar(100),
@strAccountType nvarchar(6),
@numcustid numeric(10),
@strContrStatus nvarchar(6)
AS
BEGIN
SELECT
numUserId
FROM tbCustUserInfo
where 1=1 and (ISNULL(@strUserName,'') != '' )
and strUserName like '% @strUserName %'IF @@ERROR!=0
BEGIN
RAISERROR 20000 'SP_CustUserListSearchItem: Cannot load data on tbCustUserInfo'
RETURN(1)
END
END
GO
FROM tbCustUserInfo
Where 1=1
and strUserName like case when @strUserName !='' then '% @strUserName %' else '%' END在潇潇的方法中再加上 -----else '%' -----或者这样,用动态SQLDeclare @sql varchar(1000) --len随意
Set @sql = 'SELECT numUserId FROM tbCustUserInfo Where 1=1'
If @strUserName != '' --如果还有其他字段,也可以仿造此方法
Begin
Set @sql = @sql + ' and strUserName like '''+'%' +@strUserName+'%'+ ''' --这句话中的"'"可能会有少的情况,具体我也不太记得了,Query Analyzer又没开 :) 自己测试一下吧
End
@strUserName NVARCHAR(100),
@strAccountId NVARCHAR(50),
@strAccountName NVARCHAR(100),
@strAccountType NVARCHAR(6),
@numcustid NUMERIC,
@strContrStatus NVARCHAR(6)
AS
BEGIN
SELECT numUserId
FROM tbCustUserInfo
WHERE (strUserName LIKE '% ' + @strUserName + ' %' OR @strUserName = '')
IF @@ERROR!=0
BEGIN
RAISERROR 20000 'SP_CustUserListSearchItem: Cannot LOAD data ON tbCustUserInfo'
RETURN(1)
END
END
GO
begin
select ....
end
else
begin
select ....
end