April 18, 2012 at 10:05 pm
I am attempting to create a stored procedure that joins three tables and selects those shows which are on or greater than the date provided. The problem is that when I use a stored procedure to perform this simple operation there is some sort of recursion happening. I can run the same select query in a TVF and it works fine when selected against... I can run the query out right with no problems. The problem only occurs in the stored procedure whether using the query in the stored procedure or using the TVF in the stored procedure. There are no constraints, triggers or other constructs attached to any of the tables involved in the query or indeed outside of it as it is at the moment a Dev database. Included in the code below are the simple select query, the TVF creation code and the stored procedure creation code using the TVF. (Please note that the stored procedure was originally created with the simple select code as the body of the procedure.) Please advise me as to where I may have gone wrong. I am still fairly new to SQL Server.
*Simple Select*
SELECT S.ShowName, V.VenueName, S.StartingDate, SD.ShowDate, S.ClosingDate
FROM ShowInfo.Show AS S
JOIN ShowInfo.Venue AS V
ON V.VenueID = S.VenueID
JOIN ShowInfo.ShowDay AS SD
ON SD.ShowID = S.ShowID
WHERE SD.ShowDate >= '20120522';
*TVF*
CREATE FUNCTION fn_GetShowsFromDate
(@InputDate date = '19000101')--Date input to the function with default.
RETURNS TABLE
RETURN
SELECT S.ShowName, V.VenueName, S.StartingDate, SD.ShowDate, S.ClosingDate
FROM ShowInfo.Show AS S
JOIN ShowInfo.Venue AS V
ON V.VenueID = S.VenueID
JOIN ShowInfo.ShowDay AS SD
ON SD.ShowID = S.ShowID
WHERE SD.ShowDate >= @InputDate;--Tested against individual day data.
*Stored Procedure*
CREATE PROC [ShowInfo].[usp_GetShowsFromDate]
(@InputDate AS date = '19000101')
AS
IF @InputDate = '19000101'
BEGIN
SET @InputDate = CAST(GETDATE() AS date)
END
BEGIN
SELECT * FROM dbo.fn_GetShowsFromDate(@InputDate)
ORDER BY StartingDate;
END
If I need to provide more information please bear with me and request it. 🙂
HH
April 18, 2012 at 11:30 pm
This is a shot in the dark because I cannot see what you see, but sometimes when compiling procedures additional code slips into the procedure definition if your batches are not separated by a 'GO'.
For example if I ran all this code at once I would have created a proc that also called itself...
use tempdb
go
create proc dbo.test_proc
as
select * from sys.tables;
exec dbo.test_proc;
...so when the proc was actually executed it would appear as if it were working recursively.
What do you see when you run this?
EXEC sys.sp_helptext '[ShowInfo].[usp_GetShowsFromDate]';
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 18, 2012 at 11:32 pm
OPC sorta beat me to it but I was going to ask you to provide the exact text of the error message.
Also, some DDL and sample data along with expected results could help.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
April 19, 2012 at 1:26 am
I have recently read the forums etiquette and realiaze that I've left a few things out. For this over sight I appologize. Here is the DDL for the tables in question as generated by SQL Management Studio...
*Venue Table*
/****** Object: Table [ShowInfo].[Venue] Script Date: 04/19/2012 02:08:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [ShowInfo].[Venue](
[VenueID] [uniqueidentifier] NOT NULL,
[VenueName] [nvarchar](80) NOT NULL,
[StreetAddress] [nvarchar](100) NULL,
[City] [nvarchar](50) NOT NULL,
[UsState] [nvarchar](18) NULL,
[PostalCode] [nvarchar](50) NULL,
[TimeZoneID] [uniqueidentifier] NULL,
PRIMARY KEY CLUSTERED
(
[VenueID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [UF_VenueName_City] UNIQUE NONCLUSTERED
(
[VenueName] ASC,
[City] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
UNIQUE NONCLUSTERED
(
[VenueName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [ShowInfo].[Venue] WITH NOCHECK ADD CONSTRAINT [FK_Venue_To_TimeZone_On_TimeZoneID] FOREIGN KEY([TimeZoneID])
REFERENCES [ShowInfo].[TimeZone] ([TimeZoneID])
GO
ALTER TABLE [ShowInfo].[Venue] CHECK CONSTRAINT [FK_Venue_To_TimeZone_On_TimeZoneID]
GO
*Show Table
/****** Object: Table [ShowInfo].[Show] Script Date: 04/19/2012 02:07:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [ShowInfo].[Show](
[ShowID] [uniqueidentifier] NOT NULL,
[ShowName] [nvarchar](75) NOT NULL,
[StartingDate] [date] NOT NULL,
[ClosingDate] [date] NOT NULL,
[VenueID] [uniqueidentifier] NOT NULL,
PRIMARY KEY CLUSTERED
(
[ShowID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [ShowInfo].[Show] WITH NOCHECK ADD CONSTRAINT [FK_Show_Venue] FOREIGN KEY([VenueID])
REFERENCES [ShowInfo].[Venue] ([VenueID])
GO
ALTER TABLE [ShowInfo].[Show] CHECK CONSTRAINT [FK_Show_Venue]
GO
*ShowDay Table*
/****** Object: Table [ShowInfo].[ShowDay] Script Date: 04/19/2012 02:08:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [ShowInfo].[ShowDay](
[ShowDayID] [uniqueidentifier] NOT NULL,
[ShowDate] [date] NOT NULL,
[StartTime] [time](7) NULL,
[ClosingTime] [time](7) NULL,
[ShowID] [uniqueidentifier] NOT NULL,
PRIMARY KEY CLUSTERED
(
[ShowDayID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
UNIQUE NONCLUSTERED
(
[ShowDate] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [ShowInfo].[ShowDay] WITH NOCHECK ADD CONSTRAINT [FK_ShowDay_Show] FOREIGN KEY([ShowID])
REFERENCES [ShowInfo].[Show] ([ShowID])
GO
ALTER TABLE [ShowInfo].[ShowDay] CHECK CONSTRAINT [FK_ShowDay_Show]
GO
After seeing the DDL generated by SQL Server Mangement Studio I see that I do have some check constraints on the PK to FK relations but these are to be expected and I appoligize for not stating the obvious in this case. Also please forgive me for not providing the DDL in the first place. As to the Sample data... I'm still not sure after reading the post concerning etiquete as to how I go about providing this with ease. I will work on this problem tomorrow and apollogize once again for not having it at hand. I think I should be able to provide a CSV file from my studies but I will have to review this on the morrow. Thank you for your time and consideration up to this point. 🙂
HH
April 19, 2012 at 9:43 am
Try this:
ALTER PROC [ShowInfo].[usp_GetShowsFromDate]
(@InputDate AS date = '19000101')
AS
BEGIN
IF @InputDate = '19000101'
BEGIN
SET @InputDate = CAST(GETDATE() AS date)
END
BEGIN
SELECT * FROM dbo.fn_GetShowsFromDate(@InputDate)
ORDER BY StartingDate;
END
END
Jared
CE - Microsoft
April 19, 2012 at 11:29 am
Thank you Jared! That fixed it. I see now that I did not properly nest my procedure.
Thank you all for your time and consideration. 🙂
HH
April 19, 2012 at 11:37 am
Glad to help. I ALWAYS wrap my procedures in BEGIN and END for clarity as well as to avoid issues like this.
Jared
CE - Microsoft
April 19, 2012 at 11:56 am
It is true that BEGIN / END adds clarity, but it does not release us from separating our batches with GO, i.e. this still produces an incorrect result:
use tempdb
go
create proc dbo.test_proc
as
begin
select * from sys.tables;
end
exec dbo.test_proc;
GO
EXEC sys.sp_helptext
@objname = N'dbo.test_proc';
GO
Just be sure to mind your batches, and when in doubt use GO.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 19, 2012 at 12:04 pm
opc.three (4/19/2012)
It is true that BEGIN / END adds clarity, but it does not release us from separating our batches with GO, i.e. this still produces an incorrect result:
use tempdbgo
create proc dbo.test_proc
as
begin
select * from sys.tables;
end
exec dbo.test_proc;
GO
EXEC sys.sp_helptext
@objname = N'dbo.test_proc';
GO
Just be sure to mind your batches, and when in doubt use GO.
+1 I, however, seldom use GO and certainly never in the same window that I am creating a stored proc in.
Jared
CE - Microsoft
April 19, 2012 at 8:09 pm
Ah yes... In a large part of the coding I've done for SQL Server I have used GO almost obsessive compulsively. 😀 I don't know what came over me this time. Upon further review of my code that would have prevented the recursive problem that I was having. On the other end of the spectrum I did something else that I have rarely done... I included my test statement in the SP code page. This combined with my lack of GO at the appropriate point caused the recursion error. In the end it turns out I just plain got sloppy with my code. (Note to self: Don't code when exhausted.)
Thanks again!
HH
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply