当@opType=1、2、3是,where分别用不同的条件,如何实现,以下代码报错CREATE PROCEDURE sp_ClientInfo_GetModel
@clientID int,
@userName nvarchar(50),
@userPwd nvarchar(50),
@opType int
ASSELECT
clientID,
userName,
userPwd
FROM
DX_ClientInfo
WHERE
case @opType
when 1 then [clientID] = @clientID
when 2 then [userName] = @userName
when 3 then [userName] = @userName and [userPwd] = @userPwd
end
GO
@clientID int,
@userName nvarchar(50),
@userPwd nvarchar(50),
@opType int
ASSELECT
clientID,
userName,
userPwd
FROM
DX_ClientInfo
WHERE
case @opType
when 1 then [clientID] = @clientID
when 2 then [userName] = @userName
when 3 then [userName] = @userName and [userPwd] = @userPwd
end
GO
CREATE PROCEDURE sp_ClientInfo_GetModel
@clientID int,
@userName nvarchar(50),
@userPwd nvarchar(50),
@opType int
ASDECLARE @S varchar(1000)SET @S = CASE @opType
WHEN 1 THEN '[clientID] = @clientID'
WHEN 2 THEN '[userName] = @userName'
WHEN 3 THEN '[userName] = @userName and [userPwd] = @userPwd'
ENDEXEC('
SELECT
clientID,
userName,
userPwd
FROM
DX_ClientInfo
WHERE ' + @S)
GO
@clientID int,
@userName nvarchar(50),
@userPwd nvarchar(50),
@opType int
AS
if @opType=1
begin
SELECT clientID,userName,userPwd FROM DX_ClientInfo where clientID=@clientID
end
ELSE
IF @opType=2
BEGIN
SELECT clientID,userName,userPwd FROM DX_ClientInfo where userName = @userName
END
ELSE
IF @opType=3
BEGIN
SELECT clientID,userName,userPwd FROM DX_ClientInfo where userName = @userName and userPwd = @userPwd
END
CREATE PROCEDURE sp_ClientInfo_GetModel
@clientID int,
@userName nvarchar(50),
@userPwd nvarchar(50),
@opType int
ASSELECT
clientID,
userName,
userPwd
FROM
DX_ClientInfo
WHERE clientID=CASE @opType WHEN 1 THEN @clientID ELSE clientID END
AND userName=CASE @opType WHEN 2 THEN @userName ELSE userName END
AND userName=CASE @opType WHEN 3 THEN @userName ELSE userName END
AND userPwd=CASE @opType WHEN 3 THEN @userPwd ELSE userPwd END
GO
CREATE PROCEDURE sp_ClientInfo_GetModel
@clientID int,
@userName nvarchar(50),
@userPwd nvarchar(50),
@opType int
ASDECLARE @S varchar(1000)SET @S = CASE @opType
WHEN 1 THEN '[clientID] = ' + convert(varchar(10),@clientID)
WHEN 2 THEN '[userName] = '+ @userName
WHEN 3 THEN '[userName] = ' + @userName + ' and [userPwd] = ' + @userPwd
ENDEXEC('
SELECT
clientID,
userName,
userPwd
FROM
DX_ClientInfo
WHERE ' + @S)
GO
@clientID int,
@userName nvarchar(50),
@userPwd nvarchar(50),
@opType int
AS
--用一句实现
SELECT
clientID,
userName,
userPwd
FROM
DX_ClientInfo
WHERE
case @opType when 1 then [clientID] else [userName] end
=case @opType when 1 then @clientID else @userName end
and
case @opType when 3 then [userPwd] end
=case @opType when 3 then @userPwd end
GO