query efficiency 2

[ksbrace]

Hello,
I have this query where I'm using a cursor. I was wondering if there is a way I could change it and NOT use a cursor. Any help would be greatly appreciated. Thanks in advance!

CREATE PROCEDURE dbo.usp_Path_Select

AS

DECLARE @Name varchar(500)

--TEMP TABLE 
CREATE TABLE #tempID
(Uid int,path varchar(500))

INSERT INTO #tempID

SELECT DISTINCT  
	FH.UniqueID,
	FH.[Name]
	FROM FileHieararchy FH 
	INNER JOIN tbl_File_Views F_V ON F_V.UniqueID = FH.UniqueID
	INNER JOIN tbl_File_ViewDetails F_VD ON F_VD.GroupID = F_V.GroupID
	INNER JOIN tbl_File_Details F_D ON F_D.FileID = F_VD.FileID 
	WHERE F_V.GroupID IS not null

DECLARE idCursor CURSOR FOR

--iterating through the temp table and getting the full path based on the Uid
SELECT Uid FROM #tempID
FOR READ ONLY

DECLARE @tempUid int

OPEN idCursor
FETCH NEXT FROM idCursor INTO @tempUid

WHILE(@@fetch_status = 0)
	
	BEGIN
		--setting the full path 		
		SET @Name = dbo.lookUp(@tempUid)
		UPDATE #tempID
		SET path = @name WHERE Uid = @tempUid
		
		FETCH NEXT FROM idCursor INTO @tempUid
	END CLOSE idCursor

DEALLOCATE idCursor

SELECT Uid AS UniqueID, 
	 path AS sName 
	 FROM #tempID

--dumping the temp table
DROP TABLE #tempID

GO

 

[DLSQL]

This should do it:
SELECT DISTINCT FH.UniqueID, dbo.lookUp(@tempUid)
FROM FileHieararchy FH
INNER JOIN tbl_File_Views F_V ON F_V.UniqueID = FH.UniqueID
INNER JOIN tbl_File_ViewDetails F_VD ON F_VD.GroupID = F_V.GroupID
INNER JOIN tbl_File_Details F_D ON F_D.FileID = F_VD.FileID
WHERE F_V.GroupID IS not null


DL
MCDBA, MCSD, MCT, etc.

 

[JohnDTampaBay]

It looks like this procedure returns the UniqueID and Path for that UniqueID. Since the function dbo.lookUp will give you the Path for a UniqueID, it can be used in a SELECT statement. Try this:

SELECT DISTINCT
FH.UniqueID,
dbo.lookUp(FH.UniqueID) AS sName
FROM FileHieararchy FH
INNER JOIN tbl_File_Views F_V ON F_V.UniqueID = FH.UniqueID
INNER JOIN tbl_File_ViewDetails F_VD ON F_VD.GroupID = F_V.GroupID
INNER JOIN tbl_File_Details F_D ON F_D.FileID = F_VD.FileID
WHERE F_V.GroupID IS not null

--Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.

 

[ksbrace]

thanks Angel & DLSQL, that worked perfectly. Why isn't my case expression working properly?

CREATE PROCEDURE dbo.usp_Path_Select

(
@Order int = 1
)

AS

SELECT DISTINCT  
    FH.UniqueID,
    dbo.lookUp(FH.UniqueID) AS sName
    FROM FileHieararchy FH 
    INNER JOIN tbl_File_Views F_V ON F_V.UniqueID = FH.UniqueID
    INNER JOIN tbl_File_ViewDetails F_VD ON F_VD.GroupID = F_V.GroupID
    INNER JOIN tbl_File_Details F_D ON F_D.FileID = F_VD.FileID 
    WHERE F_V.GroupID IS not null 

order by 

case @Order when 0 then
	sName end desc 
case @Order when 1 then 
        sName end asc 
Go

 

[JohnDTampaBay]

CASE can't be used that way, but this should do the same thing:

CREATE PROCEDURE dbo.usp_Path_Select
(
@Order int = 1
)
AS
SELECT DISTINCT
IDENTITY(int,1,1) AS SortID,
FH.UniqueID,
dbo.lookUp(FH.UniqueID) AS sName
INTO #temp
FROM FileHieararchy FH
INNER JOIN tbl_File_Views F_V ON F_V.UniqueID = FH.UniqueID
INNER JOIN tbl_File_ViewDetails F_VD ON F_VD.GroupID = F_V.GroupID
INNER JOIN tbl_File_Details F_D ON F_D.FileID = F_VD.FileID
WHERE F_V.GroupID IS not null
order by sName ASC

SELECT * FROM #Temp
case @Order when 0 then SortID
when 1 then -(SortID) end
asc
Go


--Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.

 

[JohnDTampaBay]

Oops! The final portion of the procedure should read...

SELECT * FROM #Temp
ORDER BY CASE @Order WHEN 0 THEN SortID
WHEN 1 THEN -(SortID) END ASC

--Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.