Stored Procedure - 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

General SQL Server Forums

New to SQL Server Programming

Stored Procedure

Author

Topic

solutionon
Starting Member

12 Posts

Posted - 2008-07-20 : 15:49:26

After a long search on the net I found this forum. First upon I am giving my cheers to all.
I want your help to create a stored procedure to the below request,

I have a table called MessageIn with Id, CustomerId(numeric), SmsBody(varchar), TagName(varchar), EventType(tinyint),FileId(varchar),Status(tinyint).

and have another table called MessageOut with Id, CustomerId(numeric),
Tagname(varchar),MobileNumber(varchar),SmsBody(varchar),EventID(numeric)

and one more table called Mobiles with Id, MobileNumber(varchar), CustomerId(numeric), FileId(varchar).

Suppose my customerId is 1. Now I have some mobile numbers in the Mobiles table. This table will look like this
Id<><><>MobileNumber<><><>CustomerId<><><>FileId
1<><><><>XXXXXXXXXX12<><><><><>1<><><><><>200807_1
2<><><><>XXXXXXXXXX13<><><><><>1<><><><><>200807_1
3<><><><>XXXXXXXXXX84<><><><><>1<><><><><>200807_1
4<><><><>XXXXXXXXXX04<><><><><>1<><><><><>200807_1
and so on etc

Now suppose one message is inserted into the table called MessageIn. then this table will look like this

Id<><>CustomerId<><>SmsBody<><>TagName<><>EventType<><>FileId<><>Status
1<><><><>1<><><><><>HelloWorld!<><><>TEST<><><>2<><><><>200807_1<><><>1

Here when a record is inserted into this table (eg as above) then this procedure should check whether the EventType is 2 or not and also shuld check whether Status = 1 or not. If EventType = 2 and Status = 1 then the procedure will execute as follows:
First it will get all the mobielenumber from Mobiles table where customerId shoud be eaqual to the CustomerId from MessageIn table and the FileId should be eaqual to the FileId from MessageIn table and then the procedure will bulk insert into MessageOut table with all other values from MessageIn table like TagName, SmsBody, CustomerId.

What I mean by all these is: If any new record inserted into the MessageIn table with EventType is :2 and Status = 1 then the SP will take the corresponding mobilenumber from Mobiles Table and all other values from MessageIn table and then it will bulk insert into MessageOut table.
Here I want to use Bulk insert method because the data is huge. There are huge amount of mobileNumber in the Mobiles table.
Then the MessageOut table will look like this:

Id<><>CustomerId<><>TagName<><>MobileNumber<><><>SmsBody
1<><><><>1<><><><><>TEST<><><><>XXXXXXXXXX12<><><>HellowWorld!
2<><><><>1<><><><><>TEST<><><><>XXXXXXXXXX13<><><>HellowWorld!
3<><><><>1<><><><><>TEST<><><><>XXXXXXXXXX84<><><>HellowWorld!

I hope you will give a solution for this please.

Regards,
Solution

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-21 : 01:58:04

[code]select m.Id, i.CustomerId, i.TagName, m.MobileNumber, i.SmsBody
from MessageIn i inner join Mobiles m
on i.CustomerId = m.CustomerId
where i.EventType = 2
and i.Status = 1[/code]

KH
[spoiler]Time is always against us[/spoiler]

solutionon
Starting Member

12 Posts

Posted - 2008-07-21 : 02:37:39

Dear Mr. Khtan,
Thanks,
Can you write complete sql script including the script to create store procedure and all please
Because I am new to all these stuffs

SolutionOn

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-21 : 02:39:43

[code]
create procedure your_procedure_name
as
begin
select m.Id, i.CustomerId, i.TagName, m.MobileNumber, i.SmsBody
from MessageIn i inner join Mobiles m
on i.CustomerId = m.CustomerId
where i.EventType = 2
and i.Status = 1
end
[/code]

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-21 : 06:08:25

Do you want this to happen at the time when insertion happens everytime? then you might need a trigger to do this. If you're using SQL 2005. then you can also use OUTPUT clause to achieve this.

solutionon
Starting Member

12 Posts

Posted - 2008-07-21 : 09:19:35

Dear Mr. visakh16,

I am using SQL Server2000. I don't know which one is better to do this? SP or Trigger. Here if any new record comes to the table MessageIn then I need to run all the above issues.
Can you give me the trigger to do all these?
Please here if this trigger is possible with Bulk Insert query then it would be better too.

Regards
SolutionOn

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-21 : 09:55:03

quote:
Originally posted by solutionon

Dear Mr. visakh16,

I am using SQL Server2000. I don't know which one is better to do this? SP or Trigger. Here if any new record comes to the table MessageIn then I need to run all the above issues.
Can you give me the trigger to do all these?
Please here if this trigger is possible with Bulk Insert query then it would be better too.

Regards
SolutionOn

CREATE TRIGGER YourTriggerName 
ON MessageIn
AFTER INSERT
AS
BEGIN
INSERT INTO MessageOut (CustomerId,TagName,MobileNumber,SmsBody)
SELECT i.CustomerId,i.TagName,m.MobileNumber,i.SmsBody
FROM INSERTED i
INNER JOIN Mobiles m
ON m.CustomerId=i.CustomerId
AND m.FileId=i.FileId
WHERE i.EventType=2
AND i.Status=1
END

solutionon
Starting Member

12 Posts

Posted - 2008-07-21 : 12:09:17

Thanks Mr.visakh16,

Its working fine.
I have one more question. Is there any limitation for trigger? I mean if I need to fetch 10000000 and more mobile numbers from the table Mobiles then does this will make slow for insertion into MessageOut table or not?
If so can we use bulk insert query in this case?
And if more than 100 customers are inserting their records into MessageIn table then this will make any problem?
I created one EXE file to do this job but its looks very slow :(

Now if all these are not the correct method to do such a great job then could you give me a solution please for this?

and also
After the trigger fired I need to change the status field into 0 in MessageIn table. Status = 0 after all insertion process.
I hope you will continue the help! :)

Regards,
SolutionOn

solutionon
Starting Member

12 Posts

Posted - 2008-07-21 : 12:47:39

Dear All,

I appreciate anybody's suggestions and comments for such a case (above)

SolutionOn

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-07-21 : 12:51:34

Triggers should be avoided if at all possible for performance reasons. If you are going to insert 10 million rows, then this is going to be very slow. BULK INSERT will ignore the trigger.

Why don't you just change whatever process inserts the rows into MessageIn so that it does both inserts?

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

Subscribe to my blog

solutionon
Starting Member

12 Posts

Posted - 2008-07-21 : 13:10:35

Then whats the best method to do such a job from SQL2000 server?

SolutionOn

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-22 : 07:01:41

quote:
Originally posted by solutionon

Then whats the best method to do such a job from SQL2000 server?

SolutionOn

what frequency will you be doing inserts?

solutionon
Starting Member

12 Posts

Posted - 2008-07-22 : 07:15:26

No Idea,
How to check frequency please? Is there any comments to do it?

SolutionOn

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-07-22 : 12:30:06

You said this: "I mean if I need to fetch 10000000 and more mobile numbers from the table", which is why a trigger shouldn't be used. So is what you said true?

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