quick question/long answer? - 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

Transact-SQL (2000)

quick question/long answer?

Author

Topic

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2005-06-24 : 04:41:00

I'm hoping there is a shortcut for this but I suspect not. If anyone has any suggestions I would be grateful

I have to put an "audit trail" into a text field so I may have

CREATE PROCEDURE MyProc
@pk int
@Data1 varchar(80)
@Data2 real
@data2 varchar(4)

...

and my audit trail needs to look (something) like
DECLARE @AUDIT varchar(4000)
SET @AUDIT = 'pk ' + CAST(@PK as varchar(10)) + 'Data1 ' + @Data1 + ...

My problem is that if one of the values of my parameters is NULL then @AUDIT is NULL

I know the data probably shouldn't be null but I have no control of that and some of it may actually be unknown (date of death anybody?)

What's the best approach to dealing with this. Should I set a load of default values for my sproc parameters or might it be better to have a whole load of ISNULL statements or something altogether different?

many thanks

steve

Alright Brain, you don't like me, and I don't like you. But lets just do this, and I can get back to killing you with beer.

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2005-06-24 : 04:44:36

Just realised that the option of a whole load of defaults may actually break the app - yes it happened before!!) so I suppose I have my answer unless someone has a brilliant idea.

cheers

steve

Alright Brain, you don't like me, and I don't like you. But lets just do this, and I can get back to killing you with beer.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-06-24 : 06:00:36

so how is putting the defaults different than doing ISNULL a few times.
i'd go for defaults....

Go with the flow & have fun! Else fight the flow

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2005-06-24 : 08:16:18

I would normally BUT I know that there is at least one place where putting '' as a default instead of NULL will make the app fall over!!

In fact I found out the hard way

steve

Alright Brain, you don't like me, and I don't like you. But lets just do this, and I can get back to killing you with beer.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-06-24 : 08:20:39

yes but if you have a null in your contencation the whole string will become null, no?
so you still have to use isnull() and won't that still crash the app??
BTW... stupid app for letting a little null crash it

Go with the flow & have fun! Else fight the flow

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-06-24 : 08:23:31

I have this in all of my stored procedures. Its come in very handy for me.

The part in blue you have to modify dependent on the input variables


	/*****************************************************
	Start Stored Procedure Tracking
	*****************************************************/
	Declare @runId uniqueidentifier,
		@execStr varchar(4000)
	
	Select @runId = newId()
	Select @execStr = convert(varchar,db_Name()) + '.dbo.' + isnull((Select name from sysobjects where id = @@procId),'unknown') + ' ' 
		+ isnull(''''+@varchar+'''','null') + ', ' + isnull(convert(varchar,@int),'null') + ', ' 
		+ isnull(''''+convert(varchar,@date,121)+'''','null') + ', ' + isnull(convert(varchar,@bit),'null')
	Insert Into netprofit.dbo.proc_log_detail Select runId = @runId, procId = isnull(@@ProcId,0), starttime = getdate(), null, @execStr
	/*****************************************************
	End Stored Procedure Tracking
	*****************************************************/

	--Your Stored Procedure
	Select [Corey Says] = 'Hi'

	/*****************************************************
	Start Stored Procedure Tracking
	*****************************************************/
	Update netprofit.dbo.proc_log_detail Set endtime = getdate() where runId = @runId
	/*****************************************************
	End Stored Procedure Tracking
	*****************************************************/

and the table used for storage:


CREATE TABLE dbo.proc_log_detail (
	runId uniqueidentifier NOT NULL ,
	procId int NOT NULL ,
	starttime datetime NOT NULL ,
	endtime datetime NULL ,
	execStr varchar (4000) NULL 
)

Corey

Co-worker on The Wizard of Oz "...those three midgets that came out and danced, the freaked me out when I was little. But they are ok now."

Crito
Starting Member

40 Posts

Posted - 2005-06-24 : 11:19:26

SET CONCAT_NULL_YIELDS_NULL OFF

Crito
Starting Member

40 Posts

Posted - 2005-06-24 : 11:21:47

... at the beginning of your proc... and sorry for the short answer. ;)

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2005-06-27 : 04:18:05

Many thanks Guys.

Spirit - Legacy App. Need I say more?

Corey - That's great, many thanks it will come in very handy.

Crito - That's exactly what I was looking for but obviously missed in BOL. Many thanks. It works in SQL Server 7 too.

steve

Alright Brain, you don't like me, and I don't like you. But lets just do this, and I can get back to killing you with beer.

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2005-06-27 : 04:33:44

Should anyone be interested I just found this [url]http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q294942&ID=KB;EN-US;Q294942&[url]

Looks like ISNULL wins out!

Thanks again guys

steve

Alright Brain, you don't like me, and I don't like you. But lets just do this, and I can get back to killing you with beer.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources