Stored Procedure Help - SQL 2005

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

Transact-SQL (2005)

Stored Procedure Help - SQL 2005

Author

Topic

rohaizad_sulaiman77
Starting Member

1 Post

Posted - 2009-03-25 : 00:01:54

Hi Anyone!!!, I need help on my simple stored procedure... I am not sure if this is right way to do it. Basically, I want to pick up 1 record from my table based on each customer ID and pump it into another table.... the thing is, its running but its been running for more than 3 hrs. Am I doing it the right way or is there a better way to optimise this????

Thanks so much !!!! Here's the stored procedure...

ALTER PROCEDURE [dbo].[usp_caller_customerfirstlifeproduct] AS

/*DECLARE @WCOUNT AS INT;*/
DECLARE @NRIC AS varchar(50);

DECLARE cursor_mynric CURSOR FOR
SELECT DISTINCT NRIC FROM dbo.V_LIFE_PRODUCT_POLICYHOLDERS /* take NRICs from my view */

OPEN cursor_mynric;
FETCH NEXT FROM cursor_mynric INTO @NRIC /* get the NRICs above into my cursor */

/*DECLARE @TempTable TABLE /* declare but not being used yet */
(
TEMPNRIC as char(50),
TEMPPRODUCT_CODE as char(50),
TEMPISSUE_DATE DateTime
)*/

BEGIN TRANSACTION

WHILE @@FETCH_STATUS = 0
BEGIN
/*INSERT INTO @TempTable (TEMPNRIC, TEMPPRODUCT_CODE, TEMPISSUE_DATE)*/
/*EXEC usp_user_customerfirstlifeproduct @NRIC*/

/* get the oldest policy in LIFE and store the NRIC, product code, issue date into the association table */
INSERT INTO T_PRODUCT_ASSOCIATION (NRIC, FirstProduct, FirstProduct_IssueDate)
SELECT TOP 1 NRIC, PRODUCT_CODE, ISSUE_DATE
FROM dbo.V_LIFE_PRODUCT_POLICYHOLDERS
WHERE NRIC=@NRIC
ORDER BY ISSUE_DATE ASC

/* go to the next NRIC */
FETCH NEXT FROM cursor_mynric
INTO @NRIC
END

COMMIT TRANSACTION
PRINT 'Successful update'
CLOSE cursor_mynric --- close
DEALLOCATE cursor_mynric

matty
Posting Yak Master

161 Posts

Posted - 2009-03-25 : 05:07:38

Actually you can do it in a single statement.

INSERT INTO T_PRODUCT_ASSOCIATION (NRIC, FirstProduct, FirstProduct_IssueDate)
SELECT NRIC, PRODUCT_CODE, ISSUE_DATE
FROM
(
SELECT *,ROW_NUMBER() OVER(PARTITION BY NRIC ORDER BY ISSUE_DATE) AS Rownum
FROM dbo.V_LIFE_PRODUCT_POLICYHOLDERS
)
WHERE Rownum = 1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-25 : 14:12:49

quote:
Originally posted by matty

Actually you can do it in a single statement.

INSERT INTO T_PRODUCT_ASSOCIATION (NRIC, FirstProduct, FirstProduct_IssueDate)
SELECT NRIC, PRODUCT_CODE, ISSUE_DATE
FROM
(
SELECT *,ROW_NUMBER() OVER(PARTITION BY NRIC ORDER BY ISSUE_DATE) AS Rownum
FROM dbo.V_LIFE_PRODUCT_POLICYHOLDERS
)t
WHERE Rownum = 1

missed an alias

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources