SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Other SQL Server 2008 Topics
 Stored Procedure not working properly
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

RCDAWebmaster
Starting Member

USA
4 Posts

Posted - 10/15/2013 :  11:10:23  Show Profile  Reply with Quote
I'm creating a stored procedure that will query a table to see if there is a record present that meats certain criteria.

If returend records count = 0
insert a record so that one matches the criteria
query the newly created record and return it.
else
return the query'd record

I'm running into a small problem where the newly created record is not being returned by the query and I have to call the procedure twice to get the record returned.

The stored procedure is being called from a dot net webpage.

Mike



USE [Notitiae]
GO
/****** Object:  StoredProcedure [dbo].[get_Sacraments_page_6]    Script Date: 10/15/2013 11:03:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[get_Sacraments_page_6] (
@par_num int
)

AS
-- Section 1 - Define and Initialize local variables
DECLARE @Count Int
SELECT @Count = 0


BEGIN

-- Lookup volunteer data using @Par_num 
SELECT tblSacraments.Parish_ID, tblSacraments.Year, tblSacraments.InfantBap, tblSacraments.ChildBap, tblSacraments.InfChildBap, tblSacraments.YoungAdultBap, tblSacraments.AdultBap, tblSacraments.TotalBaptisms, tblSacraments.RCIA, tblSacraments.Candidates, tblSacraments.Catechumens, tblSacraments.Interview, tblSacraments.InterviewAndInstruction, tblSacraments.ParishProgram1, tblSacraments.ParishProgram2, tblSacraments.ComboProgram, tblSacraments.TwoCathMarriage, tblSacraments.CathChrisMarriage, tblSacraments.CathNonMarriage, tblSacraments.CathOtherReligion, tblSacraments.TotalNumMarriages, tblSacraments.Dispensations, tblSacraments.Under21Marriage
FROM tblSacraments
WHERE (((tblSacraments.Parish_ID)=@par_num) AND ((tblSacraments.Year)=Year(GETDATE())-1));



if (@@ROWCOUNT < 1)
	begin
		-- add a record
		Insert into notitiae.dbo.tblSacraments (Parish_ID, [Year]) values (@par_num, Year(GETDATE())-1)
	
	
SELECT tblSacraments.Parish_ID, tblSacraments.Year, tblSacraments.InfantBap, tblSacraments.ChildBap, tblSacraments.InfChildBap, tblSacraments.YoungAdultBap, tblSacraments.AdultBap, tblSacraments.TotalBaptisms, tblSacraments.RCIA, tblSacraments.Candidates, tblSacraments.Catechumens, tblSacraments.Interview, tblSacraments.InterviewAndInstruction, tblSacraments.ParishProgram1, tblSacraments.ParishProgram2, tblSacraments.ComboProgram, tblSacraments.TwoCathMarriage, tblSacraments.CathChrisMarriage, tblSacraments.CathNonMarriage, tblSacraments.CathOtherReligion, tblSacraments.TotalNumMarriages, tblSacraments.Dispensations, tblSacraments.Under21Marriage
FROM tblSacraments
WHERE (((tblSacraments.Parish_ID)=@par_num) AND ((tblSacraments.Year)=Year(GETDATE())-1));

END
	
END

James K
Flowing Fount of Yak Knowledge

3707 Posts

Posted - 10/15/2013 :  11:34:57  Show Profile  Reply with Quote
Change your stored proc to this:
USE [Notitiae]
GO
/****** Object:  StoredProcedure [dbo].[get_Sacraments_page_6]    Script Date: 10/15/2013 11:03:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[get_Sacraments_page_6] (
@par_num int
)

AS
-- Section 1 - Define and Initialize local variables
DECLARE @Count Int
SELECT @Count = 0


BEGIN

IF NOT EXISTS (
	SELECT * FROM 
	tblSacraments
	WHERE (((tblSacraments.Parish_ID)=@par_num) AND ((tblSacraments.Year)=Year(GETDATE())-1))
)
BEGIN
	-- add a record
	Insert into notitiae.dbo.tblSacraments (Parish_ID, [Year]) values (@par_num, Year(GETDATE())-1);
END
	
	
SELECT tblSacraments.Parish_ID, tblSacraments.Year, tblSacraments.InfantBap, tblSacraments.ChildBap, tblSacraments.InfChildBap, tblSacraments.YoungAdultBap, tblSacraments.AdultBap, tblSacraments.TotalBaptisms, tblSacraments.RCIA, tblSacraments.Candidates, tblSacraments.Catechumens, tblSacraments.Interview, tblSacraments.InterviewAndInstruction, tblSacraments.ParishProgram1, tblSacraments.ParishProgram2, tblSacraments.ComboProgram, tblSacraments.TwoCathMarriage, tblSacraments.CathChrisMarriage, tblSacraments.CathNonMarriage, tblSacraments.CathOtherReligion, tblSacraments.TotalNumMarriages, tblSacraments.Dispensations, tblSacraments.Under21Marriage
FROM tblSacraments
WHERE (((tblSacraments.Parish_ID)=@par_num) AND ((tblSacraments.Year)=Year(GETDATE())-1));

	
END
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/16/2013 :  02:39:17  Show Profile  Reply with Quote
Why not use OUTPUT clause?

USE [Notitiae]
GO
/****** Object:  StoredProcedure [dbo].[get_Sacraments_page_6]    Script Date: 10/15/2013 11:03:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[get_Sacraments_page_6] (
@par_num int
)

AS
-- Section 1 - Define and Initialize local variables
DECLARE @Count Int
SELECT @Count = 0


BEGIN

IF NOT EXISTS (
	SELECT * FROM 
	tblSacraments
	WHERE (((tblSacraments.Parish_ID)=@par_num) AND ((tblSacraments.Year)=Year(GETDATE())-1))
)
BEGIN
	-- add a record
	Insert into notitiae.dbo.tblSacraments (Parish_ID, [Year]) 
        OUTPUT INSERTED.Parish_ID, INSERTED.Year, INSERTED.InfantBap, INSERTED.ChildBap, INSERTED.InfChildBap, INSERTED.YoungAdultBap, INSERTED.AdultBap, INSERTED.TotalBaptisms, INSERTED.RCIA, INSERTED.Candidates, INSERTED.Catechumens, INSERTED.Interview, INSERTED.InterviewAndInstruction, INSERTED.ParishProgram1, INSERTED.ParishProgram2, INSERTED.ComboProgram, INSERTED.TwoCathMarriage, INSERTED.CathChrisMarriage, INSERTED.CathNonMarriage, INSERTED.CathOtherReligion, INSERTED.TotalNumMarriages, INSERTED.Dispensations, INSERTED.Under21Marriage
        values (@par_num, Year(GETDATE())-1);
END
	
END


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3707 Posts

Posted - 10/16/2013 :  08:31:26  Show Profile  Reply with Quote
quote:
Originally posted by visakh16

Why not use OUTPUT clause?

USE [Notitiae]
GO
/****** Object:  StoredProcedure [dbo].[get_Sacraments_page_6]    Script Date: 10/15/2013 11:03:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[get_Sacraments_page_6] (
@par_num int
)

AS
-- Section 1 - Define and Initialize local variables
DECLARE @Count Int
SELECT @Count = 0


BEGIN

IF NOT EXISTS (
	SELECT * FROM 
	tblSacraments
	WHERE (((tblSacraments.Parish_ID)=@par_num) AND ((tblSacraments.Year)=Year(GETDATE())-1))
)
BEGIN
	-- add a record
	Insert into notitiae.dbo.tblSacraments (Parish_ID, [Year]) 
        OUTPUT INSERTED.Parish_ID, INSERTED.Year, INSERTED.InfantBap, INSERTED.ChildBap, INSERTED.InfChildBap, INSERTED.YoungAdultBap, INSERTED.AdultBap, INSERTED.TotalBaptisms, INSERTED.RCIA, INSERTED.Candidates, INSERTED.Catechumens, INSERTED.Interview, INSERTED.InterviewAndInstruction, INSERTED.ParishProgram1, INSERTED.ParishProgram2, INSERTED.ComboProgram, INSERTED.TwoCathMarriage, INSERTED.CathChrisMarriage, INSERTED.CathNonMarriage, INSERTED.CathOtherReligion, INSERTED.TotalNumMarriages, INSERTED.Dispensations, INSERTED.Under21Marriage
        values (@par_num, Year(GETDATE())-1);
END
	
END


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs


OP needs to get a record back even if a new row is not inserted.
Go to Top of Page

RCDAWebmaster
Starting Member

USA
4 Posts

Posted - 10/17/2013 :  10:50:33  Show Profile  Reply with Quote
Thanks visakh16 for giving me the help I needed. Now I get the record I inserted returned and do not have to run the procedure twice to get the inserted row returned. I hate wasting processing power...
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3707 Posts

Posted - 10/17/2013 :  11:39:46  Show Profile  Reply with Quote
quote:
Originally posted by RCDAWebmaster

Thanks visakh16 for giving me the help I needed. Now I get the record I inserted returned and do not have to run the procedure twice to get the inserted row returned. I hate wasting processing power...

If you were to run the stored procedure that Visakh posted with a @par_num that corresponds to an existing record, the stored procedure will return no rows at all.

From your original posting, it seemed like that was not your requirement.
Go to Top of Page

RCDAWebmaster
Starting Member

USA
4 Posts

Posted - 10/18/2013 :  10:46:16  Show Profile  Reply with Quote
I finally figured out that it was the way I grouped the insert and lookup in the same begin/end block that caused the insert to not be committed before I tried to search for it.

I now check to see if a row exists and if not, insert it. I Then query to get the record.

I think I was getting confused by the syntax of Begin/end. To understand it better, I wrote the function using curly brackets like javascript and then replaced them with Begin/End. Now I am able to understand things better and have a foolproof plan to assist me when I get stumped.

Edited by - RCDAWebmaster on 10/18/2013 10:53:20
Go to Top of Page

RCDAWebmaster
Starting Member

USA
4 Posts

Posted - 10/18/2013 :  10:48:14  Show Profile  Reply with Quote
BTW, can you tell me how to turn on word wrap so I don't have to scroll far to the right to see full messages
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000