can anyone help me with construction of an IF in a stored procedure in sql server.
任何人都可以帮我在sql server中的存储过程中构建IF。
Basically I have a simple stored procedure but I now need to pass in a new input parameter which depending if it is true I pass the value D and if its false I pass the value A. But the change is in the middle of a subquery.. let me explain... here is the stored procedure. basically if I send in True for ReturnOldStatus I execute the subquery ItemStatus='D' and if it is false then i pass in ItemStatus='A'
基本上我有一个简单的存储过程,但我现在需要传入一个新的输入参数,这取决于它是否为真我传递值D,如果它是假,我传递值A.但是更改是在子查询的中间。让我解释一下......这是存储过程。基本上如果我为ReturnOldStatus发送True,我执行子查询ItemStatus ='D',如果它是false,那么我传入ItemStatus ='A'
CREATE PROCEDURE [dbo].[MyTempStoredProc]
(
@IdOffice Int,
@ReturnOldStatus bit
)
AS
BEGIN
SET NOCOUNT ON;
SELECT * FROM Offices
WHERE
IdOffice = @IdOffice
AND (V.OffType NOT IN (
SELECT * FROM MiscOff
WHERE
ItemStatus= 'D') // This needs to be ItemStatus ='A' if FALSE is passed in on the input param
Any ideas??
Thanks
3 个解决方案
#1
I would solve it like this:
我会像这样解决它:
declare @itemStatus varchar(1);
if (@inputParam = 'FALSE')
begin
set @itemStatus = 'A'
end
else
set @itemStatus = 'D'
SELECT * FROM Offices
WHERE
IdOffice = @IdOffice
AND (V.OffType NOT IN (
SELECT * FROM MiscOff
WHERE ItemStatus= @itemStatus)
)
T-Sql is not my native language, so there may be errors in there...
T-Sql不是我的母语,因此可能存在错误......
#2
Just use the T-SQL if statement:
只需使用T-SQL if语句:
IF @ReturnOldStatus = 0
BEGIN
--Some statements
END
ELSE
BEGIN
--Some other statements
END
#3
I think this will suffice for your problem. If not, look into the DECLARE/SET statements in TSQL.
我认为这足以解决您的问题。如果没有,请查看TSQL中的DECLARE / SET语句。
CREATE PROCEDURE [dbo].[MyTempStoredProc]
(@IdOffice Int,
@ReturnOldStatus bit )
AS BEGIN
SET NOCOUNT ON;
SELECT * FROM Offices
WHERE IdOffice = @IdOffice
AND (V.OffType NOT IN (SELECT * FROM MiscOff
WHERE (ItemStatus= 'D' AND @ReturnOldStatus = 1)
OR
(ItemStatus= 'A' AND @ReturnOldStatus = 0)
)
#1
I would solve it like this:
我会像这样解决它:
declare @itemStatus varchar(1);
if (@inputParam = 'FALSE')
begin
set @itemStatus = 'A'
end
else
set @itemStatus = 'D'
SELECT * FROM Offices
WHERE
IdOffice = @IdOffice
AND (V.OffType NOT IN (
SELECT * FROM MiscOff
WHERE ItemStatus= @itemStatus)
)
T-Sql is not my native language, so there may be errors in there...
T-Sql不是我的母语,因此可能存在错误......
#2
Just use the T-SQL if statement:
只需使用T-SQL if语句:
IF @ReturnOldStatus = 0
BEGIN
--Some statements
END
ELSE
BEGIN
--Some other statements
END
#3
I think this will suffice for your problem. If not, look into the DECLARE/SET statements in TSQL.
我认为这足以解决您的问题。如果没有,请查看TSQL中的DECLARE / SET语句。
CREATE PROCEDURE [dbo].[MyTempStoredProc]
(@IdOffice Int,
@ReturnOldStatus bit )
AS BEGIN
SET NOCOUNT ON;
SELECT * FROM Offices
WHERE IdOffice = @IdOffice
AND (V.OffType NOT IN (SELECT * FROM MiscOff
WHERE (ItemStatus= 'D' AND @ReturnOldStatus = 1)
OR
(ItemStatus= 'A' AND @ReturnOldStatus = 0)
)