Tetteh Tetteh - 10 days ago 5
SQL Question

if statement in transact sql

I have a stored procedure that I need to enhance. This is what I have now.

ALTER PROCEDURE [rw].[sp_EFT_NoticeXLS2]
(@Scope varchar(50)
, @AcctPeriod char(6)
, @CompanyID nchar(10))
AS ......


What I need to do is to pass a variable @GLsubacct to it only if @CompanyID is 0000.

I tried.....

ALTER PROCEDURE [rw].[sp_EFT_NoticeXLS2]
(@Scope varchar(50)
, @AcctPeriod char(6)
, @CompanyID nchar(10)
IF (@CompanyID IS 0000)
@GLsubacct varchar(1000))
AS ....


but I am getting an error. I am not sure how to write the if statement. I need all the help I can get, thanks!

Answer

You can't do that.

You can setup the second variable to be "nullable"...making it "optional".

Then you can raise an exception if the rules don't follow.

Generic example:

Use Northwind
GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[uspDoSomething]') AND type in (N'P', N'PC'))
    DROP PROCEDURE [dbo].[uspDoSomething]
GO

CREATE PROCEDURE [dbo].[uspDoSomething]
(
    @CustomerId varchar(12)
    , @PostalCode varchar(12) = NULL
)
AS

SET NOCOUNT ON;

if @CustomerId = 'ALFKI' AND @PostalCode IS NULL
BEGIN
    THROW 51000, 'You must supply a Postal Code with this @CustomerId.', 1;  
END


select * from dbo.Customers c 
where 
c.CustomerID = @CustomerId 
AND 
( @PostalCode IS NULL OR c.PostalCode = @PostalCode )


GO
/* examples */

exec [dbo].[uspDoSomething] 'ALFKI' , '12209'

exec [dbo].[uspDoSomething] 'ALFKI'

exec [dbo].[uspDoSomething] 'ANATR' , '05021'
Comments