【【【存储过程能加判断控制条件在where吗】】】】】

[yyyjff]

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
IF (@strUserName != '' ) --如果if和end不加是可以的
and strUserName like '% @strUserName %'
end
IF @@ERROR!=0
BEGIN
RAISERROR 20000 'SP_CustUserListSearchItem: Cannot load data on tbCustUserInfo'
RETURN(1)
END
END
GO

[yyyjff]

关键字end附近有语法错误

[超级大笨狼]

IF len(@strUserName )>0
begin
select ....
end
else
begin
select ....
end

[-狙击手-]

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

[zlp321002]

--可以用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,然后拼接条件！

[Andy__Huang]

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

[yyyjff]

新人类 如果我好几个条件怎么办？？

潇潇的好像还不可以

仙林的不行

[yyyjff]

潇潇 的 应该可以了
谢谢哈

[lzhs]

SELECT numUserId
FROM tbCustUserInfo
Where 1=1
and strUserName like case when @strUserName !='' then '% @strUserName %' else '%' END

在潇潇的方法中再加上 -----else '%' -----

或者这样,用动态SQL

Declare @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

[bugchen888]

尽量不要使用动态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

[yyyjff]

谢谢各位 最后用动态SQl做的

[ppzhlmt]

IF len(@strUserName )>0
begin
select ....
end
else
begin
select ....
end