dynamic TOP N records - 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)

dynamic TOP N records

Author

Topic

challa_praveena
Starting Member

7 Posts

Posted - 2004-10-07 : 13:37:12

Its very urgent guys - please help me

I have this stored procedure - where I have to decalre a variable and pass it as @Var to get TOP @Var rows

my stored procedure is like this :
----------------------------------
@intENCOUNTER_HEALTH_PLAN_TYPE_CATEGORY_VALUE_ID int,
@intENCOUNTER_TYPE_CATEGORY_VALUE_ID int,
@intTOP_N_COUNT int

AS

--first, get all of the records and put them into a temp table
----------------------------------
INSERT INTO #tempAllEncounters
SELECT TOP 2500 E.ENCOUNTER_ID,
CLIENT_ID,
VENDOR_AHCCCS_ID,
VENDOR_ENTITY_TYPE_FLAG

FROM tblENCOUNTER E
LEFT JOIN tblENCOUNTER_STATUS ES
ON E.ENCOUNTER_ID = ES.ENCOUNTER_ID

LEFT JOIN wrktbl837_BizTalk_work
ON E.ENCOUNTER_ID = wrktbl837_BizTalk_work.ENCOUNTER_ID

WHERE (E.ENCOUNTER_HEALTH_PLAN_TYPE_CATEGORY_VALUE_ID = @intENCOUNTER_HEALTH_PLAN_TYPE_CATEGORY_VALUE_ID)
AND (E.ENCOUNTER_TYPE_CATEGORY_VALUE_ID = @intENCOUNTER_TYPE_CATEGORY_VALUE_ID)
AND (ES.ENCOUNTER_STATUS_END_DATE IS NULL)
AND (ES.ENCOUNTER_STATUS_CATEGORY_VALUE_ID = 54927) --- Validated Status
AND wrktbl837_BizTalk_work.ENCOUNTER_ID IS NULL

ORDER BY VENDOR_AHCCCS_ID,
CLIENT_AHCCCS_ID,
FREQUENCY_TYPE_CATEGORY_VALUE_ID DESC,
E.ENCOUNTER_ID

the TOP 2500 is the one I need to make it dynamic - so that user can pass the number he wants.

please help me

thank you

rgds
pivi

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-10-07 : 13:39:24

http://www.sqlteam.com/item.asp?ItemID=233

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources