can this query be improved??

Question

Hi,

Please have a look at the below SQL code.

DECLARE @RET TABLE(OID BIGINT NOT NULL,rowid bigint identity);
DECLARE @ResultTbl TABLE(OID BIGINT,sOID BIGINT,partkey bigint);

DECLARE @PATOID as VARCHAR(4000)

SET @PATIENTOID= '95,96,192,253,110,201,201,83,87,88,208,208,208,208'
INSERT INTO @RET SELECT OID FROM dbo.FGETBIGINTLIST(@PATOID )


DECLARE @NoOfRows bigint
DECLARE @InOID bigint
select @NoOfRows =  max(rowid) from @RET

while (@NoOfRows >=1)
begin
     select @InOID = oid from @RET where rowid=@NoOfRows
    insert into @ResultTbl 
            select * from fresolve_11(@InOID)
    set @NoOfRows = @NoOfRows - 1

end

SELECT * FROM @RET 
SELECT * FROM @ResultTbl 

The function FGETBIGINTLIST accepts a comma separated value as a parameter and returns the value in table format.. just like

OID 

95
96
192
253
110
201
201
83
87
88
208
208
208
208

and function fresolve_11 accepts the bigint data returned by FGETBIGINTLIST and will return the output in this format

OID                  sOID                 PartKey
-------------------- -------------------- -----------
95                   95                   6

My requirement is to pass each data returned by the FGETBIGINTLIST to function fresolve_11 and it should return a result set like this

OID                  sOID                 partkey
-------------------- -------------------- --------------------
208                  208                  29
208                  208                  29
208                  208                  29
208                  208                  29
88                   88                   29
87                   87                   28
83                   83                   24
201                  201                  22
201                  201                  22
110                  110                  21
253                  253                  14
192                  192                  13
96                   96                   7
95                   95                   6

My query works perfectly and returns the expected result. But I am looking for better alternatives without using while loop and 2 table variables.

Thanks in advance.

Cheers

Ramesh Vel

Answer 1

I had to look it up myself, but:

SELECT result.*
FROM dbo.FGETBIGINTLIST(@PATOID) AS OIDs
OUTER APPLY dbo.fresolve_11(OIDs.OID) AS result

Works for me. Should be SQL Server 2005 and up.

See the MSDN page on APPLY (SQL Server 2005 version).