select into a variable - 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 2005 Forums

Transact-SQL (2005)

select into a variable

Author

Topic

dotolee
Starting Member

9 Posts

Posted - 2010-05-20 : 14:25:38

ok. so i have to do something that seems a little backwards. i'm forwarning you because i know you're going to be tempted to comment on how backwards this is. trust me, i know. but i need it do it anyways.

i need a table trigger that will write the pk value to a file whenever a record has been modified to have a status of 1.

this file will then be read by a stored procedure which will simply return the pk value it reads. this stored proc will be called by an external application...

we are using an iway / biztalk type application for the industrial world and as per their support department, this is the only way to get data out from a sql database into their app without "knowing" which record to request. it's a lot more complicated than this but i'm trying to just summarize what's going on at a very high level. in any case, although i dont' quite like the solution myself, i have to give it a try to see if it'll work.

The error I'm getting is "incorrect syntax near '='".


CREATE TRIGGER MYTRIGGER ON DBO.MYTABLE
AFTER UPDATE
AS
DECLARE @PID INT
DECLARE@CMDSTRING VARCHAR(1000)

IF EXISTS
(SELECT @PID = I.PALLETID FROM INSERTED I
WHERE I.STATUS=1)
SET @CMDSTRING='ECHO ' + @PID + ' >> C:\JL\SQLOUTPUT.LOG'
EXEC XP_CMDSHELL @CMDSTRING

any suggestions?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-05-20 : 14:30:34

IF EXISTS (SELECT * FROM INSERTED WHERE STATUS=1)

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-05-20 : 14:31:33

The inserted trigger table could contain multiple rows if the update affected multiple rows. If you need each data value to get into a file where status=1, then you'll need to loop through the inserted table.

Can the update affect more than one row ever?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

dotolee
Starting Member

9 Posts

Posted - 2010-05-20 : 14:33:38

hi. no, actually, it'll only ever return one value.

dotolee
Starting Member

9 Posts

Posted - 2010-05-20 : 14:36:12

by the way, i had a typo in my original post. it's been fixed now. i only have 1 variable called @pid, which i need to save the select result to. in your example, i don't think i can use the wildcard because I need to save the specific column to a variable. this table has several different columns but i'm only interested in one...

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-05-20 : 14:40:12

The wildcard is because that's how you do it with IF EXISTS or IF NOT EXISTS. Since inserted table will only contain one value, you would do this instead:


SELECT @PID = PALLETID 
FROM INSERTED
WHERE STATUS = 1

IF @@ROWCOUNT <> 0  --or check for NULL value in @PID
BEGIN
 SET @CMDSTRING='ECHO ' + @PID + ' >> C:\JL\SQLOUTPUT.LOG'
 EXEC XP_CMDSHELL @CMDSTRING
END

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

dotolee
Starting Member

9 Posts

Posted - 2010-05-20 : 14:51:04

quote:
Originally posted by tkizer

The wildcard is because that's how you do it with IF EXISTS or IF NOT EXISTS. Since inserted table will only contain one value, you would do this instead:

SELECT @PID = PALLETID
FROM INSERTED
WHERE STATUS = 1

IF @@ROWCOUNT <> 0 --or check for NULL value in @PID
BEGIN
SET @CMDSTRING='ECHO ' + @PID + ' >> C:\JL\SQLOUTPUT.LOG'
EXEC XP_CMDSHELL @CMDSTRING
END

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

ok. that got rid of the "=" error but the trigger is failing with the following message:
"conversion failed when converting the varchar 'echo ' to data type int".
I'm not too familiar with TSQL but is there a way to convert the @PID to a string and then concatenate that into the @CMDSTRING variable?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-05-20 : 15:09:57

SET @CMDSTRING='ECHO ' + CONVERT(varchar(20), @PID) + ' >> C:\JL\SQLOUTPUT.LOG'

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

dotolee
Starting Member

9 Posts

Posted - 2010-05-20 : 16:44:19

thanks tara. can you tell me how to now read the text file i just created? i tried downloading some sql functions that provide capabilities to read in a text file but it's giving me grief. I've noticed that the text file that's being generated has what looks like a space, and then a carriage return line feed. will this by default be ignored when i read the text file using tsql? i just want the value itself.

dotolee
Starting Member

9 Posts

Posted - 2010-05-20 : 16:48:37

wmy file looks like:
12345· (after the space, it has what looks like a backwards b, and then a new line. i'm assuming it's the equivalent of CRLF)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-05-20 : 17:00:37

I don't know. Nothing will be ignored unless you tell it to ignore it. I don't understand what you are doing. I thought the whole purpose of writing to a file was so that some other program could get to it. If your intention is to read the file in T-SQL, then this setup doesn't make sense.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

dotolee
Starting Member

9 Posts

Posted - 2010-05-21 : 08:43:26

sorry if I've confused you. THe logic you helped me create to write data to a file will need to be read by another stored procedure. let's just call it "spReadFile". This stored proc is the one that's going to be called from an external application. So the overall picture is:
1. sql database table is updated.
2. trigger is started. data is written to an output field. and trigger also makes a call to an external application. (we didn't discuss this piece of code... I already have it and it seems to work.
3. the external application calls a new stored proc that i still need to create called spReadFile
4. spReadFile opens c:\jl\sqloutput.log, grabs the one value, saves the value to an output parameter, and then deletes the file.

I tried to use some functions i found on the internet to read the file we created but it's failing.
i'm just wondering if you could help me figure out how to do this ...

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-05-21 : 11:18:29

I don't have any experience with what you have described. I'd suggest starting a new topic here so that others can help out.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources