Can anyone explain this code 1

[simian101]

I was reviewing someone else's code and came across the following code...

It is a stored procedure that alters itself? But I don't see that it alters anything.

It also has 2 nested begin and end statement but they seem to be redundent?

Thanks

Simi



ALTER PROCEDURE [dbo].[sp_Account_Identification_Question_Select_Param]
@Account_Identification_Question_ID int=null

AS

IF (@Account_Identification_Question_ID is not null )
BEGIN
BEGIN
select * from Account_Identification_Question
where Account_Identification_Question_ID = @Account_Identification_Question_ID
END
END
ELSE
BEGIN
select * from Account_Identification_Question
END

 

[RiverGuy]

The stored procedure itself doesn't actually alter anything. When you create a new stored procedure, you need to tell SQL Server to create a new stored procedure. You use the CREATE PROCEDURE command for that. When you are making modifications to a stored procedure, you need to tell SQL Server that you are modifying a pre-existing stored procedure. You use the command ALTER PROCEDURE for that.

 

[simian101]

Yes, but in this example what is being altered if it altering itself? (Assuming there is not anouther SP somewhere that also alters it.)

Simi

 

[simian101]

Nevermind... I think I got it.

Thanks

simi

 

[RiverGuy]

Read it like this:

ALTER PROCEDURE [dbo].[sp_Account_Identification_Question_Select_Param]

Translation: I wish to modify the stored procedure called sp_Account_Identification_Question_Select_Param and here is the definition to use for that stored procedure

@Account_Identification_Question_ID int=null

AS

IF (@Account_Identification_Question_ID is not null )
    BEGIN
        BEGIN
        select * from Account_Identification_Question
            where Account_Identification_Question_ID = @Account_Identification_Question_ID  
        END
    END
ELSE
    BEGIN
        select * from  Account_Identification_Question  
    END