Analyzer works- SP does not - 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)

Analyzer works- SP does not

Author

Topic

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-02-19 : 20:26:55

Hi

I have an SP which returns an extra records - more than when I run the same code in Query Analyzer. I'm missing something really obvious:

Here's the code:
CREATE PROCEDURE sp_Test AS

declare @svalue nvarchar(50)
set @svalue = NULL

select * into #b from tRoles where groupname= @svalue
select * from #b

drop table #b

Now when I run the code in Query Analyzer I get no records. But the sp returns heaps.

Suggestions?

Edited by - rrb on 02/19/2002 21:24:17

Tigger
Yak Posting Veteran

85 Posts

Posted - 2002-02-19 : 20:35:36

Don't you just love it when that happens!

Is there a primary key or something you can use to find out which is the extra record? eg if primary key is an integer, sum up the primary keys for both querys and the difference is the extra record.

That might give you a clue as to what is going on.

If all you've done is paste code then I would have thought it must be something to do with how the parameters are being passed.

Sorry I can't help more - if I even helped at all.

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-02-19 : 20:54:17

quote:

Don't you just love it when that happens!

No. Actually I know which is the extra record. The code shown is actually a simplification (believe it or not) of the real thing, which I've reduced down to the point where the difference is just a single record... I might see if I can reduce it further.

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-02-19 : 21:44:50

This is probably due to the SET ANSI_NULLS setting that was in effect when the SP was created. I believe that QA has a different ANSI setting than the SP has. Let me guess...you created the SP through Enterprise Manager, right?

Secondly, you really shouldn't use the equal sign (=) to test for Nulls. Even with the ANSI_NULLS setting enabled, if you need to check for Null you should use:

SELECT * INTO #b FROM tRoles WHERE groupname Is Null

You should write your procedure like this:

CREATE PROCEDURE sp_Test AS
DECLARE @svalue nvarchar(50)
SET @svalue = NULL

IF @svalue IS NULL
BEGIN
SELECT * INTO #b FROM tRoles WHERE groupname Is Null
END
ELSE
BEGIN
SELECT * INTO #b FROM tRoles WHERE groupname= @svalue
END

SELECT * FROM #b
DROP TABLE #b

It's a good practice to have specific null-handling if your table(s) allow nulls, and not to rely on the ANSI_NULLS setting.

Tigger
Yak Posting Veteran

85 Posts

Posted - 2002-02-19 : 21:49:18

I've just been having a play in Query Analyzer and if I run

Select * from tableA where fieldA = null

I get nothing back (same as you)

However when I run

Select * from tableA where fieldA is null

I get loads of records.

Looks like nulls might be being handled differently between SP and Query analyzer.

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-02-19 : 21:56:16

quote:

Secondly, you really shouldn't use the equal sign (=) to test for Nulls.

Yep I agree - I was seting @svalue = NULL to simulate what I was getting from another table....

eg
select @sgroupname = groupname from table a

but to highlight that the problem was failing when @sgroupname was NULL. How can I do
where groupname = @svalue
and guard against the case where it might be NULL? And before you jump in, I already tried
where groupname = @svalue and groupname is not null
and it doesn't work

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2002-02-19 : 21:57:28

Ever wondered why when you script out SP's that there are a bunch of SET statements preceding the creation of the SP?????

The ANSI settings that are active for the connection when the SP is complied is used in the SP..

DavidM

Tomorrow is the same day as Today was the day before.

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-02-19 : 22:00:26

quote:

Ever wondered why when you script out SP's that there are a bunch of SET statements preceding the creation of the SP?????.

David, I've always wondered about a lot of things...but now that I have SQLTeam -I need worry no more...

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-02-19 : 22:10:11

robvolk,
your suggested change to my sp results in an error, (I'd already tried that, but EM doesn't let me do select * into #b on two lines - even if there is an "if" ?)

even adjusting the script results in
Server: Msg 2714, Level 16, State 1, Procedure sp_PURCHASING_Rules, Line 23
There is already an object named '#b' in the database.

Edited by - rrb on 02/19/2002 22:13:26

Nazim
A custom title

1408 Posts

Posted - 2002-02-19 : 23:35:18

You can use a if condition with IsNull and branch your conditions accordingly

quote:

but to highlight that the problem was failing when @sgroupname was NULL. How can I do
where groupname = @svalue
and guard against the case where it might be NULL? And before you jump in, I already tried
where groupname = @svalue and groupname is not null
and it doesn't work

This isnt a EM problem. your last call to sp resulted in creating the table #b. but before it could be dropped some error might have occured and it is still there. run a Drop table #b , your sp should work fine. i would suggest you to use if ..Exist and check for existence of #b then go for select * into .. .

quote:

even adjusting the script results in
Server: Msg 2714, Level 16, State 1, Procedure sp_PURCHASING_Rules, Line 23
There is already an object named '#b' in the database.

HTH

--------------------------------------------------------------

Edited by - Nazim on 02/19/2002 23:39:42

Nazim
A custom title

1408 Posts

Posted - 2002-02-19 : 23:44:19

AND Dont start your stored procedure with sp_ , when you execute it the system first searches in your master database then comes to your local DB ,so taking some extra time.

--------------------------------------------------------------

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-02-20 : 00:03:46

Nazim

if I try do drop #b I get
Server: Msg 3701, Level 11, State 5, Line 1
Cannot drop the table '#b', because it does not exist in the system catalog.

And if I don't, then I get the above error when I try to create the SP.

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

Nazim
A custom title

1408 Posts

Posted - 2002-02-20 : 02:12:30

Drop the Sp and recreate it . it should solve your problem.

HTH

--------------------------------------------------------------

Edited by - Nazim on 02/20/2002 02:13:46

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-02-20 : 02:14:06

quote:

Drop the Sp recreate it . it should solve your problem.

Actually I exported the sp to text, and then ran it. That should've dropped the sp. It still comes up with the same error....Maybe an SQL 2000 problem?

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

Nazim
A custom title

1408 Posts

Posted - 2002-02-20 : 02:18:54

Exporting a sp to text , drops the sp????. i dont think so... then.. am not sure ...coz i never did anything like that.

why dont you explicitly drop it using drop procedure spname

quote:

Actually I exported the sp to text, and then ran it. That should've dropped the sp. It still comes up with the same error....Maybe an SQL 2000 problem?

--------------------------------------------------------------

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-02-20 : 02:23:25

quote:

Exporting a sp to text , drops the sp????. i dont think so... then.. am not sure ...coz i never did anything like that.

No I just mean that when you export it to text, the first lines of the text are
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[PURCHASING_Rules]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[PURCHASING_Rules]
which should drop the procedure.

Actually since then, I have also explicitly dropped and re-built the procedure - but I still have the same problem....

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

Nazim
A custom title

1408 Posts

Posted - 2002-02-20 : 02:27:29

Ah! you are right there.

hmmm why dont you change the name of the temp table from #b to #tempRoles or something different for a workaround.

meanwhile can you post your whole sp code. i would like to test it.

--------------------------------------------------------------

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-02-20 : 02:32:29

quote:

meanwhile can you post your whole sp code. i would like to test it.

Thanks Nazim. I suspect it's an SQL server 2000 EM thing!!

Try this

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Test]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Test]
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE dbo.Test AS

if (0=1)
select 1 as id into #temptableforNazim
else
select 0 as id into #temptableforNazim

drop table #temptableforNazim
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

Nazim
A custom title

1408 Posts

Posted - 2002-02-20 : 02:41:01

Argh! i got it. the problem is you have multiple creation table creation statements with same table name. which Sql Server doesnt allows(God knows Why??).

something like this will also not work

CREATE PROCEDURE dbo.Testi @m char AS

if (@m='1')
begin
create table #tt(a int)
drop table #tt
end
else
begin
create table #tt(b int)
drop table #ttt
end
GO

CREATE PROCEDURE dbo.Test AS

if (0=1)
select 1 as id into #temptableforNazim
else
select 0 as id into #temptableforNazim

drop table #temptableforNazim
GO

you have to change the names of the two tables

if (0=1)
select 1 as id into #temptableforNazim
else
select 0 as id into #temptableforNazimnew

HTH

--------------------------------------------------------------

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-02-20 : 04:29:06

quote:

Argh! i got it.

yay! now at least I know I'm not crazy.

Unfortunately, this is not the solution, and yes, I have already tried this. You see, I want the results finally in the same table.

Now if I do a
select * from temptableforNazim union select * from temptableforNazimNew
after the "if" statement (my next guess), then EM reports another error - mainly along the lines of the fact that one of the tables does not exist. (I can't remember the error message and I'm at home so I can't check it...)

I can't see an easy solution to my dilemma - even with dynamic sql. I'm using select * into #a so that future maintainers don't have to alter the sp when the fieldnames or number of fields in the table are changed....and even using dynamic sql - it looks as if I'd have to use a real (ie non-temp) table

to transfer the data from the dynamic exec batch back to the sp.

So (breath) - do I set ANSI_NULLS ON????

PS (I have a "dodgy" workaround which works - but I'd rather not use it...)

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

nr
SQLTeam MVY

12543 Posts

Posted - 2002-02-20 : 05:10:28

Haven't read the whole of this thread but sounds like it's to do with the way sql server checks objects.

When it does the object resolution it will take notice of temp table creates within the sp - but if it already has a definition it will ignore the create (actually I think it searches for the first definition of that name).
Hence if a temp table is created outside the sp it will take that definition for the structure.
If it is not created outside the sp then it will attempt to use the create statements within the sp - but does this independent of the control of flow within the SP (as you would expect).

hence if you define two temp tables with the same name it will compile with the object definition of the first.

so
create procedure a
as
create table #a(i int)
exec b
go
create procedure b
as
create table #a(j int)
select * from #a --(1)
select j from #a --(2)
select i from #a --(3)
go

(1) works happily and gets #a(j)
both (2) and (3) give invalid column (but for different reasons).

This is all caused by the change of the way SPs are compiled in v7.
It didn't cause a problem in v6.5.

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources