SQL server exams - Coco's Question of the Week #5, the Procedure

Author

Coco's Question of the Week #5, the Procedure

cocolocopolo

2002-09-03, 1:28 am

I tried 'follow' book instruction to create a procedure and I list the code as follow:

Create Procedure prHelloWorld
@name varchar (80), @location varchar (80) = Null, @date_executed datetime Output As
Declare @print_string varchar (200)
Declare @Return int

set @date_executed = GetDate ( )

If @location is Null
Set @print_string = 'Hello ' +@name
Set @Return = 1

Else
Set @print_string = 'Hello ' +@name+ ', Welcome to ' + @location
Set @Return = 0

Print @print_string
Return @return

But I got error:
========
Server: Msg 156, Level 15, State 1, Procedure prHelloWorld, Line 12
Incorrect syntax near the keyword 'Else'.

Please help where I made mistake.

2lazybutsmart

2002-09-03, 8:26 am

if you want to SET or SELECT something. use the BEGIN...END Statement.

As in.
-------------------
IF (condition)
BEGIN
<statements>
END
ELSE
BEGIN
<statements>
END
-----------------------------
so in your case. your condition code would look like this:
------------------------------
If @location is Null
BEGIN
Set @print_string = 'Hello ' +@name
Set @Return = 1
END
Else
BEGIN
Set @print_string = 'Hello ' +@name+ ', Welcome to ' + @location
Set @Return = 0
END

GregLrsn

2002-09-03, 9:21 am

Get rid of the ELSE and add the an IF @location is not null. The IF ELSE return TRUE | FALSE. NULL is not true or false but unknown.

@name varchar (80),
@location varchar (80) = Null,
@date_executed datetime Output As
Declare @print_string varchar (200)
Declare @Return int
set @date_executed = GetDate ( )

If @location is Null
Set @print_string = 'Hello ' +@name
Set @Return = 1

IF @location is not null
Set @print_string = 'Hello ' +@name+ ', Welcome to ' + @location
Set @Return = 0

Print @print_string
Return @return
--------------------
Here are the test statements:

prHelloWorld 'Greg', @date_executed = '09/03/02'

prHelloWorld 'Greg', 'Washington', '09/03/02'

----------------------------
Next is the ELSE statement in a TRUE | FALSE
situation:

DROP Procedure prHelloWorld
GO
CREATE Procedure prHelloWorld
@name varchar (80),
@location varchar (80),
@date_executed datetime Output As
Declare @print_string varchar (200)
Declare @Return int
set @date_executed = GetDate ( )
IF @location = null
Set @print_string = 'Hello ' +@name
Set @Return = 1

IF
@location = 'California'
Set @print_string = 'Hello ' +@name+ ', You are not welcome to ' + @location + ' Please leave.'
Else
Set @print_string = 'Hello ' +@name+ ', Welcome to ' + @location
Set @Return = 0
Print @print_string
Return @return
------------------
Following is the test statements:

prHelloWorld 'Greg', 'California', '09/03/02'
prHelloWorld 'Greg', 'Washington', '09/03/02'

2lazybutsmart

2002-09-03, 7:56 pm

yeah, but GregLsrn, Null in a condition expression will either return true or false.

IF X IS NULL (could return True/False).

And i've used it couple of times before.

GregLrsn

2002-09-04, 10:47 am

The IF, ELSE returns TRUE | FALSE on a Boolean expression where 0 = false, 1 = True.
You can bypass the Boolean expression search by using IS [ NULL | NOT NULL ]. This would not be a "true false condition expression" but a condition on the column containing the value N-U-L.and L and a condition on the column to contain a value NOT N-U-L and L.

This is why the "helloworld" procedure works when you remove the ELSE from the statement and add a second IF statement in its place (If @location is NOT Null).

The "ANSI settings" on/off returns values based on the ANSI character(s) in the column row.

Cut and Paste the statements (including yours) into Query Analyzer you'll see Coco's expression works as I've stated.

From BOL,
The value NULL means the data value for the column is unknown or not available. NULL is not synonymous with zero (numeric or binary value), a zero-length string, or blank (character value). Rather, null values allow you to distinguish between an entry of zero (numeric columns) or blank (character columns) and a nonentry (NULL for both numeric and character columns).
Care must be taken when comparing null values. The behavior of the comparison depends on the setting of the SET ANSI_NULLS option.

When SET ANSI_NULLS is ON, a comparison in which one or more of the expressions is NULL does not yield either TRUE or FALSE; it yields UNKNOWN. This is because a value that is unknown cannot be compared logically against any other value. This occurs if either an expression is compared to the literal NULL, or if two expressions are compared and one of them evaluates to NULL. For example, this comparison always yields UNKNOWN when ANSI_NULLS is ON:

ytd_sales > NULL

This comparison also yields UNKNOWN any time the variable contains the value NULL:

ytd_sales > @myVariable

2lazybutsmart

2002-09-04, 12:42 pm

ur absolutley right, but what i was saying was preceeding NULL with IS or NOT.

cheers

cocolocopolo

2002-09-05, 1:30 am

Thanks 2lazybutsmart and GregLrsn (alphabetical order).

It works now. But I feel really frustrated it takes a lot of programming just in order to create a simple procedure.

I had no programming experience before and really confusing when I tried to practise creating procedure, function, trigger .....

Anyway, thanks again.

Sponsored Links