Store Procedure Help - 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 2008 Forums

Transact-SQL (2008)

Store Procedure Help

Author

Topic

tooba
Posting Yak Master

224 Posts

Posted - 2012-09-18 : 11:51:34

Hi Guys,

I need help, Here is Scenario,

If some once call through Phone and place an order through Phone or left a message that message will store in our database, due to some reason
(I couldn't find out what reason) if message is not clear or message is to big or don't follow the logic that message didn't save in our database.
Some time if we receive 100 Message all messages insert on the table or sometime 2 out of 100 not save in table.

Here is my first table where Message is saved = MessagethroughPh

and everyday i ran Job and if the message is in this table its mean message successfully stored, Table Name = MessageafterPh

I am using this query to find out how many messages are not saved..

select * from Messagethroughph
where AppDate = '9-17-2012'
and PhID not in (Select PhID from MessageafterPh)

If i get any result (i.e 2,3 or 4 PhID) i want to insert that PhID to MessageafterPh)

I was thinking to create Store Procedure. My question for you guys is, how i can start this, i have logic to insert missing PhID to table.
But how i can start this logic in S.P? to check how many PhID is missing and if there is more then one how i can loop through, Guide Me.

Create Proc PhIDInsert
My Variables

as

Thanks for Help...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-18 : 13:07:19

[code]
Create Proc MissingPhIDInsert
@AppDate date
AS
INSERT INTO MessageafterPh (PhID,col1,col2,...)
select PhID,col1,Col2,...
from Messagethroughph
where AppDate = @AppDate
and PhID not in (Select PhID from MessageafterPh)
GO
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

tooba
Posting Yak Master

224 Posts

Posted - 2012-09-18 : 13:31:21

Thanks for your reply, quick question how this gonna work if i have to insert more than 1, Mean if i have 4 or 5 ids are missing then?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-18 : 13:42:31

it will work for any number of IDs missing from table so far as they all fall on same AppDate value passed

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

tooba
Posting Yak Master

224 Posts

Posted - 2012-09-18 : 15:40:41

Thanks for your help....

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources