Need help on store procedure
-
I have a store procedure and I am getting null value for one of my declare variable and I am trying to add that variable into my insert statement and it giving me null value error. Here is the procedure.
CREATE PROCEDURE [dbo].[Insert_Inspection]
@Receiving int
AS
Declare @LotNo varchar(50)
select @LotNo = [Lot No] from Receiving where [Receiving Log No] = @Receiving
Declare @PartId varchar(50)
Select @PartId = Max([Part ID]) + 1 from [Receiving Inspection Header] where [Receiving Log No] = @Receiving And [
“Need code to set @PartId =1 when it return Null”
SET NOCOUNT OFF;
INSERT INTO dbo.[Receiving Inspection Header] ([Receiving Log No], [Lot No], [Part Id]) VALUES (@Receiving, @LotNo, @PartId)
GO
For variable @PartId – There is no record present so the select statement for @PartId is giving my NULL value and if I try to use NULL into Insert statement it is giving me error. I would like to set @PartId = 1 when it return NULL from select statement. How can I do that?
-
Select @PartId = ISNULL(Max([Part ID]),0) + 1 from [Receiving Inspection Header] where [Receiving Log No] = @Receiving And [Lot No] = @LotNo
-
Or you could do this:
INSERT INTO dbo.[Receiving Inspection Header] ([Receiving Log No], [Lot No], [Part Id]) VALUES (@Receiving, @LotNo, ISNULL( @PartId,1) )
-
Coalesce(@PartID, 1)
That'll give you 1 if it's null, and @PartID if it isn't.
-
@PartId should probably be declared as an Int and not a VarChar
@PartId is not initialzed and therefore is null before the select:
If the select returns 0 rows then the @PartId will be unchanged and will remain null.
If the select does contain rows and the all values for Part_ID are null then @PartId will be assigned null.
After the select:
Set @PartId = IsNull( @PartId, 1 )
Whether the select returns a null or returns no rows, the Set IsNull will give the desired results.
-
I like the coalesce. Nice and succinct, and from a newbie ?!
Added later: Whoops! Didn't mean to call you a newbie - just realised that Newbie refers to how many posts you've made.
Best Regards
Terry -
LOL that's ok.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply