Help in Understanding Code/tsql

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)

Help in Understanding Code/tsql

Author

Topic

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2008-10-27 : 07:01:46

Dear All
the below code is store procedue.
can u explain about below code?
pls help me
as soon as possible.

here i dont have any idea in fetch and Cursor. these function are not understand.
regard
nazarml

CREATE procedure [dbo].[spgetparentcat_New](
@CUSTID AS VARCHAR(50)
)
as
DECLARE @CATID AS VARCHAR(50), @PARENID VARCHAR(50)

SELECT ParentCategoryID AS CatID INTO #tbl FROM dbo.tblParentCategory WHERE ParentCategoryID IS NULL

DECLARE cus CURSOR FOR
SELECT DISTINCT CategoryId,ISNULL((SELECT TOP 1 ParentCategoryId FROM dbo.tblParentCategory
WHERE ParentCategoryId = CC.CategoryId),'0') AS CAT1
FROM dbo.tblCustomerIngredientCategory CC WHERE CustomerID= @CUSTID

OPEN cus
FETCH cus INTO @CATID,@PARENID

WHILE @@FETCH_STATUS = 0
BEGIN

PRINT @CATID + ' --- ' + @PARENID

IF @PARENID = '0'
BEGIN
INSERT INTO #tbl VALUES (@CATID)
END

WHILE @PARENID <> '0'
BEGIN
SET @CATID = (SELECT TOP 1 ParentCategoryId FROM dbo.tblCategory
WHERE CategoryID = @PARENID)

PRINT '*******************'
PRINT @CATID
PRINT '*******************'

IF @CATID= '0'
BEGIN
INSERT INTO #tbl VALUES (@PARENID)

PRINT 'BREAK'
BREAK
END
ELSE
BEGIN
PRINT '---------'
PRINT 'PARENT INTERCHANED'
PRINT '---------'
SET @PARENID = @CATID
END
END

SET @CATID = ''
SET @PARENID = 'NA'
FETCH cus INTO @CATID,@PARENID
END
CLOSE cus
DEALLOCATE cus

SELECT DISTINCT ParentCategoryID,CategoryName FROM dbo.tblParentCategory
WHERE ParentCategoryID IN (SELECT CatID FROM #tbl)

DROP TABLE #tbl

[It is posted by one of my friend.I am not good in cursors so please help him]

Kamran Shahid
Sr. Software Engineer(MCSD.Net,MCPD.net)
www.netprosys.com

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-10-27 : 07:04:59

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx

-------------
Charlie