if/else in stored procedure - SQL Server Forums

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

SQL Server Development (2000)

if/else in stored procedure

Author

Topic

ebbmar
Starting Member

2 Posts

Posted - 2003-07-16 : 07:34:39

Hello all,

I've created a sp that gets its input from an asp.net page. Among the variables I pass along, one is "uploadfile" which can either be 0 or 1. If it's set to 1, it should insert all columns, but if it's 0 it should insert all but the binfile (image) column. In the sp I try to check if a 1 or 0 is passed along with a regular if/else, but if the variable contains a 0 (zero) it doesn't do anything. Neither the first or the second block is executed. But if it contains a 1, the first block is executed.
So my question is this: if a zero is passed in @uploadfile to the sp, why isn't the second block executed?
The code is as follows..


CREATE Procedure InsertDocument 
	@no int,
	@comment varchar( 50 ),
	@uploadfile bit,
	@filename varchar( 100 ),
	@binfile image,
	@contenttype varchar(150)

As
declare @created smalldatetime
SET @skapad = CONVERT(smalldatetime, GETDATE()) 

if (@uploadfile = 1)  
  begin
    Insert Files (no, comment, created, uploadfile, filename,  binfile, contenttype)
    Values (@no, @comment, @created, @uploadfile, @filename, @binfile, @contenttype)
  end
else
  begin 
    Insert Files (no, comment, created, uploadfile, filename, contenttype)
    Values (@no, @comment, @created, @uploadfile, @filename, @contenttype)
  end
GO

I am fairly new to stored procedures, so hopefully it's the if/else condition that's incorrect.

Many thanks in advance,
Mike

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2003-07-16 : 08:29:33

Becasue ELSE isn't recognised in T-SQL?

if (@uploadfile = 1)
begin
Insert Files (no, comment, created, uploadfile, filename, binfile, contenttype)
Values (@no, @comment, @created, @uploadfile, @filename, @binfile, @contenttype)
end
if (@uploadfile = 0)
begin
Insert Files (no, comment, created, uploadfile, filename, contenttype)
Values (@no, @comment, @created, @uploadfile, @filename, @contenttype)
end

This should work....and then read BOL for structure/rules regarding IF.

Edited by - AndrewMurphy on 07/16/2003 08:31:46

ebbmar
Starting Member

2 Posts

Posted - 2003-07-16 : 09:15:15

Well, I am using MS SQL Server Enterprise manager to write my procedures in, and it complains if I don't use "else" between to blocks.
I even tried adding "if (@uploadfile = 0)" below the else (which appears to be valid syntax), but it still ignores the last block.

Thanks anyways :)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-07-16 : 11:40:34

your stored proc is fine. ELSE is valid in T-SQL.

FYI -- you don't need the BEGIN's and END's because each block is only one statement.

how are you calling this stored proc? are you sure you are passing in a 0 ? or are you leaving it null?

- Jeff

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2003-07-16 : 14:06:09

quote:

your stored proc is fine. ELSE is valid in T-SQL.

FYI -- you don't need the BEGIN's and END's because each block is only one statement.

how are you calling this stored proc? are you sure you are passing in a 0 ? or are you leaving it null?

- Jeff

Jeffs Right Take out the redundent BEGIN and END

Jim
Users <> Logic

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-07-16 : 14:30:00

Your code looks fine and should work.

You CAN use BEGIN and END even if you only have one statement, it's just not necessary. Also, the parenthesis around @uploadfile = 1 is also unnecessary, but it still works.

BTW, you should be creating and modifying your stored procedures in Query Analyzer instead of Enterprise Manager because you are going to run into some problems with EM (maybe this is your problem here).

How are you sure that it isn't executing the second block? Have you checked SQL Profiler to determine exactly what INSERT statement is running?

Instead of what you have for the second block, put PRINT 'Test second block'. Execute the stored procedure in Query Analyzer and see if you get the print statement in the results pane. Let us know.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources