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.
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 zeroHere is the stored procset ANSI_NULLS ONset QUOTED_IDENTIFIER ONgoALTER PROCEDURE [dbo].[UPDATENOA](@Accepted char(10),@NOAId bigint,@Court char(30),@RetainerType char(30),@PendingInTaxCourt char(1) )ASbeginUPDATE NOA set StageCode = Accepted,Court=@Court,RetainerType=@RetainerType,PendingInTaxCourt=@PendingInTaxCourt where NOAId = @NOAId;Declare @Sequence intSelect @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] |
|
|
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 betterif you use IF IF @Sequence isNullSET @Sequence = 0 or as the other suggestion just doSET @Sequence = isnull(@Sequence,0)Poor planning on your part does not constitute an emergency on my part. |
|
|
BendJoe
Posting Yak Master
128 Posts |
Posted - 2008-03-11 : 22:08:01
|
set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgoALTER PROCEDURE [dbo].[UPDATENOA](@Accepted char(10),@NOAId bigint,@Court char(30),@RetainerType char(30),@PendingInTaxCourt char(1) )ASbeginUPDATE NOA set StageCode = @Accepted,Court=@Court,RetainerType=@RetainerType,PendingInTaxCourt=@PendingInTaxCourt where NOAId = @NOAIdDeclare @Sequence intSelect @Sequence = MAX(Sequence) From [dbo].[NOAStages] where NOAId=@NOAIdISNULL (@Sequence,0)Insert into [dbo].[NOAStages] (NOAId,StageCode,Sequence)values (@NOAId,@Accepted,@Sequence+1)Msg 102, Level 15, State 1, Procedure UPDATENOA, Line 14Incorrect syntax near 'ISNULL'.Msg 102, Level 15, State 1, Procedure UPDATENOA, Line 16Incorrect syntax near ')'. The first message is for the ISNULL lineThe second message is for the last line.Thanks |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2008-03-11 : 22:12:44
|
[code]Select @Sequence = MAX(Sequence) From [dbo].[NOAStages] where NOAId=@NOAIdISNULL (@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] |
|
|
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)+1If 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. |
|
|
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] |
|
|
|
|
|
|
|