Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

ms sql server 2005 error with trigger

Posted on 2011-03-18
4
Medium Priority
?
362 Views
Last Modified: 2012-05-11
here is the section of code that I ge the following errors from...

--


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

-- if recp...STOP...then STOP...
if @campaign_message = 'STOP'
BEGIN

INSERT INTO Message_Queue (TO_Number, Campaign_Message)
Values (@to_number,'You have been opted out of this campaign. Thank you for your time.')

Update grp_campaign set stop = 'STOP' where to_number = @to_number
and sms_server_num = @sms_server_number

END



-- if recp...send message..repond appropriatelly...
if @campaign_message <> ''
BEGIN

-- 1. get the old message into a var

declare @old_grp_msg nvarchar(max)
@old_grp_msg = (SELECT rolling_convo_msg from grp_campaign
where to_number = @to_number and sms_server_num =  @sms_server_number)

-- 2. then tack on the newest response + NEW LINE SPACE!!!

declare @grp_tc_id nvarchar(max)
@grp_tc_id = (SELECT TC_ID from grp_campaign
where to_number = @to_number and sms_server_num =  @sms_server_number)

declare @new_part nvarchar(max)
@new_part = @old_grp_msg + '%0a' + @new_part

-- 3. get the last 500 chars into a new var
update grp_campaign set rolling_convo_msg = RIGHT(@new_part,500)
where tc_id = @grp_tc_id

-- 4. send update msg to all participats
insert into message_queue (to_number,campaign_message)
SELECT to_number, rolling_convo_msg as 'campaign_message'
where tc_id = @grp_tc_id



END


--

Msg 102, Level 15, State 1, Procedure TRIGGER_GRP_Msg_Queue_Recieve, Line 85
Incorrect syntax near '@old_grp_msg'.
Msg 102, Level 15, State 1, Procedure TRIGGER_GRP_Msg_Queue_Recieve, Line 91
Incorrect syntax near '@grp_tc_id'.
Msg 102, Level 15, State 1, Procedure TRIGGER_GRP_Msg_Queue_Recieve, Line 95
Incorrect syntax near '@new_part'.
Msg 137, Level 15, State 2, Procedure TRIGGER_GRP_Msg_Queue_Recieve, Line 98
Must declare the scalar variable "@new_part".
Msg 137, Level 15, State 2, Procedure TRIGGER_GRP_Msg_Queue_Recieve, Line 104
Must declare the scalar variable "@grp_tc_id".
0
Comment
Question by:GlobaLevel
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 2

Expert Comment

by:EL_Barbado
ID: 35169983
You need to use the SELECT clause in front of the variable when populating variables from SQL Statements. Try the code below instead. Im sure it will work nicely for you.


-- 1. get the old message into a var

declare @old_grp_msg nvarchar(max)
SELECT @old_grp_msg = rolling_convo_msg from grp_campaign
where to_number = @to_number and sms_server_num =  @sms_server_number

-- 2. then tack on the newest response + NEW LINE SPACE!!!

declare @grp_tc_id nvarchar(max)
SELECT @grp_tc_id = TC_ID from grp_campaign
where to_number = @to_number and sms_server_num =  @sms_server_number)

declare @new_part nvarchar(max)
SELECT @new_part = @old_grp_msg + '%0a' + @new_part

-- 3. get the last 500 chars into a new var
update grp_campaign set rolling_convo_msg = RIGHT(@new_part,500)
where tc_id = @grp_tc_id

-- 4. send update msg to all participats
insert into message_queue (to_number,campaign_message)
SELECT to_number, rolling_convo_msg as 'campaign_message'
where tc_id = @grp_tc_id



END
0
 
LVL 2

Accepted Solution

by:
EL_Barbado earned 2000 total points
ID: 35169988
I've neglected to remove an unneeded parenthesis in #2.

Try this instead for #2
-- 2. then tack on the newest response + NEW LINE SPACE!!!

declare @grp_tc_id nvarchar(max)
SELECT @grp_tc_id = TC_ID from grp_campaign
where to_number = @to_number and sms_server_num =  @sms_server_number

declare @new_part nvarchar(max)
SELECT @new_part = @old_grp_msg + '%0a' + @new_part
0
 
LVL 10

Author Comment

by:GlobaLevel
ID: 35169995
I figured it out myself...i needed the set statement..you used the select...either way enjoy the points...
0
 
LVL 10

Author Comment

by:GlobaLevel
ID: 35169998
I was going to reward points anyhow...though I figured it myself...
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …

604 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question