Problem with alias name

Question

Post reply

Problem with alias name

Chandru -734144

Ten Centuries

Points: 1040
More actions
July 30, 2008 at 4:10 am

#190383

Dear all,
The following is the procedure that i have created for insert and update .The problem nw i'm facing is with the alias name.I get the error like this
Msg 4104, Level 16, State 1, Procedure aup_custom_data, Line 60
The multi-part identifier "custom_data.fk_id" could not be bound.
i have used "custom_data.fk_id" in insert statement
Can any one help me on this.
Create procedure [dbo].[aup_custom_data]
(
@XMLDOC ntext,
@fk_id int=null,
@last_changed_by_login_idint=null
)
as
begin
DECLARE @v_error_no INT
DECLARE @xml_hnd INT
set @fk_id=1
set @last_changed_by_login_id=1
EXEC sp_xml_preparedocument @xml_hnd OUTPUT, @XMLDOC
BEGIN
BEGIN TRY
BEGIN TRAN
UPDATE custom_data
set
custom_field_id=t.custom_field_id,
custom_field_value=t.custom_field_value,
fk_id=@fk_id,
last_update=getdate(),
last_changed_by_login_id=@last_changed_by_login_id
FROM OpenXML(@xml_hnd, '/NewDataSet/Table1', 2)
WITH
(
custom_field_id int,
custom_field_value nvarchar(100)
) t
where custom_data.custom_field_id=t.custom_field_id
And t.custom_field_id in (select custom_field_id from custom_data)
INSERT INTO custom_data
(
custom_field_id,
custom_field_value,
fk_id,
last_changed_by_login_id
)
SELECT
s.custom_field_id,
s.custom_field_value,
@fk_id,
@last_changed_by_login_id
FROM OPENXML(@xml_hnd, '/NewDataSet/Table1', 2)
WITH
(
custom_field_id int,
custom_field_value varchar(100)
)s
--where s.custom_field_id not in(select custom_field_id from custom_data)
--and fk_id not in(select fk_id from custom_data)
where custom_data.fk_id not in(select fk_id from custom_data)
and s.custom_field_id not in(select custom_field_id from custom_data)
EXECUTE sp_xml_removedocument @xml_hnd
COMMIT TRAN
END TRY
BEGIN CATCH
-- insertion failed
SET @v_error_no = 9999
SELECT @v_error_no AS db_error
ROLLBACK TRANSACTION
RETURN
END CATCH
--insertion success
SET @v_error_no = 0
SELECT @v_error_no AS db_error
END
END
thanks.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Hi Chandru,

There's a problem with the INSERT INTO ... SELECT ... block.

SELECT

s.custom_field_id,

s.custom_field_value,

@fk_id,

@last_changed_by_login_id

FROM OPENXML(@xml_hnd, '/NewDataSet/Table1', 2)

WITH

(

custom_field_id int,

custom_field_value varchar(100)

s

--where s.custom_field_id not in(select custom_field_id from custom_data)

--and fk_id not in(select fk_id from custom_data)

where custom_data.fk_id not in(select fk_id from custom_data)

and s.custom_field_id not in(select custom_field_id from custom_data)

- there's no reference to table custom_data in the statement. You can work on the SELECT in isolation of the rest of the procedure.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Chandru -734144 Ten Centuries Points: 1040 More actions · Answer 2

Yes Chris you are correct

then Can you help me on this

My scenario is nothing but

If new fk_id is passed (i.e fk_id which is not present in custom_data table) then i have insert into custom_data table otherwise i have to update the records present in custom_data table.

Thanks for your reply.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

Hi Chandru

Couple of questions:

1. How many rows are returned by the source FROM OpenXML(@xml_hnd, '/NewDataSet/Table1', 2)?

Always one, or possibly more than one? "Always one" would make things easy.

2. Are you ok with pulling data from the XML source into a local temporary table?

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Chandru -734144 Ten Centuries Points: 1040 More actions · Answer 4

Hi Chris,

1.More than one rows will be returned

2.And i dint understand what is need of using a local temp table here??

And if we use a temp table no harm in that.

Thanks.

Chandru

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

Chandru (7/30/2008)
If new fk_id is passed (i.e fk_id which is not present in custom_data table) then i have insert into custom_data table otherwise i have to update the records present in custom_data table.

Do you mean the @fk_id variable passed into the procedure, like this?

IF (SELECT count(*) FROM custom_data WHERE fk_id = @fk_id) = 0

INSERT

ELSE

UPDATE

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Chandru -734144 Ten Centuries Points: 1040 More actions · Answer 6

Yes Chris i too tried in that way but it will create problem

Let me try in a different scenario

Thanks

Chandru.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

Chandru (7/30/2008)
Yes Chris i too tried in that way but it will create problem
Let me try in a different scenario
Thanks
Chandru.

What problem, Chandru? I suspect your answer might be fundamental to the real nature of this problem 😎

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden