August 27, 2004 at 12:02 am
i have a pecular problem with stored procedure.
when i run a stored procedure in query analyser by typing exec usp_art_stat the procedure runs but does not insert values into the tables at the end.
if i run the actual code in the query analyser it works just fine.
can anyone suggest a solution?
stored procedure code given below:
CREATE PROCEDURE dbo.USP_ART_STAT
AS
BEGIN
DECLARE @JRNL_COUNT INT
DECLARE @JDT INT, @JDM INT, @JDY INT, @jdc INT
DECLARE @JTT INT, @JTM INT, @JTY INT, @JTC INT
DECLARE @CURR_DATE AS DATETIME
SET @CURR_DATE = GETDATE() - 1
--Journal : Directory : Today.
select @JDT = count(journal_id) from tb_m_journals where media_id not in
(1,3,5) and active_flag like 'a' and convert(char(17),created_date,111) = CONVERT(CHAR(10),@CURR_DATE,111)
--Journal : Directory : Month
select @JDM = count(journal_id) from tb_m_journals where media_id not in
(1,3,5) and active_flag like 'a' and Month(created_date) = Month(CONVERT(CHAR(10),@CURR_DATE,111))
and Year(created_date) = Year(CONVERT(CHAR(10),@CURR_DATE,111))
--Journal : Directory : Year
select @JDY = count(journal_id) from tb_m_journals where media_id not in
(1,3,5) and active_flag like 'a' and Year(created_date) = Year(CONVERT(CHAR(10),@CURR_DATE,111))
--Journals : Directory : Cumulative
select @jdc = count(journal_id) from tb_m_journals where media_id not in
(1,3,5) and active_flag like 'a'
--Journals : TOC : Today.
select @JTT = count(journal_id) from tb_m_journals where media_id not in (1,3,5) and journal_id in
(select distinct(journal_id) from tb_m_issues where
active_flag like 'a' and convert(char(17),created_date,111) = CONVERT(CHAR(10),@CURR_DATE,111)) and convert(char(17),created_date,111) = CONVERT(CHAR(10),@CURR_DATE,111)
--Journals : TOC : Monthly
select @JTM = count(journal_id) from tb_m_journals where media_id not in (1,3,5) and journal_id in
(select distinct(journal_id) from tb_m_issues where
active_flag like 'a' and Month(created_date) = Month(CONVERT(CHAR(10),@CURR_DATE,111)) and Year(created_date) = Year(CONVERT(CHAR(10),@CURR_DATE,111)))
and Month(created_date) = Month(CONVERT(CHAR(10),@CURR_DATE,111)) and Year(created_date) = Year(CONVERT(CHAR(10),@CURR_DATE,111))
--Journals : TOC : Year
select @JTY = count(journal_id) from tb_m_journals where media_id not in (1,3,5) and journal_id in
(select distinct(journal_id) from tb_m_issues where
active_flag like 'a' and Year(created_date) = Year(CONVERT(CHAR(10),@CURR_DATE,111))) and Year(created_date) = Year(CONVERT(CHAR(10),@CURR_DATE,111))
--Journals : TOC : Cumulative
select @JTC = count(journal_id) from tb_m_journals where media_id not in (1,3,5) and journal_id in
((select distinct(journal_id) from tb_m_issues where
active_flag like 'a' ))
INSERT INTO TB_DAILY_STATISTICS (STAT_ID, T_INSERTED, M_INSERTED, Y_INSERTED, UPDATED, DELETED, GEN_DATE, CUMULATIVE)
VALUES (1, @JDT, @JDM, @JDY, 0, 0, CONVERT(CHAR(10),@CURR_DATE,111), @jdc)
INSERT INTO TB_DAILY_STATISTICS (STAT_ID, T_INSERTED, M_INSERTED, Y_INSERTED, UPDATED, DELETED, GEN_DATE, CUMULATIVE)
VALUES (2, @JTT, @JTM, @JTY, 0, 0, CONVERT(CHAR(10),@CURR_DATE,111), @JTC)
END
GO
August 27, 2004 at 12:35 am
Two things jump out at me as possible problems:
1. you have LIKE operators that are incomplete
active_flag like 'a'
has no wild-card or regular expression defined. if you are trying to get all rows where the active_flag starts with 'a' you need to use the following:
active_flag like 'a%'
2. You have a reserved word used as column names in your table. DELETED and INSERTED are keywords used in triggers to reference the original (Deleted) and new (inserted) version of a row in a transaction. Running as a script, the optimizer may be ignoring this, but running as a compiled query plan, it might be being confused by the reference to the DELETED keyword.
I would make sure that you never use reserve words as table or column names, but in the meantime, change your query to enclose the column names in square brackets, so that they are explicitly tagged as column names: [DELETED]
From a performance perspective, your queries are going to suffer from two problems:
1. The use of "IN (sub select)" instead of a "where exists" clause
Rewrite as a correlated sub-query:
@JTC = count(journal_id)
tb_m_journals J
media_id not in (1,3,5)
exists
2. The use of functions on the left side of a condition:
and Year(created_date) = Year(CONVERT(CHAR(10),@CURR_DATE,111))
You would be better served by creating two work variables for the beginning and ending date of the range you are interested in and populating them with the first day of the year and the first day of the next year, then using them to limit the created_date column directly:
@w_startdate datetime
,@w_enddate datetime
@w_startdate = cast('01/01/' + cast(year(@CURR_DATE)as varchar) as datetime)
@w_enddate = cast('01/01/' + cast((year(@CURR_DATE) + 1) as varchar) as datetime)
(created_date >= @w_startdate and created_date < @w_enddate)
This allows for an index on the created_date column to be used in the query, otherwise your code will cause a table-scan every time it runs.
Kindest Regards,
Clayton
August 27, 2004 at 2:38 am
thanks for your prompt reply.
i made the changes you suggested enclosing reserved keywords in square brackets. it seems to be working fine now.
thanks also for the suggestions to improve the stored procedure. i will implement these in all the stored procedures.
ravi
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply