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
 General SQL Server Forums
 New to SQL Server Programming
 check for null

Author  Topic 

BendJoe
Posting Yak Master

128 Posts

Posted - 2008-03-11 : 21:56:32
I am trying to create a stored proc.
I have to check a parameter for null value and if null then set it to zero
Here is the stored proc
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[UPDATENOA]
(
@Accepted char(10),
@NOAId bigint,
@Court char(30),
@RetainerType char(30),
@PendingInTaxCourt char(1)
)
AS
begin
UPDATE NOA set StageCode = Accepted,Court=@Court,RetainerType=@RetainerType,PendingInTaxCourt=@PendingInTaxCourt where NOAId = @NOAId;
Declare @Sequence int
Select @Sequence = MAX(Sequence) From [dbo].[NOAStages] where NOAId=@NOAId;
if @Sequence IS NULL
{
@Sequence=0
}
Insert into [dbo].[NOAStages] (NOAId,StageCode,Sequence)
values (@NOAId,@Accepted,@Sequence+1)
But this is giving me error. What is the correct way to do this.
Thanks






khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-03-11 : 22:01:30
what is the error ?

you can use isnull(@Sequence, 0) to do it instead of "IF @Sequence IS NULL . . . ."


KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-11 : 22:05:09
{
@Sequence=0
}

those brackets are wrong..that my be it to..is better

if you use IF

IF @Sequence isNull
SET @Sequence = 0



or as the other suggestion just do

SET @Sequence = isnull(@Sequence,0)

Poor planning on your part does not constitute an emergency on my part.

Go to Top of Page

BendJoe
Posting Yak Master

128 Posts

Posted - 2008-03-11 : 22:08:01
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[UPDATENOA]
(
@Accepted char(10),
@NOAId bigint,
@Court char(30),
@RetainerType char(30),
@PendingInTaxCourt char(1)
)
AS
begin
UPDATE NOA set StageCode = @Accepted,Court=@Court,RetainerType=@RetainerType,PendingInTaxCourt=@PendingInTaxCourt where NOAId = @NOAId
Declare @Sequence int
Select @Sequence = MAX(Sequence) From [dbo].[NOAStages] where NOAId=@NOAId
ISNULL (@Sequence,0)
Insert into [dbo].[NOAStages] (NOAId,StageCode,Sequence)
values (@NOAId,@Accepted,@Sequence+1)
Msg 102, Level 15, State 1, Procedure UPDATENOA, Line 14
Incorrect syntax near 'ISNULL'.
Msg 102, Level 15, State 1, Procedure UPDATENOA, Line 16
Incorrect syntax near ')'.
The first message is for the ISNULL line
The second message is for the last line.
Thanks






Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-03-11 : 22:12:44
[code]
Select @Sequence = MAX(Sequence) From [dbo].[NOAStages] where NOAId=@NOAId
ISNULL (@Sequence,0)
Insert into [dbo].[NOAStages] (NOAId,StageCode,Sequence)
values (@NOAId,@Accepted,isnull(@Sequence,0)+1)
[/code]


KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-11 : 22:16:27
SET @Sequence 0= ISNULL(@Sequence,0)

the 2nd error is likely related to the first. fruit of the poisoned tree.

But this works too (without having to use the above..)

Insert into [dbo].[NOAStages] (NOAId,StageCode,Sequence)
Select @NOAId,@Accepted,isNull(@sequence,0)+1

If you are trying to do a running total or anything...might check this out:
http://www.sqlservercentral.com/articles/Advanced+Querying/61716/














Poor planning on your part does not constitute an emergency on my part.

Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-03-11 : 23:59:07
quote:
Originally posted by dataguru1971

SET @Sequence 0 = ISNULL(@Sequence,0)




extra 0 there. Must be typo


KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page
   

- Advertisement -