create report for hour to hour basis using SQL SP

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

General SQL Server Forums

New to SQL Server Programming

create report for hour to hour basis using SQL SP

Author

Topic

Mubarak
Starting Member

1 Post

Posted - 2012-09-19 : 04:20:12

Dear All,
Here I mentioned the sample data in a table, and I need to create a complete statistics about receiving of messages each hour to hour per day. And also here I mentioned my query for your additional reference, when I executing the SP, it returns me with error.

Msg 217, Level 16, State 1, Procedure Search_Satistics, Line 60
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).
Im trying to modified many times with ‘GetDate’ Function, But its again, the same errors shown. Please help me to resolved the issue soon as possible.
Inbox Date fatwa_id Code Mob_no Message status Answered date Answer
29 Aug 2012 13:16:01:377 60070 6878 971507410524 ????? ?? ????? .. ????? ????? ??? ???? ????? ????????? ?????????? 1 2012-08-29 13:16:05.377 ????? ???
29 Aug 2012 15:03:54:020 60071 6878 971502362336 ???? ??? ????? ???????? ???? ???? ?? ???? ??? ????? ???? ???? ???? ???? 1 2012-08-29 15:10:54.020 ???? ???? ???? ?? ????? ???? ??? ?????? ????? ?? ???.??????? ?? ??? ??? ????? ????????? ????? ?? ???? ???? ??????? ???? ?????? ?? ??????? ?? ????? ????? ????
QUERY :
ALTER PROCEDURE [dbo].[Search_Satistics]
@smsinbox_id INT = NULL,
@fatwa_id INT = NULL,
@short_code NVARCHAR(20) = NULL,
@mobile_no NUMERIC(18,0) = NULL,
@message_received NTEXT = NULL,
@received_date DATETIME = NULL,
@recvd_day DATETIME = NULL,
@recvd_mon DATETIME = NULL,
@recvd_yr DATETIME = NULL,
@answer_message NTEXT = NULL,
@answered_date DATETIME = NULL,
@StartDate DATETIME = NULL,
@EndDate DATETIME = NULL,
@FrmDate DATETIME = NULL,
@FrmDateGROUP DATETIME = NULL,
@fatwa_status INT = NULL, -- 1. fatwa_received, 2. fatwa_answered
@category NVARCHAR(20) = NULL,
@status INT = NULL,
@updated_by INT = NULL
AS
BEGIN
DECLARE @sSql NVARCHAR(MAX)
DECLARE @wCond NVARCHAR(MAX)
set @received_date = (select getdate())

SET @sSql = 'SELECT CONVERT(VARCHAR(10),answered_date,101) as Tdate, CONVERT(VARCHAR(2),answered_date,108) as Time,
COUNT(fatwa_id) AS TotalHour
FROM fatwa WITH (NOLOCK) WHERE answered_date= CONVERT(VARCHAR(10),GETDATE(),101)'

SET @wCond = ''
IF @fatwa_status IS NOT NULL
IF @wCond = ''
-- SET @wCond = ' WHERE fatwa_status=' + Convert(VARCHAR, @fatwa_status)
--ELSE
SET @wCond = @wCond + ' AND fatwa_status=' + Convert(VARCHAR, @fatwa_status)
IF @short_code IS NOT NULL
IF @wCond = ''
-- SET @wCond = ' WHERE src_addr=' + Convert(NVARCHAR, @src_addr)
--ELSE
SET @wCond = @wCond + ' AND a.short_code=' + Convert(NVARCHAR, @short_code)
IF @StartDate IS NOT NULL AND @EndDate IS NULL
IF @wCond = ''
SET @wCond = ' WHERE answered_date=''' + @StartDate + ''''
ELSE
SET @wCond = @wCond + ' AND answered_date=''' + @StartDate + ''''
IF @StartDate IS NOT NULL AND @EndDate IS NOT NULL
IF @wCond = ''
SET @wCond = ' WHERE answered_date>=''' + @StartDate + '''
AND answered_date<=''' + Convert(VARCHAR, DateAdd(day, 1, Convert(DATETIME, @EndDate))) + ''''
ELSE
SET @wCond = @wCond + ' AND answered_date>=''' + @StartDate + '''
AND answered_date<=''' + Convert(VARCHAR, DateAdd(day, 1, Convert(DATETIME, @EndDate))) + ''''

SET @wCond = 'GROUP BY CONVERT(VARCHAR(10),answered_date,101), CONVERT(VARCHAR(2),answered_date,108)'

EXEC(@sSql + @wCond + ' ORDER BY TDate ASC')
END

Waiting for your kind reply.
Thanks
Mubarak

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-19 : 09:19:32

The error message which says "Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32)." means that somehow you are recursively calling a stored procedure, trigger, or view. There is not sufficient information in what you have posted to be able to figure it out.

What I would suggest is the following:

Change the stored procedure (for testing purposes) by commenting out the EXEC line and just printing out the query.

....
	--EXEC (@sSql + @wCond + ' ORDER BY TDate ASC')
	SELECT @sSql + @wCond + ' ORDER BY TDate ASC'
...

See what the query is, and try to run that from an SSMS window. If doing that still gives you the same error, then look at the tables/views that are involved to see if there are any triggers on it, or views that call other views.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-19 : 11:44:03

it might be even that you've an INSERT...EXEC procedure and that procedure is inturn calling another procedure and so on

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources