The script below is just the beginning of something a bit more complicated, which I'm not sure how I'll explain so depending on the amount of posts I'll explain further.
For the time being can anybody see if there's anything wrong with how I've scripted the SP below particulary the loop (Check thread - Stored Procedure Programming)?
Thanks for your input in advance.
CREATE PROCEDURE newimr_sp_duplicate_report
@countryid as INT,
@marketsectorid as INT, -- marketsector to transfer from
@specialreportid as INT
@other_marketsectorid as INT
if (@marketsectorid = 1)
set @other_marketsectorid = 2
set @other_marketsectorid = 1
declare topicids cursor for
select id, description from tbl_topics where specialreportid = 7 and marketsectorid = @marketsectorid
while 1 = 1
@other_topicid as int,
@description as varchar(255)
fetch next from topicids into @other_topicid, @description
if @@fetch_status <> 0
Okay, here's my attempt to get across what I want to achieve.
it's not at all clear which table you're trying to insert the duplicates into
the set-based solution will involve INSERT/SELECT synyax, like this --
, countryid )
from reports as r
join topics as t
on r.topicid = t.id
and t.marketsectorid = 1
where r.countryid = 7
yes, I understand I'm supposed to post my query with the tables in question and some sample data. But where on that page? I clicked on the SQLTeam link and then I'm not sure where to go... don't know if I'm really stoopid or what...
brett, perhaps what you could do is write up the "here's how to ask a question" stuff as a new thread here, and i'll make it a "sticky" which means it will forever stay at the top of the forum list (for example, see mysql forum)