stored procedures - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

stored procedures

Author

Topic

mdhingra01
Posting Yak Master

179 Posts

Posted - 2005-05-25 : 12:26:33

I created a stored procedure that when I try to execute using the EXEC command it never returns, however when I cut and paste the contents of the stored proc in Query analyzer it completes. Does anyone know what might be causing this.

Thanks.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-05-25 : 12:53:12

No, post the code for the stored procedure as well as the code you are using to call it.

Be One with the Optimizer
TG

mdhingra01
Posting Yak Master

179 Posts

Posted - 2005-05-25 : 15:39:41

exec sp_r7220_ocrc_addr_invl_cls which does not work

CREATE PROCEDURE dbo.sp_r7220_ocrc_addr_invl_cls AS

/*
DATE: FEBRUARY, 2005
DESCRIPTION: THIS IS TO REFRESH THE OCRC_ADDR_INVL_CLS TABLE
MODIFICATIONS:
DATE DEVELOPER DESCRIPTION
YYYY-MMM-DD
------------ -------------- ------------------------------------------------------
2005-MAY-16 - INITIAL VERSION

This sp will refresh OCRC_ADDR_INVL_CLS. It will do so by generating a new record for
each code contained in the Classification column. Up to 5 codes can be in the
Classification column. Each code is 3 char long and is enclosed in ';'. So the format
is " ;ABC; " for one code, " ;ABC;DEF; " for two codes and so forth up to five codes.
Because the lenght is always constant, we can use that in our calculation to figure
the number of codes that are in the Classification column. This number will also be
used in the inner loop as the counter.

NOTE: Must be run AFTER sp_r4200_ocrc_addr

*/

SET NOCOUNT ON
SET QUOTED_IDENTIFIER OFF
SET IMPLICIT_TRANSACTIONS OFF
SET ANSI_WARNINGS OFF

DECLARE @rec_id VARCHAR(25) -- for use by the cursor
DECLARE @ClsString VARCHAR(50) -- for use by the cursor
DECLARE @Cls_Len INT -- for use by the cursor
DECLARE @Mod_dt DATETIME -- for use by the cursor
DECLARE @Num_of_cd INT
DECLARE @Cd CHAR(3)
DECLARE @Step_Name VARCHAR(35)
DECLARE @Last_Good_Run DATETIME

SELECT @Last_Good_Run = MAX(Last_Good_Rfrsh_Dt)
FROM DQOR_test.DBO.DQOR_PROCESS
WHERE Last_Good_Rfrsh_Dt IS NOT NULL

/* INSERT NEW RECORDS FROM SOURCE TABLE NICHERMS_TEST.DBO.TBL_GOCCIVPA
THAT ARE NOT IN DQOR_test.DBO.OCRC_ADDR_INVL_CLS. THE WHERE CLAUSE WILL ENSURE
THAT ONLY NEW RECORDS ARE SELECTED.
*/

SELECT @Step_Name = 'SP_R7220_OCRC_ADDR_INVL_CLS - INSERTING'
UPDATE DQOR_test.DBO.DQOR_PROCESS
SET LAST_PROCESS = 'STARTED-' + @Step_Name + ' AT ' + CONVERT(CHAR(8),CURRENT_TIMESTAMP,108)
WHERE ACTIVE_RECORD = 1

-- select only the records for which we do not have a match in OCRC_ADDR_INVL_CLS and
-- TBL_GOCCIVPA. T2 will contain only new records
CREATE TABLE #t2 --
(t2id varchar(25), t2CD char(25), t2MODDT datetime)
INSERT INTO #t2
(t2id,
t2CD,
t2MODDT)
SELECT
NOIPA.ID,
NOIPA.CLASSIFICATION,
NOIPA.MODTIME
FROM NicheRMS_test.dbo.TBL_GOCCIVPA NOIPA INNER JOIN
dbo.OCRC_ADDR op ON NOIPA.Id = op.OCRC_ADDR_ID LEFT OUTER JOIN
dbo.OCRC_ADDR_INVL_CLS ON op.OCRC_ADDR_ID = dbo.OCRC_ADDR_INVL_CLS.OCRC_ADDR_ID
WHERE dbo.OCRC_ADDR_INVL_CLS.OCRC_ADDR_ID IS NULL

DECLARE all_recs CURSOR READ_ONLY -- Create a cursor with T2
FOR -- so we can process each new record
SELECT
t2id,
t2CD,
t2MODDT
FROM #t2

OPEN all_recs
FETCH all_recs INTO @rec_id, @ClsString, @Mod_dt -- load first record

CREATE TABLE #t -- this table will hold all the new records
(tID varchar(25), tCD char(3), tMODDT datetime) -- generated based on their number of codes

WHILE @@FETCH_STATUS = 0 -- loop for all records
BEGIN -- figure out how many codes are in the classification
SET @Cls_Len = LEN(@ClsString) -- by its lenght
SET @Num_of_cd = dbo.Nbr_Cls_Cd (@Cls_Len) -- UDF call. returns the number of codes
WHILE @Num_of_cd <> 0 -- loop for classification
BEGIN
SET @Cd = dbo.Cls_Cd (@Num_of_cd, @ClsString) -- UDF call: return one single code from the string
INSERT INTO #t (tid, tcd, tmoddt) -- store this new record
VALUES( @rec_id, @Cd, @Mod_dt )
SET @Num_of_cd = @Num_of_cd - 1
END
FETCH all_recs INTO @rec_id, @ClsString, @Mod_dt -- load next record
END

CLOSE all_recs
DEALLOCATE all_recs

INSERT INTO DBO.OCRC_ADDR_INVL_CLS -- insert all the new generated records in OCRC_ADDR_INVL_CLS
( OCRC_ADDR_ID,
OCRC_INVL_CLS_CD,
MOD_DT
)
SELECT
tID,
tCD, tMODDT
FROM #t

DROP TABLE #t
DROP TABLE #t2

/* DELETED RECORDS: UPDATE THE RECORD_STATUS COLUMN TO 'D' FOR ANY RECORD FOUND IN
DQOR_test.DBO.OCRC_ADDR THAT ARE NO LONGER IN THE SOURCE
NICHERMS_TEST.DBO.TBL_GOCCIVPA.

PURGED RECORDS: SPECS NOT DEFINE YET.
*/

SELECT @Step_Name = 'SP_R7220_OCRC_ADDR_INVL_CLS - DELETING'
UPDATE DQOR_test.DBO.DQOR_PROCESS
SET LAST_PROCESS = 'STARTED-' + @Step_Name + ' AT ' + CONVERT(CHAR(8),CURRENT_TIMESTAMP,108)
WHERE ACTIVE_RECORD = 1

UPDATE DQOR_test.DBO.OCRC_ADDR_INVL_CLS
SET RECORD_STATUS= 'D'
FROM DQOR_test.DBO.OCRC_ADDR_INVL_CLS
WHERE RECORD_STATUS <> 'D' AND
OCRC_ADDR_ID NOT IN
(SELECT ID
FROM NICHERMS_TEST.DBO.TBL_GOCCIVPA)

/* UPDATE ALL COLUMNS IN DQOR_test.DBO.OCRC_ADDR_INVL_CLS FOR ANY RECORD
FOUND IN NICHERMS_TEST.DBO.TBL_GOCCIVPA THAT THE MODTIME IS GREATER THAN THE
LAST_GOOD_REFRESH_DATE_TIME (COMES FROM THE PROCESS TABLE). BECAUSE WE ARE UPDATING
A COLUMN FOR WHICH WE MAY HAVE MORE THAN ONE VALUE (;ABC;DEF;), WE WILL SPLIT THE PROCESS
IN TWO PARTS.
*/

SELECT @Step_Name = 'SP_R7220_OCRC_ADDR_INVL_CLS - UPDATING'
UPDATE DQOR_test.DBO.DQOR_PROCESS
SET LAST_PROCESS = 'STARTED-' + @Step_Name + ' AT ' + CONVERT(CHAR(8),CURRENT_TIMESTAMP,108)
WHERE ACTIVE_RECORD = 1

-- load the modified records into #t3.
CREATE TABLE #t3 --
(t3id varchar(25), t3CD char(25), t3MODDT datetime)
INSERT INTO #t3
(t3id, t3CD, t3MODDT)
SELECT DISTINCT
NOIPA.ID, NOIPA.CLASSIFICATION, NOIPA.MODTIME
FROM NICHERMS_TEST.DBO.TBL_GOCCIVPA NOIPA INNER JOIN
DQOR_test.DBO.OCRC_ADDR_INVL_CLS U ON NOIPA.ID = U.OCRC_ADDR_ID
WHERE NOIPA.MODTIME > @Last_Good_Run --and LEN(NOIPA.CLASSIFICATION) > 5

-- delete records in OCRC_ADDR_INVL_CLS instead of trying to figure out
-- which codes have changed
DELETE FROM DBO.OCRC_ADDR_INVL_CLS
FROM DBO.OCRC_ADDR_INVL_CLS INNER JOIN
#t3 ON DBO.OCRC_ADDR_INVL_CLS.OCRC_ADDR_ID = #t3.t3id COLLATE database_default

-- generate all the new records
DECLARE all_recs CURSOR READ_ONLY -- Create a cursor with T2
FOR -- so we can process each new record
SELECT
t3id,
t3CD,
t3MODDT
FROM #t3

OPEN all_recs
FETCH all_recs INTO @rec_id, @ClsString, @Mod_dt -- load first record

CREATE TABLE #t4 -- this table will hold all the new records
(t4ID varchar(25), t4CD char(3), t4MODDT datetime) -- generated based on their number of codes

WHILE @@FETCH_STATUS = 0 -- loop for all records
BEGIN -- figure out how many codes are in the classification
SET @Cls_Len = LEN(@ClsString) -- by its lenght
SET @Num_of_cd = dbo.Nbr_Cls_Cd (@Cls_Len) -- UDF call. returns the number of codes
WHILE @Num_of_cd <> 0 -- loop for classification
BEGIN
SET @Cd = dbo.Cls_Cd (@Num_of_cd, @ClsString) -- UDF call: return one single code from the string
INSERT INTO #t4 (t4id, t4cd, t4moddt) -- store this new record
VALUES( @rec_id, @Cd, @Mod_dt )
SET @Num_of_cd = @Num_of_cd - 1
END
FETCH all_recs INTO @rec_id, @ClsString, @Mod_dt -- load next record
END

CLOSE all_recs
DEALLOCATE all_recs

INSERT INTO DBO.OCRC_ADDR_INVL_CLS -- insert all the new generated records in OCRC_ADDR_INVL_CLS
( OCRC_ADDR_ID,
OCRC_INVL_CLS_CD,
MOD_DT
)
SELECT
t4ID,
t4CD,
t4MODDT
FROM #t4

DROP TABLE #t3
DROP TABLE #t4
GO

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-05-25 : 20:23:39

Well, one big performance issue is the fact that you've got two seperate sets of cursors both with nested loops going on. It would be huge if you could make your code "set-based" instead of procedural. Besides that, you should put all your create table statements at the beginning of the sp. The sp is probably being re-compiled every time the code reaches another create table.

Be One with the Optimizer
TG

_Raclede_
Starting Member

3 Posts

Posted - 2005-05-25 : 20:46:25

use tv-udf or temp tables and construct SQL well to avoid using cursors

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources