Stuck! Newbie needs help. - SQL Server Forums

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

SQL Server 2000 Forums

SQL Server Development (2000)

Stuck! Newbie needs help.

Author

Topic

Tim_Field
Yak Posting Veteran

87 Posts

Posted - 2003-04-17 : 05:34:22

Hi all,

Hopefully someone out there can help. I'm pretty new to this so please be patient!

I'm passing through an XML string (works fine) as a string of ID's. These get put into a temporary table. I delete the ID's not found in the temporary table out of the database using the DELETE command. I then insert the ID's that are in the temporary table but not in the database. Are the commands that I'm using best for this job?

I get the following errors in the script below. Not sure why! Must declare the variable '@TempTable'. Occurs on the FETCH NEXT line.
Incorrect syntax near '@PersonID'. Occurs on the IF SELECT line. So I'm stuck! Can anyone help out.

Many Thanks, Tim

--------------------------------------------------------------

declare @TempTable table
(personID int)

DELETE FROM sims.sims_member WHERE person_id NOT IN (SELECT personID FROM @TempTable)

WHILE @@FETCH_STATUS = 0 -- Fetch was succesful.
BEGIN
FETCH NEXT FROM @TempTable TT
IF SELECT TT.personID FROM sims.sims_member WHERE sims.sims_person = TT.personID
BEGIN
INSERT INTO sims.sims_member
(base_group_id, person_id)
VALUES
(@groupID, TT.personID)
END
END

samsekar
Constraint Violating Yak Guru

437 Posts

Posted - 2003-04-17 : 05:56:33

quote:

I delete the ID's not found in the temporary table out of the database using the DELETE command. I then insert the ID's that are in the temporary table but not in the database. Are the commands that I'm using best for this job?

Hmmm..
Say I have persion id 1-5 in sims_member and 4-6 in temp table.
For your first delete command I will loose record with person id 1,2,3. Then you want to insert records, which are not in the table means only record 6.
Finally the table will have 4-6 records exactly the records of temporary table. Is that what you are trying to accomplish??

quote:
Incorrect syntax near '@PersonID'.

I don’t see @personId in your syntax & where do you get the @groupId

Can you please post the sql scripts and exact logic

Sekar
~~~~
Success is not a destination that you ever reach. Success is the quality of your journey.

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-04-17 : 06:04:49

Two problems i can see:

Firstly

WHILE @@FETCH_STATUS = 0 -- Fetch was succesful.
BEGIN
FETCH NEXT FROM @TempTable TT
IF SELECT TT.personID FROM sims.sims_member WHERE sims.sims_person = TT.personID
BEGIN
INSERT INTO sims.sims_member
(base_group_id, person_id)
VALUES
(@groupID, TT.personID)
END
END

These statements are used only with cursors, and you havent declared one. In any case, you should learn to live without cursors for as long as possible, as they are slow and not too efficient

Secondly, I dont understand the logic behind this piece of code:

IF SELECT TT.personID FROM sims.sims_member WHERE sims.sims_person = TT.personID
BEGIN
INSERT INTO sims.sims_member
(base_group_id, person_id)
VALUES
(@groupID, TT.personID)
END

The syntax is wrong as well, but i cant help you with it unless I know what you are trying to do. The code implies that you are looking for a record in sims_member for a particular person_id. But if you find that record, you proceed to insert another record for the same person_id...doesnt make sense to me.

A clarification is in order

Tim F
Starting Member

35 Posts

Posted - 2003-04-17 : 06:21:38

Hi All,

Appologies! I should have posted the whole script. Thanks for replying.

"Say I have persion id 1-5 in sims_member and 4-6 in temp table.
For your first delete command I will loose record with person id 1,2,3. Then you want to insert records, which are not in the table means only record 6.
Finally the table will have 4-6 records exactly the records of temporary table. Is that what you are trying to accomplish?? "

This is 100% what I'm trying to do. Glad your explanation was better than mine.

Many Thanks, Tim

/*--------------------------------------------------------------------------------------------------------------*/

/* */
/*--------------------------------------------------------------------------------------------------------------*/

set nocount on
go

------------------------------------------------------------------------------------------------------------------

exec sims.db_p_drop 'sims.ra_pix_PreGroupMemberDetails_SaveGroupMembers','P'
go

Alter procedure sims.ra_pix_PreGroupMemberDetails_SaveGroupMembers
(
@xmlpersonids text,
@groupID int

)
--with encryption
as
/*--------------------------------------------------------------------------------------------------------------*/
/* */
/* Results : N\A */
/* : */
/* */
/*--------------------------------------------------------------------------------------------------------------*/

/*--------------------------------------------------------------------------------------------------------------*/
/* */
/* Format : Transact-SQL */
/* Object : sims.ra_pix_PreGroupMemberDetails_SaveGroupMembers */
/* */
/* Purpose : Saves applicant records */
/* Comments : */
/* */
/* Author : Timothy Field */
/* Date : 16/04/03 */
/* */
/*--------------------------------------------------------------------------------------------------------------*/

--print 'sims.ra_pix_PreGroupMemberDetails_SaveGroupMembers'
set nocount on

--
--- declare system variables
--

declare @errcode int
, @retcode int
, @rowcount int

--
--- initialise system variables
--

select @errcode = 0
, @retcode = 0
, @rowcount = 0

--
--- declare local variables
--

declare @TempTable table
(personID int)

declare @XmlDocHandle int

--
--- check for connection
--
/*exec @retcode = sims.db_p_has_connection
if @retcode > 3 return @retcode*/

--
--- main process
--
---------------------
-- get a handle to the XML Document
exec sp_xml_preparedocument @XmlDocHandle output, @xmlpersonids

insert into @TempTable
select personID
from
openxml(@XmlDocHandle,'data/row',1)
with
(personID int)

--- release the handle to the XML Document
exec sp_xml_removedocument @XmlDocHandle

Declare @group_code t_pub_code_10_required

Declare @baseGroupID int
SELECT @baseGroupID = (SELECT base_group_type_id FROM sims.sims_base_group_type WHERE code = 'Intake')
--print @baseGroupID

DELETE FROM sims.sims_member WHERE base_group_id = @groupID AND person_id NOT IN (SELECT personID FROM @TempTable)

WHILE @@FETCH_STATUS = 0 -- Fetch was succesful.
BEGIN
FETCH NEXT FROM @TempTable TT
IF SELECT TT.personID FROM sims.sims_member WHERE base_group_id = @baseGroupID AND sims.sims_person = TT.personID
BEGIN
INSERT INTO sims.sims_member
(base_group_id, person_id)
VALUES
(@groupID, TT.personID)
END
END

return 0
go

exec sims.db_p_check 'sims.ra_pix_PreGroupMemberDetails_SaveGroupMembers'
go

------------------------------------------------------------------------------------------------------------------
/* TEST
exec sims.db_p_instance_init
exec sims.ra_pix_PreGroupMemberDetails_SaveGroupMembers 'XML String'
exec sims.db_p_instance_drop */

samsekar
Constraint Violating Yak Guru

437 Posts

Posted - 2003-04-17 : 06:45:43

If that is the case why don't you delete all the records in sims_member for that group id and Insert whatever in the temptable.

HTH..
DELETE FROM SIMS_MEMBER WHERE GROUPID=@GROUPID
INSERT INTO SIMS_MEMBER
SELECT @GROUPID,PERSONID FROM @TEMPTABLE

Sekar
~~~~
Success is not a destination that you ever reach. Success is the quality of your journey.

Edited by - samsekar on 04/17/2003 06:49:17

Tim_Field
Yak Posting Veteran

87 Posts

Posted - 2003-04-17 : 06:54:07

"If that is the case why don't you delete all the records in sims_member for that group id and Insert whatever in the temptable. "

Certainly a valid point! However there are more fields in sims.sims_member than just base_group_id and person_id, so unfortunatly I can't blanket delete.

Thanks, Tim

samsekar
Constraint Violating Yak Guru

437 Posts

Posted - 2003-04-17 : 07:39:41

I am not able to understand your requirement properly, anyway try this & let us know.

DELETE FROM sims.sims_member WHERE base_group_id = @groupID AND person_id NOT IN (SELECT personID FROM @TempTable)

INSERT INTO sims.sims_member (base_group_id, person_id)
SELECT @GROUPID, PERSONID FROM @TEMPTABLE WHERE NOT IN
(SELECT PERSIONID FROM sims.sims_member)

Sekar
~~~~
Success is not a destination that you ever reach. Success is the quality of your journey.

Tim_Field
Yak Posting Veteran

87 Posts

Posted - 2003-04-17 : 09:38:10

Hi,

I'll try to be clearer!

If you pass through ID's (stored in TempTable) of:
1
2
5
6

And in the sims.sims_member table the ID's are currently:
2
5
9

Then I must end up with:
1
2
5
6

The way to do this is to delete record ID 9 and add record ID 1.
(NOTE: I CANNOT JUST DELETE OUT AND RE-ADD, there are more fields in sims.sims_member and I don't want to loose their values). Thus I have the delete command which I believe is fine:

DELETE FROM sims.sims_member WHERE base_group_id = @groupID AND person_id NOT IN (SELECT personID FROM @TempTable)

and I need to get the insert working:

Code ??

Hope this is clear....

Your insert statement errors saying: Incorrect syntax near the keyword 'IN'.
INSERT INTO sims.sims_member (base_group_id, person_id)
SELECT @GROUPID, PERSONID FROM @TEMPTABLE WHERE NOT IN
(SELECT PERSIONID FROM sims.sims_member)

Any ideas.....
Many Thanks, Tim

Tim_Field
Yak Posting Veteran

87 Posts

Posted - 2003-04-17 : 11:11:11

AHHH! Getting further now....I think you missed person_id after the NOT IN. Still not running though, it doesn't liek person_id in the first line. Bit confusing this! Thanks, Tim

Latest:
INSERT INTO sims.sims_member (base_group_id, person_id, start_date)
SELECT @groupID, person_id, GETDATE() FROM @TempTable WHERE person_id NOT IN
(SELECT person_id FROM sims.sims_member WHERE base_group_id = @groupID)

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-04-17 : 11:18:47

Hi Tim

Unless i am missing something here, arent you doing a delete and insert and still ending up with the same resultset you start with in the first place? Take a look at the sample data you provided:

quote:

If you pass through ID's (stored in TempTable) of:
1
2
5
6

And in the sims.sims_member table the ID's are currently:
2
5
9

Then I must end up with:
1
2
5
6

Tim_Field
Yak Posting Veteran

87 Posts

Posted - 2003-04-17 : 11:43:58

Hi All,

Got sorted! With alot of tweaking... Thanks for all your help.
mohdowais -> yep I am but as mentioned earlier there are more columns in sims_member that I want to keep so. I can't just delete all the rows and put TempTable back in or I'll loose this data.

Cheers, Tim

Final script!
/*--------------------------------------------------------------------------------------------------------------*/
/* */
/* Module : SIMS Database SQL Server 2000 */
/* Platform : Pulsar */
/* Area : Reports */
/* Copyright : (c) Capita Education Services 1984-2002 */
/* : This code has been written by Capita Education Services for use within its own SIMS systems. */
/* : It is unpublished proprietary work, and as such it is illegal for it to be reproduced by any */
/* : means or used without the express written permission of Capita Education Services. */
/* Status : UNREVIEWED */
/* */
/*--------------------------------------------------------------------------------------------------------------*/

set nocount on
go

------------------------------------------------------------------------------------------------------------------

exec sims.db_p_drop 'sims.ra_pix_PreGroupMemberDetails_SaveGroupMembers','P'
go

Alter procedure sims.ra_pix_PreGroupMemberDetails_SaveGroupMembers
(
@xmlpersonids text,
@groupID int

)
--with encryption
as
/*--------------------------------------------------------------------------------------------------------------*/
/* */
/* Results : N\A */
/* : */
/* */
/*--------------------------------------------------------------------------------------------------------------*/

/*--------------------------------------------------------------------------------------------------------------*/
/* */
/* Format : Transact-SQL */
/* Object : sims.ra_pix_PreGroupMemberDetails_SaveGroupMembers */
/* */
/* Purpose : Saves applicant records */
/* Comments : */
/* */
/* Author : Timothy Field */
/* Date : 16/04/03 */
/* */
/*--------------------------------------------------------------------------------------------------------------*/

--print 'sims.ra_pix_PreGroupMemberDetails_SaveGroupMembers'
set nocount on

--
--- declare system variables
--

declare @errcode int
, @retcode int
, @rowcount int

--
--- initialise system variables
--

select @errcode = 0
, @retcode = 0
, @rowcount = 0

--
--- declare local variables
--

declare @TempTable table
(personID int)

declare @XmlDocHandle int

--
--- check for connection
--
/*exec @retcode = sims.db_p_has_connection
if @retcode > 3 return @retcode*/

--
--- main process
--
---------------------
-- get a handle to the XML Document
exec sp_xml_preparedocument @XmlDocHandle output, @xmlpersonids

insert into @TempTable
select personID
from
openxml(@XmlDocHandle,'data/row',1)
with
(personID int)

--- release the handle to the XML Document
exec sp_xml_removedocument @XmlDocHandle

Declare @group_code t_pub_code_10_required

/* Not required
Declare @baseGroupID int
SELECT @baseGroupID = (SELECT base_group_type_id FROM sims.sims_base_group_type WHERE code = 'Intake')
--print @baseGroupID
*/

DELETE FROM sims.sims_member WHERE base_group_id = @groupID AND person_id NOT IN (SELECT personID FROM @TempTable)

INSERT INTO sims.sims_member (base_group_id, person_id, start_date)
SELECT @groupID, personID, GETDATE() FROM @TempTable WHERE personID NOT IN
(SELECT person_id FROM sims.sims_member WHERE base_group_id = @groupID)

return 0
go

exec sims.db_p_check 'sims.ra_pix_PreGroupMemberDetails_SaveGroupMembers'
go

------------------------------------------------------------------------------------------------------------------
/* TEST
exec sims.db_p_instance_init
exec sims.ra_pix_PreGroupMemberDetails_SaveGroupMembers 'XML String'
exec sims.db_p_instance_drop */

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources