StoredProcedure @variable = table.field not working

Question

jtok 10 Light Poster

16 Years Ago

I am using SQL Server 2005 to run a stored procedure to update, delete, or insert depending on some conditions.
The problem is that it gives the following error:
Msg 4104, Level 16, State 1, Line 49
The multi-part identifier "PART.ID" could not be bound.

I have it inserting some useless data right now to test it. The problem lies with determining If @PrtID1 = PART.ID
Here is a copy of the stored procedure:

DECLARE @ProblemID int
DECLARE	@PartDescription1 nvarchar(100)
DECLARE	@PartCost1 money
DECLARE @PartCharge1 money
DECLARE @PartOrderedFrom1 nvarchar(40)
DECLARE @PartOrderNumber1 nvarchar(20)
DECLARE @PartDescription2 nvarchar(100)
DECLARE @PartCost2 money
DECLARE @PartCharge2 money
DECLARE @PartOrderedFrom2 nvarchar(40)
DECLARE @PartOrderNumber2 nvarchar(20)
DECLARE @PrtID1 int
DECLARE @PrtID2 int
DECLARE @PartDescription3 nvarchar(100)
DECLARE @PartCost3 money
DECLARE @PartCharge3 money
DECLARE @PartOrderedFrom3 nvarchar(40)
DECLARE @PartOrderNumber3 nvarchar(30)
DECLARE @PrtID3 int



Select @PartDescription1 = 'PleaseWork'
Select @PartCost1 = 12.00
Select @PartCharge1 = 12.00
Select @PartOrderedFrom1 = 'BooYA!'
Select @PartOrderNumber1 = 'abc123'
Select @PrtID1 = 16

Select @PartDescription2 = NULL
Select @PartCost2 = NULL
Select @PartCharge2 = NULL
Select @PartOrderedFrom2 = NULL
Select @PartOrderNumber2 = NULL
Select @PrtID2 = 19
Select @ProblemID = 11

Select @PartDescription3 = 'Three'
Select @PartCost3 = 1.00
Select @PartCharge3 = 3.00
Select @PartOrderedFrom3 = 'Hyper'
Select @PartOrderNumber3 = 'N/A'
Select @ProblemID = 11



	DECLARE @PartID1 char(10)
	If @PrtID1 IS NOT NULL AND @PrtID1 = PART.ID AND @PartDescription1 IS NOT NULL AND @PartCost1 IS NOT NULL
BEGIN
		Update PART
		Set PartDescription = @PartDescription1, PartCost = @PartCost1, PartCharge = @PartCharge1, OrderedFrom = @PartOrderedFrom1, OrderNumber = @PartOrderNumber1
		Where ID = @PrtID1
		Select @PartID1=@PrtID1
END
	Else If @PrtID1 IS NOT NULL AND @PrtID1 = PART.ID AND @PartDescription1 IS NULL
BEGIN
		Delete From PROBLEM_PART Where PROBLEM_ID = @ProblemID AND PART_ID = @PrtID1
		Delete From PART Where ID = @PrtID1
		Select @PartID1=@PrtID1
END
	Else If @PrtID1 IS NOT NULL AND @PrtID1 != PART.ID AND @PartDescription1 IS NOT NULL AND @PartCost1 IS NOT NULL
BEGIN
		Insert PART (PartDescription,PartCost,PartCharge,OrderedFrom,OrderNumber) Values(@PartDescription1,@PartCost1,@PartCharge1,@PartOrderedFrom1,@PartOrderNumber1)
		Select @PartID1=@@Identity
END



	DECLARE @PartID2 char(10)
	If @PrtID2 IS NOT NULL AND @PrtID2 = PART.ID AND @PartDescription2 IS NOT NULL AND @PartCost2 IS NOT NULL
BEGIN
		Update PART
		Set PartDescription = @PartDescription2, PartCost = @PartCost2, PartCharge = @PartCharge2, OrderedFrom = @PartOrderedFrom2, OrderNumber = @PartOrderNumber2
		Where ID = @PrtID2
		Select @PartID2=@PrtID2
END
	Else If @PrtID2 IS NOT NULL AND @PrtID2 = PART.ID AND @PartDescription2 IS NULL
BEGIN
		Delete From PROBLEM_PART Where PROBLEM_ID = @ProblemID AND PART_ID = @PrtID2
		Delete From PART Where ID = @PrtID2
		Select @PartID2=@PrtID2
END
	Else If @PrtID2 IS NOT NULL AND @PrtID2 != PART.ID AND @PartDescription2 IS NOT NULL AND @PartCost2 IS NOT NULL
BEGIN
		Insert PART (PartDescription,PartCost,PartCharge,OrderedFrom,OrderNumber) Values(@PartDescription2,@PartCost2,@PartCharge2,@PartOrderedFrom2,@PartOrderNumber2)
		Select @PartID2=@@Identity
END


	DECLARE @PartID3 char(10)
	If @PrtID3 IS NOT NULL AND @PrtID3 = PART.ID AND @PartDescription3 IS NOT NULL AND @PartCost3 IS NOT NULL
BEGIN
		Update PART
		Set PartDescription = @PartDescription3, PartCost = @PartCost3, PartCharge = @PartCharge3, OrderedFrom = @PartOrderedFrom3, OrderNumber = @PartOrderNumber3
		Where ID = @PrtID3
		Select @PartID3=@PrtID3
END
	Else If @PrtID3 IS NOT NULL AND @PrtID3 = PART.ID AND @PartDescription3 IS NULL
BEGIN
		Delete From PROBLEM_PART Where PROBLEM_ID = @ProblemID AND PART_ID = @PrtID3
		Delete From PART Where ID = @PrtID3
		Select @PartID3=@PrtID3
END
	Else If @PrtID3 IS NOT NULL AND @PrtID3 != PART.ID AND @PartDescription3 IS NOT NULL AND @PartCost3 IS NOT NULL
BEGIN
		Insert PART (PartDescription,PartCost,PartCharge,OrderedFrom,OrderNumber) Values(@PartDescription3,@PartCost3,@PartCharge3,@PartOrderedFrom3,@PartOrderNumber3)
		Select @PartID3=@@Identity
END




	If @PartID1 != PROBLEM_PART.PART_ID AND @PartDescription1 IS NOT NULL AND @PartCost1 IS NOT NULL
		Insert PROBLEM_PART Values(@ProblemID,@PartID1)
	If @PartID2 != PROBLEM_PART.PART_ID AND @PartDescription2 IS NOT NULL AND @PartCost2 IS NOT NULL
		Insert PROBLEM_PART Values(@ProblemID,@PartID2)
	If @PartID3 != PROBLEM_PART.PART_ID AND @PartDescription3 IS NOT NULL AND @PartCost3 IS NOT NULL
		Insert PROBLEM_PART Values(@ProblemID,@PartID3)

Thanks,
J'Tok

mssql

1 Contributor
2 Replies
124 Views
1 Day Discussion Span
Latest Post 16 Years Ago Latest Post by jtok

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

jtok 10 Light Poster · Answer 1 · 2008-01-26T22:36:32+00:00

I think I'm not asking the question correctly, or simply enough.
What I need to do in this stored procedure is see if the part ID variables I'm passing into it already exist in the PART table. Hence:
@PrtID1 = PART.ID

I'm just not sure how to make it do this.

- J'Tok

Edit: Do I actually need to be doing this in the where clause?

jtok 10 Light Poster · Answer 2 · 2008-01-27T03:36:33+00:00

Finally figured it out! My approach was all wrong. Here's what I came up with:

DECLARE @ProblemID int
DECLARE @PartDescription1 nvarchar(100)
DECLARE @PartCost1 money
DECLARE @PartCharge1 money
DECLARE @PartOrderedFrom1 nvarchar(40)
DECLARE @PartOrderNumber1 nvarchar(20)
DECLARE @PartDescription2 nvarchar(100)
DECLARE @PartCost2 money
DECLARE @PartCharge2 money
DECLARE @PartOrderedFrom2 nvarchar(40)
DECLARE @PartOrderNumber2 nvarchar(20)
DECLARE @PrtID1 int
DECLARE @PrtID2 int
DECLARE @PartDescription3 nvarchar(100)
DECLARE @PartCost3 money
DECLARE @PartCharge3 money
DECLARE @PartOrderedFrom3 nvarchar(40)
DECLARE @PartOrderNumber3 nvarchar(30)
DECLARE @PrtID3 int

Select @PartDescription1 = 'PleaseWork'
Select @PartCost1 = 12.00
Select @PartCharge1 = 12.00
Select @PartOrderedFrom1 = 'BooYA!'
Select @PartOrderNumber1 = 'abc123'
Select @PrtID1 = 16

Select @PartDescription2 = NULL
Select @PartCost2 = NULL
Select @PartCharge2 = NULL
Select @PartOrderedFrom2 = NULL
Select @PartOrderNumber2 = NULL
Select @PrtID2 = 19
Select @ProblemID = 11

Select @PartDescription3 = 'Three'
Select @PartCost3 = 1.00
Select @PartCharge3 = 3.00
Select @PartOrderedFrom3 = 'Hyper'
Select @PartOrderNumber3 = 'N/A'

DECLARE @PartID1 char(10)
If @PrtID1 IS NOT NULL AND @PartDescription1 IS NOT NULL AND @PartCost1 IS NOT NULL
BEGIN
Update PART
Set PartDescription = @PartDescription1, PartCost = @PartCost1, PartCharge = @PartCharge1, OrderedFrom = @PartOrderedFrom1, OrderNumber = @PartOrderNumber1
Where ID = @PrtID1
Select @PartID1=@PrtID1
END
Else If @@ROWCOUNT = 0 AND @PrtID1 IS NULL AND @PartDescription1 IS NOT NULL AND @PartCost1 IS NOT NULL
BEGIN
Insert PART (PartDescription,PartCost,PartCharge,OrderedFrom,OrderNumber) Values(@PartDescription1,@PartCost1,@PartCharge1,@PartOrderedFrom1,@PartOrderNumber1)
Select @PartID1=@@Identity
Insert PROBLEM_PART Values(@ProblemID,@PartID1)
END
Else If @@ROWCOUNT = 0 AND @PrtID1 IS NOT NULL AND @PartDescription1 IS NULL
BEGIN
Delete From PROBLEM_PART Where PROBLEM_ID = @ProblemID AND PART_ID = @PrtID1
Delete From PART Where ID = @PrtID1
Select @PartID1=@PrtID1
END

DECLARE @PartID2 char(10)
If @PrtID2 IS NOT NULL AND @PartDescription2 IS NOT NULL AND @PartCost2 IS NOT NULL
BEGIN
Update PART
Set PartDescription = @PartDescription2, PartCost = @PartCost2, PartCharge = @PartCharge2, OrderedFrom = @PartOrderedFrom2, OrderNumber = @PartOrderNumber2
Where ID = @PrtID2
Select @PartID2=@PrtID2
END
Else If @@ROWCOUNT = 0 AND @PrtID2 IS NULL AND @PartDescription2 IS NOT NULL AND @PartCost2 IS NOT NULL
BEGIN
Insert PART (PartDescription,PartCost,PartCharge,OrderedFrom,OrderNumber) Values(@PartDescription2,@PartCost2,@PartCharge2,@PartOrderedFrom2,@PartOrderNumber2)
Select @PartID2=@@Identity
Insert PROBLEM_PART Values(@ProblemID,@PartID2)
END
Else If @@ROWCOUNT = 0 AND @PrtID2 IS NOT NULL AND @PartDescription2 IS NULL
BEGIN
Delete From PROBLEM_PART Where PROBLEM_ID = @ProblemID AND PART_ID = @PrtID2
Delete From PART Where ID = @PrtID2
Select @PartID2=@PrtID2
END

DECLARE @PartID3 char(10)
If @PrtID3 IS NOT NULL AND @PartDescription3 IS NOT NULL AND @PartCost3 IS NOT NULL
BEGIN
Update PART
Set PartDescription = @PartDescription3, PartCost = @PartCost3, PartCharge = @PartCharge3, OrderedFrom = @PartOrderedFrom3, OrderNumber = @PartOrderNumber3
Where ID = @PrtID3
Select @PartID3=@PrtID3
END
Else If @@ROWCOUNT = 0 AND @PrtID3 IS NULL AND @PartDescription3 IS NOT NULL AND @PartCost3 IS NOT NULL
BEGIN
Insert PART (PartDescription,PartCost,PartCharge,OrderedFrom,OrderNumber) Values(@PartDescription3,@PartCost3,@PartCharge3,@PartOrderedFrom3,@PartOrderNumber3)
Select @PartID3=@@Identity
Insert PROBLEM_PART Values(@ProblemID,@PartID3)
END
Else If @@ROWCOUNT = 0 AND @PrtID3 IS NOT NULL AND @PartDescription3 IS NULL
BEGIN
Delete From PROBLEM_PART Where PROBLEM_ID = @ProblemID AND PART_ID = @PrtID3
Delete From PART Where ID = @PrtID3
Select @PartID3=@PrtID3
END

- J'Tok