Stored Procedure not working properly

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

Other SQL Server 2008 Topics

Stored Procedure not working properly

Author

Topic

RCDAWebmaster
Starting Member

4 Posts

Posted - 2013-10-15 : 11:10:23

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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-15 : 11:34:57

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-16 : 02:39:17

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

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-16 : 08:31:26

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.

RCDAWebmaster
Starting Member

4 Posts

Posted - 2013-10-17 : 10:50:33

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...

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-17 : 11:39:46

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.

RCDAWebmaster
Starting Member

4 Posts

Posted - 2013-10-18 : 10:46:16

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.

RCDAWebmaster
Starting Member

4 Posts

Posted - 2013-10-18 : 10:48:14

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources