Solved

ms sql server 2005 error with trigger

Posted on 2011-03-18
4
355 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
  • 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 500 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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL query bug 3 28
Using CTE to insert records into a table 2 26
SQL Statement to Update Email Domain 2 18
MS SQL Inner Join - Multiple Join Parameters 2 17
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
This is a video describing the growing solar energy use in Utah. This is a topic that greatly interests me and so I decided to produce a video about it.
Need to grow your business through quality cloud solutions? With everything required to build a cloud platform and solution, you may feel like the distance between you and the cloud is quite long. Help is here. Spend some time learning about the Con…

948 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now