MS SQL Server - Why is this SP not working properly?

Author

Why is this SP not working properly?

Gabriella

2006-10-25, 6:00 am

Why is this SP not working properly?

CREATE PROCEDURE insertBP
(
@URL VARCHAR(300),
@BPID INT OUTPUT
)
AS

SELECT @BPID = BPID FROM Table1 WHERE URL = @URL
IF @BPID = NULL
BEGIN
... INSERT STATEMENT
SELECT @BPID = @@IDENTITY
(Inside IF is working OK!)
END

I tried
IF @BPID = NULL
IF @BPID IS NULL
IF @BPID = ""
IF @BPID = ''

I KNOW that the SELECT statement returns empty, but it never goes into
the IF!!

Any ideas?

Thanks,
Gabi

David Portas

2006-10-25, 6:00 am

"Gabriella" <frohlinger@yahoo.com> wrote in message
news:1161632498.689421.99290@b28g2000cwb.googlegroups.com...
> Why is this SP not working properly?
>
> CREATE PROCEDURE insertBP
> (
> @URL VARCHAR(300),
> @BPID INT OUTPUT
> )
> AS
>
> SELECT @BPID = BPID FROM Table1 WHERE URL = @URL
> IF @BPID = NULL
> BEGIN
> ... INSERT STATEMENT
> SELECT @BPID = @@IDENTITY
> (Inside IF is working OK!)
> END
>
> I tried
> IF @BPID = NULL
> IF @BPID IS NULL
> IF @BPID = ""
> IF @BPID = ''
>
> I KNOW that the SELECT statement returns empty, but it never goes into
> the IF!!
>
> Any ideas?
>
> Thanks,
> Gabi
>

There are perhaps two problems here.

Firstly you can't compare NULL using = unless SET ANSI_NULLS is OFF. The
preferred setting for that option is ON. Always include SET ANSI_NULLS ON
before your procedure declaration unless you intended differently. Use
"@bpid IS NULL" to test for a null.

Secondly, if your SELECT statement returns zero rows then it will NOT assign
a NULL to @bpid. Instead @bpid will retain the value it had *before* the
SELECT statement. In this case that means whatever value was passed to the
proc. This "feature" of assignment in a SELECT statement is one reason why I
usually prefer the SET syntax instead unless I need to assign multiple
variables. Try:

SET @BPID =
(SELECT BPID FROM Table1 WHERE URL = @URL);

That way, @BPID will be NULL if the subquery returns zero rows. If that
isn't what you intended then use @@ROWCOUNT to test whether rows were
returned instead of testing the value of the variable

Hope this helps.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Arnie Rowland

2006-10-25, 6:00 am

How do you know if execution falls inside the conditional block -the SELECT
@BPID = @@IDENTITY is only an assignment and will NOT provide any output.

Change to [IS NULL], and add a statement similar to [SELECT 'Inside IF is
working OK!'] inside the conditional block.

--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous

You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf

"Gabriella" <frohlinger@yahoo.com> wrote in message
news:1161632498.689421.99290@b28g2000cwb.googlegroups.com...
> Why is this SP not working properly?
>
> CREATE PROCEDURE insertBP
> (
> @URL VARCHAR(300),
> @BPID INT OUTPUT
> )
> AS
>
> SELECT @BPID = BPID FROM Table1 WHERE URL = @URL
> IF @BPID = NULL
> BEGIN
> ... INSERT STATEMENT
> SELECT @BPID = @@IDENTITY
> (Inside IF is working OK!)
> END
>
> I tried
> IF @BPID = NULL
> IF @BPID IS NULL
> IF @BPID = ""
> IF @BPID = ''
>
> I KNOW that the SELECT statement returns empty, but it never goes into
> the IF!!
>
> Any ideas?
>
> Thanks,
> Gabi
>

Gabriella

2006-10-25, 6:00 am

Thanks everyone. It's working now :)

Sponsored Links