NULL problem - 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)

NULL problem

Author

Topic

TAS
Yak Posting Veteran

65 Posts

Posted - 2004-04-23 : 15:33:30

I have store procedure like this:

Alter Procedure "ProcCustInfUpdate"
@AgentCode varchar(5),
@OrgAgentCode varchar(5)
As

Set Nocount ON

If (@OrgAgentCode <> @AgentCode) OR (@OrgAgentCode IS NULL AND @AgentCode IS NOT NULL)OR (@OrgAgentCode IS NOT NULL AND @AgentCode IS NULL)

Begin
Insert into tblChng Values(@SSN,'Agent Code',@OrgAgentcode, @AgentCode,Getdate(),Substring(System_User,7,30))
END

I just want to add to tblChng when any chang happens.
The problem is it add to tblChng when (@OrgAgentCode IS NULL and @AgentCode IS NULL).

Why?

X002548
Not Just a Number

15586 Posts

Posted - 2004-04-23 : 15:47:48

That's because NULL is ALWAYS never equal to NULL

So your condition 1 is TRUE

Pluse, you should add error handling and supply a column list for your INSERT

Also DROP and CREATE, don't ALTER

And add SET NOCOUNT OFF on the way out....

(you don't have to though)

Brett

8-)

SamC
White Water Yakist

3467 Posts

Posted - 2004-04-23 : 15:59:24

[code]IF @OrgAgentCode IS NOT NULL AND @AgentCode IS NOT NULL AND @OrgAgentCode <> @AgentCode BEGIN
-- It's your thing. Do what you wanna do here.
END[/code]

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-04-23 : 16:05:01

why drop and create and not alter?

- Jeff

X002548
Not Just a Number

15586 Posts

Posted - 2004-04-23 : 16:08:13

Well, you're right...I guess it was the view that got funky with the ALTER....

Just habit I guess....

Do you do ALTER?

Brett

8-)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-04-23 : 16:50:29

yeah, usually that's what i use ...

- Jeff

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-23 : 16:56:37

I usually use ALTER in development so that I don't have to regrant permissions. To deploy the objects to another environment, I use DROP/CREATE.

Tara

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-04-23 : 17:16:04

We always do a drop/create. The Grants are included in the script stored in SourceSafe. All developers have Query Analyzer set to auto-generate the existing permissions, ansi-nulls, etc.

The drop/create is the only way to modify the "create date" so we can tell when procedures were last modified without going to SourceSafe. It also allows us to be consistent....one rule for everything.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Kristen
Test

22859 Posts

Posted - 2004-04-24 : 12:17:13

quote:
Originally posted by X002548

Also DROP and CREATE, don't ALTER

We do that, but I wonder if we should

It mucks up SYSDEPENDS if we just "freshen" an SProc that is EXEC'd by loads of others (unless we recreate all of them too, but that seems a waste if they haven't changed ...

All our SProcs are in individual files (rather than right clicking the SProc in Enterprise Manager and doing PROPERTIES, for example!!) and we are contemplating changing them to:


if NOT exists(SELECT * FROM sysobjects WHERE ...)
CREATE PROCEDURE foo
AS
SELECT 1
GO

ALTER PROCEDURE foo
... lots of real code
GO

Is this a really really bad idea?

Kristen

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-04-24 : 23:59:52

Keep it simple Kristen, choose one way or the other. ALTER has the possibility to mess up sysdepends just as bad. It depends on the order of your releases.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Kristen
Test

22859 Posts

Posted - 2004-04-25 : 00:57:19

quote:
Originally posted by derrickleggett

Keep it simple Kristen, choose one way or the other. ALTER has the possibility to mess up sysdepends just as bad. It depends on the order of your releases.

Well, the general idea we had was that if we could rely on sysdepends we could use it as a means of working out the order we should create each SProc. Dunno if that will work as a plan though?

Kristen

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-04-25 : 14:35:04

You could analyze syscomments or the text of the procedures compared against information schema also. This would allow it to work on completely new procedures also.

Let me know what you decide to do. This is always an interesting project. One place I worked, I had the standards include all dependent procedures in the comments header. This worked really well there. We did an audit periodically for QA. The sysdepends was "always" up-to-date there. It's still the best overall solution I've ever seen in place.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Kristen
Test

22859 Posts

Posted - 2004-04-26 : 15:40:47

quote:
Originally posted by derrickleggett

You could analyze syscomments or the text of the procedures compared against information schema also. This would allow it to work on completely new procedures also.

Let me know what you decide to do. This is always an interesting project.

I'd have to get in to parsing the SQL in syscomments though, wouldn't I? For example, if I had EXEC MyChildSProc in some commented out bit of code I wouldn't want that to be part of the assumed dependancies?

This is basically a difference of opinion between an [R&D] colleague and me.

My approach is to concatenate a single SQL file of all the individual SQL files changed since the last date we did a release (created by a batch file, which also creates a "marker" directory entry, which shows up nicely in a date/time-sorted-directory-listing).

I run this composite SQL file on the target machine; if it gives DEPENDS errors I just run it again - not because anything will break, but because I'm happier that sysdepends is in Good Order. (This is made possible because the script does "DROP MySProc CREATE MySProc" pairs - rather than DROP ALL SProcs and then CREATE ALL SProcs ... as per SQL Script generated in Enterprise Manager).

My colleague is keen to generate the Composite SQL Script by reference to the sysdepends table in the DB, rather than the most-recent-update-date on the individual SQL scripts. Personally, I'm not so sure we need to worry about keeping sysdepends "accurate"

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources