Stored Procedure if-else problem

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Stored Procedure if-else problem

Author

Topic

MFors
Starting Member

2 Posts

Posted - 2005-11-01 : 14:59:12

I'm trying to create a stored procedure that takes actions based on the value of the parameters passed to it.
For example if I pass 1 into @action and 1 into @mode I want to do an update on the Account StatusTable. If I pass 1 and 2 to the same parameters I'd like to add a new field to the AccountStatus table.

When I used the code below and try to add a new entry into the table, the entry is added into both tables. I'm new to TSQL and believe that my if-else syntax is logically incorrect.

Could Someone please point me in the right direction? Thanks.

Mark F.

CREATE PROCEDURE dbo.AddUpStatus

@action int
,@mode int
,@key int
,@name varchar(100)

As

if @action = 1

if @mode = 1 --Handle Account Status
Begin
Update AccountStatus
Set Name = @name
Where accountStatusKey = @key
End

if @mode = 2
Begin
Insert Into AccountStatus(Name)
Values(@name)
End

else

if @action = 2 --Handle AccountSubstatus

if @mode = 1
Begin
Update AccountSubstatus
Set Name = @name
Where AccountSubstatusKey = @key
End

if @mode = 2
Begin
Insert Into AccountSubstatus(Name)
Values(@name)
End

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-11-01 : 15:39:58

I think you just need to be more explicit with your BEGIN and END blocks. Here are some less ambigious techniques:


if @action = 1
begin
	if @mode = 1
	begin
		--Code for @action=1 and @mode=1
	end
	else
	begin
		--code for @action=1 and @mode <> 1
	end
end
else --@action <> 1
begin
	if @mode = 1
	begin
		--code for @action <> 1 and @mode = 1
	end
	else
	begin
		--code for @action <> 1 and @mode <> 1
	end
end

--OR

if @action = 1 and @mode = 1
begin

end

if @action = 1 and @mode = 2
begin

end

if @action = 2 and @mode = 1
begin

end

if @action = 2 and @mode = 2
begin

end

Another alternative is to create a different stored procedure for each objective. Each proc is very simple that way.

Be One with the Optimizer
TG

MFors
Starting Member

2 Posts

Posted - 2005-11-01 : 15:57:51

TG,

Thanks for the assist. I thought it was something along those lines, I just couldn't put my finger on it.
I thought about using separate procedures, but our shop likes to not load the database with objects.
Again, thanks.

Mark F.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources