Dynamic SQL use - 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)

Dynamic SQL use

Author

Topic

brianlitt
Starting Member

8 Posts

Posted - 2002-07-03 : 14:19:50

UPDATE PART_TRANS_COUNTER SET ARRIVALS = @TotalIn, DEPARTURES = @TotalOut WHERE ENTITY_ID = @CurENTITY_ID AND PART_ID = @CurPART_ID

Is this illegal Syntax? Specifically, the SET ARRIVALS = @TotalIn. Do I NEED to put @SQL = "UPDATE PART_TRANS_COUNTER SET ARRIVALS =" & @TotalIn & ", DEPARTURES = " & @TotalOut & "WHERE ..."

Thanks,
Brian

macka
Posting Yak Master

162 Posts

Posted - 2002-07-03 : 14:24:32

The syntax looks fine.

Can you post the DDL of the table (the CREATE TABLE statement) ?

What type of variable is @TotalIn ?

If you can provide a little more information it'd help.

macka.

brianlitt
Starting Member

8 Posts

Posted - 2002-07-03 : 14:36:26

Well, i'm trying to update a counter basically. There is one database that has a trigger in it which basically sends all the information to a new database. Where i come in is at the new database. I'm looking at the LAST_TRANSIT_MESSAGE table and once it is updated, i want to see if ACTION inside of it has been changed and if it has changed the add an arrival to MY table (PART_TRANS_COUNTER). The reason i want to see if it has changed is i don't want to count two arrival messages of the same part twice, it needs to depart, and then arrive (obviously). It can be either DEPARTURE or ARRIVAL. I looked on the FAQ about accessing other tables and tehy always had this au.some_table which i didn't understand where the au came from. I'm currently surfing the net to find this, it's losing me.

here's what my trigger looks like.
CREATE TRIGGER Adder ON LAST_TRANSIT_MESSAGE FOR UPDATE AS
DECLARE @OldAction varchar(256)
DECLARE @NewAction varchar(256)

DECLARE @TotalIn int

DECLARE @TotalOut int

DECLARE @CurENTITY_ID int
DECLARE @CurPART_ID int
DECLARE @CurZONE_ID int
DECLARE @CurCONTAINER_ID int

SELECT @OldAction = ACTION FROM deleted
SELECT @NewAction = ACTION FROM inserted

IF @NewAction <> @OldAction
BEGIN
SELECT @CurZONE_ID = ZONE_ID FROM inserted
SELECT @CurENTITY_ID = ENTITY_ID FROM ZONE WHERE ZONE_ID=@CurZONE_ID
SELECT @CurCONTAINER_ID FROM inserted
SELECT @CurPART_ID = PART_ID FROM CONTAINER_ITEM WHERE CONTAINER_ID = @CurCONTAINER_ID

SELECT @TotalIn = ARRIVALS FROM FogBreak.dbo.PART_TRANS_COUNTER WHERE CONTAINER_ID = @CurCONTAINER_ID
AND ENTITY_ID = @CurENTITY_ID
SELECT @TotalOut = DEPARTURES FROM PART_TRANS_COUNTER WHERE CONTAINER_ID = @CurCONTAINER_ID
AND ENTITY_ID = @CurENTITY_ID
IF (@NewAction) = "ARRIVAL"

BEGIN
SET @TotalIn = @TotalIn + 1
UPDATE PART_TRANS_COUNTER SET ARRIVALS = @TotalIn,
DEPARTURES = @TotalOut
WHERE ENTITY_ID = @CurENTITY_ID AND PART_ID = @CurPART_ID
END

ELSE

BEGIN
SET @TotalOut = @TotalOut + 1
UPDATE PART_TRANS_COUNTER SET ARRIVALS = @TotalIn,
DEPARTURES = @TotalOut
WHERE ENTITY_ID = @CurENTITY_ID AND PART_ID = @CurPART_ID

END
END

I can't tell if that looks formatted because of this window. I'm getting some errors like "Invalid Column name : CONTAINER_ID"

the LAST_TRANSIT_MESSAGE table looks like this:

PK,Fk1 CONTAINER_ID
FK ZONE_ID
ACTION
ACTION_TIME

and my table, PART_TRANS_COUNTER is

PK ENTITY_ID
PK PART_ID
ARRIVALS
DEPARTURES

your help is greatly appreciated,
Brian Littleton

brianlitt
Starting Member

8 Posts

Posted - 2002-07-03 : 14:44:16

okay, i understand about how to access other tables now. Most of my code is changing to this type:

SELECT @TotalIn = ARRIVALS
FROM FogBreak.dbo.PART_TRANS_COUNTER ptc
WHERE ptc.CONTAINER_ID = @CurCONTAINER_ID AND ptc.ENTITY_ID = @CurENTITY_ID

I don't need to copy the whole trigger, but that is what i am doing.

macka
Posting Yak Master

162 Posts

Posted - 2002-07-03 : 14:52:00

Brian,

I would guess that the 'au' you refer to is a table alias for the authors table which comes with the sample Pubs database - so I wouldn't worry about that.

If @NewAction DOES equal @OldAction then the variable @TotalOut has not been initialised. If you don't initialise a variable you wish to increment then it will try to add 1 to NULL which is NULL. I would do this after you have declared your variables with :

SET @TotalOut = 0

This piece of code doesn't look right:

SELECT @TotalOut = DEPARTURES FROM PART_TRANS_COUNTER WHERE CONTAINER_ID = @CurCONTAINER_ID

Going by your table definitions, its the LAST_TRANSIT_MESSAGE which has a container ID.

How are these two tables related ?

brianlitt
Starting Member

8 Posts

Posted - 2002-07-03 : 14:52:28

here's my real trigger. I figured that snippit of code would be junk. hey, by the way, if you're trying to learn SQL don't buy a book called Beginning Visual Basic SQL Server :)

CREATE TRIGGER Adder ON LAST_TRANSIT_MESSAGE FOR UPDATE AS

/*******************/
/* DECLARE LOCALS **/
/*******************/
DECLARE @OldAction varchar(256)
DECLARE @NewAction varchar(256)

DECLARE @TotalIn int

DECLARE @TotalOut int

DECLARE @CurENTITY_ID int
DECLARE @CurPART_ID int
DECLARE @CurZONE_ID int
DECLARE @CurCONTAINER_ID int

/*********************************/
/* Get New And Old Values From DB*/
/*********************************/

SELECT @OldAction = ACTION FROM deleted
SELECT @NewAction = ACTION FROM inserted

/*********************************************/
/* IF THEY ARE DIFFERENT, UPDATE COUNTING DB */
/*********************************************/

IF @NewAction <> @OldAction
BEGIN /*BEGIN MAIN PART*/

/**********************************************************/
/* GET INFORMATION ON NEW ENTITY_ID, ZONE_ID, and PART_ID */
/**********************************************************/

SELECT @CurZONE_ID = ZONE_ID
FROM inserted

SELECT @CurENTITY_ID = ENTITY_ID
FROM FogBreak.dbo.ZONE zone
WHERE zone.ZONE_ID=@CurZONE_ID

SELECT @CurCONTAINER_ID
FROM inserted

SELECT @CurPART_ID = PART_ID
FROM FogBreak.dbo.CONTAINER_ITEM ci
WHERE ci.CONTAINER_ID = @CurCONTAINER_ID

/*************************************************************/
/* GET TOTAL DEPARTURES AND ARRIVALS FROM PART_TRANS_COUNTER */
/*************************************************************/

SELECT @TotalIn = ARRIVALS
FROM FogBreak.dbo.PART_TRANS_COUNTER ptc
WHERE ptc.CONTAINER_ID = @CurCONTAINER_ID AND ptc.ENTITY_ID = @CurENTITY_ID
SELECT @TotalOut = DEPARTURES
FROM FogBreak.dbo.PART_TRANS_COUNTER ptc
WHERE ptc.CONTAINER_ID = @CurCONTAINER_ID AND ENTITY_ID = @CurENTITY_ID

/*****************************************************************/
/* NOW UPDATE P_T_C BY INCREMENTING ARRIVAL OR DEPARTURE COUNTER */
/* AND PUTTING IT IN THE TABLE FOR THE SPECIFIC PART AND ENTITY */
/*****************************************************************/

IF (@NewAction) = "ARRIVAL"

BEGIN
SET @TotalIn = @TotalIn + 1
UPDATE FogBreak.dbo.PART_TRANS_COUNTER ptc
SET ptc.ARRIVALS = @TotalIn, ptc.DEPARTURES = @TotalOut
WHERE ptc.ENTITY_ID = @CurENTITY_ID AND ptc.PART_ID = @CurPART_ID
END

ELSE

BEGIN
SET @TotalOut = @TotalOut + 1
UPDATE Fogbreak.dbo.PART_TRANS_COUNTER ptc
SET ptc.ARRIVALS = @TotalOut, ptc.DEPARTURES = @TotalOut
WHERE ptc.ENTITY_ID = @CurENTITY_ID AND ptc.PART_ID = @CurPART_ID

END
END

brianlitt
Starting Member

8 Posts

Posted - 2002-07-03 : 15:00:34

The only tables i use are:

ZONE

PK ZONE_ID
FK ENTITY_ID
and others that don't apply.

LAST_TRANSIT_MESSAGE
PK CONTAINER_ID
FK ZONE_ID
ACTION

PART_TRANS_COUNTER
PK ENTITY_ID
PK PART_ID
ARRIVALS
DEPARTURES

CONTAINER
PK CONTAINER_ID
PK ENTITY_ID
QUANTITY <-- (does not apply)

So i'm looking at LAST_TRANSIT_MESSAGE, when it updates i check to see if the last transit message for a certain container was arrival or departure. If it's a new message (not two departs in a row), then i add one to either the ARRIVALS counter which is in PART_TRANS_COUNTER or add one to DEPARTURES which is in the same table. But i want the counter to be for a specific part so i say under this entity, this part, has arrived X times and departed y times.

I hope this helps. Thank you very much for your help.

Brian Littleton

brianlitt
Starting Member

8 Posts

Posted - 2002-07-03 : 15:17:19

one of my errors right now is the if statement on NewAction = "ARRIVAL". It seems to think that is a column name. Is this not the best way to test to see if the data = "ARRIVAL"? This comes from ACTION under the LAST_TRANSIT_MESSAGE table which can either be "ARRIVAL" or "DEPARTURE"

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-07-03 : 15:27:02

DECLARE @NewAction varchar(256)

IF (@NewAction = 'ARRIVAL')
PRINT 'TRUE'
ELSE
PRINT 'FALSE'

Use Single quotes

Michael

<Yoda>Use the Search page you must. Find answer you will.

macka
Posting Yak Master

162 Posts

Posted - 2002-07-03 : 15:33:46

quote:
This piece of code doesn't look right:

SELECT @TotalOut = DEPARTURES FROM PART_TRANS_COUNTER WHERE CONTAINER_ID = @CurCONTAINER_ID

You need to join the two tables here using the zone table.

SELECT @TotalOut = DEPARTURES
FROM PART_TRANS_COUNTER
INNER JOIN ZONE ON ZONE.ENTITY_ID = PART_TRANS_COUNTER.ENTITY_ID
INNER JOIN LAST_TRANSIT_MESSAGE ON LAST_TRANSIT_MESSAGE.ZONE_ID = ZONE.ZONE_ID
WHERE CONTAINER_ID = @CurCONTAINER_ID

macka.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources