April 3, 2009 at 6:18 pm
I've got a problem with a loop and a nested stored_proc.
Main stored_proc:-
...
INSERT #eMailList
SELECT Approver
FROM haApprovals
WHERE GUID=@theGUID
SET @Recipient = 10 + (SELECT MAX(eMailTo)
FROM #eMailList)
SET @Recipient = ISNULL(@Recipient,0) -- in case no records in #eMailList
WHILE @Recipient > 1
BEGIN
/*Debugging..*/ SELECT @Recipient AS Before
IF EXISTS (SELECT *
FROM #eMailList
WHERE eMailTo=@Recipient)
EXEC sp_MakeEmail @AbsUserNum, @Recipient, @Abbrev, @Prefix, @Suffix
SET @Recipient = (@Recipient-1)
/*Debugging..*/ SELECT @Recipient AS After
END
...
Nested stored_proc :-
sp_MakeEmail
- this basically writes a single record to Table Emails
- it has no RETURN statement
- and does not return a dataset or scalar
- contains no 'while' loops
-----------------------
My problem is that when #eMailList contains more than one value (eg: 160 & 197)
the loop counts down correctly from 207 till it reaches 197
then EXECs sp_MakeEmail once
and terminates the main stored_proc,
before reaching the /*Debugging..*/ 'After' statement
ie: it never reaches value 160
Comment out the EXEC statement and the loop counts down to 1.
What have I been missing here ?
Thanks for any help,
Mike
April 3, 2009 at 8:50 pm
It's probably something simple, but you will have to provide more information. Please read http://www.sqlservercentral.com/articles/Best+Practices/61537/ , and if you post sample data, you will probably get some help.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
April 5, 2009 at 7:43 pm
I have now solved my problem, but don't understand why.
I constructed a very simple stored_proc along the lines of :
sp_OUTER
AS
...
SET @Loop = 99
WHILE @Loop > 1
..BEGIN
....EXEC sp_INNER @Loop
....SET @Loop = @Loop - 1
..END
sp_INNER
@Param int
AS
...
SELECT 'Debug1'
INSERT Test1 (Col1)
VALUES (@Param)
SELECT 'Debug2'
The loop in sp_OUTER therefore only executed once.
The simple project worked perfectly.
My original project then worked correctly.
It seems that there was something wrong with the original 'Emails' table.
In all other respects the original 'Emails' table behaved properly.
I thank everyone for your interest.
Mike
/****** Object: Trigger dbo.haEmails_Insert_SendAll Script Date: 06/04/2009 02:12:02 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[haEmails_Insert_SendAll]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [dbo].[haEmails_Insert_SendAll]
GO
/****** Object: Table [dbo].[haEmails] Script Date: 06/04/2009 02:12:02 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[haEmails]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[haEmails]
GO
/****** Object: Table [dbo].[haEmailsDUFF] Script Date: 06/04/2009 02:12:02 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[haEmailsDUFF]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[haEmailsDUFF]
GO
/****** Object: Table [dbo].[haEmails] Script Date: 06/04/2009 02:12:02 ******/
CREATE TABLE [dbo].[haEmails] (
[emailRef] [int] IDENTITY (1, 1) NOT NULL ,
[Sent] [bit] NOT NULL ,
[FromUserNum] [int] NOT NULL ,
[ToUserNum] [int] NOT NULL ,
[MessageType] [int] NOT NULL ,
[AddedAt] [datetime] NOT NULL ,
[SentAt] [datetime] NULL ,
[MessageText] [varchar] (400) COLLATE Latin1_General_CI_AS NULL ,
[Version20090406] [bit] NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[haEmailsDUFF] Script Date: 06/04/2009 02:12:02 ******/
CREATE TABLE [dbo].[haEmailsDUFF] (
[emailRef] [int] IDENTITY (1, 1) NOT NULL ,
[Sent] [bit] NOT NULL ,
[FromUserNum] [int] NOT NULL ,
[ToUserNum] [int] NOT NULL ,
[MessageType] [int] NOT NULL ,
[AddedAt] [datetime] NOT NULL ,
[SentAt] [datetime] NULL ,
[MessageText] [varchar] (400) COLLATE Latin1_General_CI_AS NULL ,
[Version20090211] [bit] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[haEmails] WITH NOCHECK ADD
CONSTRAINT [PK_TestEmails] PRIMARY KEY CLUSTERED
(
[emailRef]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[haEmailsDUFF] WITH NOCHECK ADD
CONSTRAINT [PK_haEmails] PRIMARY KEY CLUSTERED
(
[emailRef]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[haEmails] ADD
CONSTRAINT [DF_TestEmails_Sent] DEFAULT (0) FOR [Sent],
CONSTRAINT [DF_TestEmails_MessType] DEFAULT ((-1)) FOR [MessageType],
CONSTRAINT [DF_TestEmails_AddedAt] DEFAULT (getdate()) FOR [AddedAt]
GO
ALTER TABLE [dbo].[haEmailsDUFF] ADD
CONSTRAINT [DF_haEmails_Sent] DEFAULT (0) FOR [Sent],
CONSTRAINT [DF_haEmails_MessageType] DEFAULT ((-1)) FOR [MessageType],
CONSTRAINT [DF_haEmails_AddedDate] DEFAULT (getdate()) FOR [AddedAt]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
/****** Object: Trigger dbo.haEmails_Insert_SendAll Script Date: 06/04/2009 02:12:02 ******/
CREATE TRIGGER [haEmails_Insert_SendAll] ON [dbo].[haEmails]
FOR INSERT
-- modified 11/2/2009 by MJC
-- actioned on Insert of an email with negative emailType
-- groups all unsent emails, by recipient
-- then concatenates messages
-- then sends one email to each recipient
-- then marks emails as sent
-- also housekeeps haEmails : removes all messages sent > 3 calendar months ago
AS
SET DATEFORMAT dmy
DECLARE @Error integer
DECLARE @User integer
DECLARE @HiUser integer
DECLARE @LoUser integer
DECLARE @Msg varchar(7000)
DECLARE @Ref int
DECLARE @Loop integer
DECLARE @Operation integer
-- initialise variable
SET @Error = 0
-- BEGIN TRAN
SET @Operation = (SELECT MAX(MessageType)
FROM INSERTED)
IF @Operation = -1 -- Flag to SendEmails
BEGIN
SET @User = (SELECT MAX(FromUserNum)
FROM INSERTED)
-- all emails not yet sent by @User only
SELECT emailRef as Ref, ToUserNum as ToUser, 0 AS Done
INTO #NotSent
FROM haEmails
WHERE MessageType >= 0 -- ignore Flagged record {@Operation = -1}
AND Sent = 0 -- NB: (AND SentAt = NULL) does NOT work here
AND FromUserNum = @User -- ignore emails from other Users
ORDER BY ToUser, Ref -- NB: emails are added in datetime order, Ref is Identity column
SET @Error = @@ERROR
IF @Error <> 0
GOTO Abort
-- select UserNum range
SET @HiUser = (SELECT MAX(ToUser)
FROM #NotSent)
SET @LoUser = (SELECT MIN(ToUser)
FROM #NotSent)
SET @Loop = @LoUser
-- loop through UserNum range
WHILE @Loop <= @HiUser
BEGIN
SET @Msg = ''
-- loop through all messages to one UserNum
WHILE EXISTS (SELECT *
FROM #NotSent
WHERE ToUser = @Loop
AND Done = 0)
BEGIN
SET @Ref = (SELECT Min(Ref)
FROM #NotSent
WHERE ToUser = @Loop
AND Done = 0) -- message not yet concatenated
IF @Msg = ''
BEGIN
SET @Msg = 'Message(s) from '
+ (SELECT UserName
FROM [pharmacyCentral].[dbo].UserLogons
WHERE UserNum = @User)
SET @Error = @@ERROR
IF @Error <> 0
GOTO Abort
END
SET @Msg = @Msg
+CHAR(13)
+(SELECT MessageText
FROM haEmails E
WHERE E.emailRef = @Ref)
UPDATE #NotSent
SET Done = 1
WHERE Ref = @Ref
SET @Error = @@ERROR
IF @Error <> 0
GOTO Abort
UPDATE haEmails
SET Sent = 1,
SentAt = getdate()
WHERE emailRef = @Ref
SET @Error = @@ERROR
IF @Error <> 0
GOTO Abort
END -- inner WHILE
-- SendEmail HERE ~ NB: if @Msg='' then no messages found for User:@Loop
IF @Msg <> ''
INSERT Temp (Ref, FromUser, ToUser, Added, Message)
VALUES (0, @User, @Loop, getdate(), @Msg)
SET @Error = @@ERROR
IF @Error <> 0
GOTO Abort
SET @Loop = @Loop+1
END
DELETE haEmails
WHERE MessageType < 0 -- the 'Flag' record
AND FromUserNum = @User -- User just dealt with
SET @Error = @@ERROR
IF @Error <> 0
GOTO Abort
DROP TABLE #NotSent -- else @@TRANCOUNT error on second loop
END -- (IF @Operation = -1)
-- HOUSE-KEEPING
DELETE haEmails
WHERE Sent = 1
AND DATEADD(mm,3,SentAt) < getdate() -- sent more than 3 calendar months ago
GOTO Success
Abort:
BEGIN
IF @@TRANCOUNT > 0
ROLLBACK TRAN
-- RETURN @Error -- NB: cannot RETURN in a trigger
END
Success:
BEGIN
IF @@TRANCOUNT > 0
COMMIT TRAN
-- RETURN 0 -- NB: cannot RETURN in a trigger
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply