Newbie: stored procedure stops and exits prematurely

Well thanks to a lot of reading and experimentation, as a newbie, I've made
it to stored procedure writing...But my inexperience is showing, I'm
afraid...Can anyone point out what I should be doing here? Thank you...
I'm pulling several steps together into one stored procedure, but the
procedure stops and exits at the first step in order to return a message box
that says "The stored procedure executed successfully but did not return
records."
How can I tell the SP to continue to execute? Or, how can I avoid getting
the message?
Synopsis of the SP:
The purpose is to allow the user to select individuals to receive an
emailing. (The SP is called from the form.) Before presenting the list of
contacts on the form, I want to make sure that any new contacts in the
Contacts table get added to the SelectForEmail table . (The Contacts table
is part of a replicated database, to which I cannot add a trigger.)
Step 1: Behind the scenes, add any new contacts to the table SelectForEmail.
Step 2: Present the form that allows the user to check off the individuals
that should receive the emailing.
Here's the code for the first step:
/* Get all contacts from the contacts table */
INSERT INTO dbo.SelectForEmail (Contact_ID)
SELECT dbo.Contacts.Contact_ID FROM dbo.Contacts
LEFT OUTER JOIN dbo.SelectForEmail
ON dbo.Contacts.Contact_ID = dbo.SelectForEmail.Contact_ID
WHERE (dbo.SelectForEmail.Contact_ID IS NULL)> How can I tell the SP to continue to execute? Or, how can I avoid getting
> the message?
Perhaps the Procedure already executed but you didnt issued a command to
display any data ?
Create procedure DisplaySomeData
AS
INSERT INTO sometable values (1)
GO
--> Wont display any data
Create procedure DisplaySomeData
AS
INSERT INTO sometable values (1)
Select * from Sometable where ID = 1
GO
--> Will display data
It hard to tell what you have to do without having some of the DDL of your
procedure, please post some DDL and thatll try to help us to help you.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Kaycee Heyer" <heyerk@.uncw.edu> schrieb im Newsbeitrag
news:uISPrHKVFHA.3252@.TK2MSFTNGP10.phx.gbl...
> Well thanks to a lot of reading and experimentation, as a newbie, I've
> made
> it to stored procedure writing...But my inexperience is showing, I'm
> afraid...Can anyone point out what I should be doing here? Thank you...
> I'm pulling several steps together into one stored procedure, but the
> procedure stops and exits at the first step in order to return a message
> box
> that says "The stored procedure executed successfully but did not return
> records."
> How can I tell the SP to continue to execute? Or, how can I avoid getting
> the message?
> Synopsis of the SP:
> The purpose is to allow the user to select individuals to receive an
> emailing. (The SP is called from the form.) Before presenting the list of
> contacts on the form, I want to make sure that any new contacts in the
> Contacts table get added to the SelectForEmail table . (The Contacts table
> is part of a replicated database, to which I cannot add a trigger.)
> Step 1: Behind the scenes, add any new contacts to the table
> SelectForEmail.
> Step 2: Present the form that allows the user to check off the individuals
> that should receive the emailing.
> Here's the code for the first step:
>
> /* Get all contacts from the contacts table */
> INSERT INTO dbo.SelectForEmail (Contact_ID)
> SELECT dbo.Contacts.Contact_ID FROM dbo.Contacts
> LEFT OUTER JOIN dbo.SelectForEmail
> ON dbo.Contacts.Contact_ID = dbo.SelectForEmail.Contact_ID
> WHERE (dbo.SelectForEmail.Contact_ID IS NULL)
>|||Thanks.
I found that if I do the SELECT first, and then check @.@.ROWCOUNT <> 0
before doing the INSERT, that I can avoid getting the message.
Here's the entire new procedure:
ALTER PROCEDURE SEGM_EmailSelect4List
AS
/* Add new contacts from the contacts table */
/* Check first to make sure that new contacts exist */
SELECT dbo.Contacts.Contact_ID
FROM dbo.Contacts LEFT OUTER JOIN
dbo.SEGM_SelectForEmail ON
dbo.Contacts.Contact_ID = dbo.SEGM_SelectForEmail.Contact_ID
WHERE (dbo.SEGM_SelectForEmail.Contact_ID IS NULL)
IF @.@.ROWCOUNT <> 0
BEGIN
/* Add new contacts from the contacts table */
INSERT INTO dbo.SEGM_SelectForEmail
(Contact_ID)
SELECT dbo.Contacts.Contact_ID
FROM dbo.Contacts LEFT OUTER JOIN
dbo.SEGM_SelectForEmail ON
dbo.Contacts.Contact_ID = dbo.SEGM_SelectForEmail.Contact_ID
WHERE (dbo.SEGM_SelectForEmail.Contact_ID IS NULL)
END
/* Flag any contacts who have been investigators (that is, they exist in
the Investigators table) */
UPDATE dbo.SEGM_SelectForEmail
SET SEGM_Investigator = 1, SelectForEmail = 1
WHERE (Contact_ID IN
(SELECT dbo.Investigators.Contact_ID
FROM dbo.Investigators))
/* Return all contacts to the form that called us */
SELECT TOP 100 PERCENT dbo.Contacts.Last_Name,
dbo.Contacts.First_Name, dbo.Contacts.E_mail,
dbo.SEGM_SelectForEmail.SEGM_Investigator,
dbo.SEGM_SelectForEmail.SelectForEmail,
dbo.Affiliations.Affiliation
FROM dbo.Contacts
INNER JOIN dbo.SEGM_SelectForEmail ON
dbo.Contacts.Contact_ID = dbo.SEGM_SelectForEmail.Contact_ID
INNER JOIN dbo.Organizations ON dbo.Contacts.Organization_ID =
dbo.Organizations.Organization_ID
INNER JOIN dbo.Affiliations ON dbo.Organizations.Affiliation_ID =
dbo.Affiliations.Affiliation_ID
WHERE 1=1
ORDER BY dbo.Contacts.Last_Name, dbo.Contacts.First_Name
But now I have a new problem. The last select returns 0 rows to the form
opened in datasheet view. When I remove all the code except the last
select, it returns many rows.
What's going on?
*** Sent via Developersdex http://www.examnotes.net ***