Dynamic packet query

Originally posted address: http://community.9cbs.net/expert/topic/3428/3428792.xml? Temp = .6476251

- Example Data Create Table Table (ID INT, NUM INT) INSERT Table SELECT 1,2UNION All SELECT 2,3UNION All SELECT 3,2UNION All SELECT 4,2UNION All SELECT 5,12UNION All SELECT 6,2UNION All SELECT 7,1UNION All Select 8,5UNION All SELECT 9,1GO

/ * - Problem Description:

Enter the packet parameters, such as input "3, 6", implement the packet parameters by ID <= 3, 3 6 packet query, such as input "2, 5, 8", implementing Id <= 2, 2 8 packet query - * /

- Inquiry stored procedure Create Proc P_Qry @ Numlist varchar (1000) Asset NoCount OnDeclare @t Table (ID Int Int, Group VARCHAR (10), A INT, B INT) Declare @i int, @ pnum varchar (10) SELECT @ i = charindex (',', @ Numlist ','), @ pnum = left (@ Numlist, @ i-1), @ Numlist = stuff (@ Numlist, 1, @ i, '), @ i = Charindex (',', @ Numlist) INSERT @t select 'id <=' @ pnum, null, @ pnumwhile @i> 0begin insert @t select @pnum '' @ Numlist, @ Numlist, NULL

Select B. Group, Num = SUM (A.NUM) from Table A, @ T bwhere case when ba is null kil1n case 0 end <= bb byen 1 else 0 end when bb is null kiln case at a.id> Ba Then 1 Else 0 End else Case When A.id> Ba and A.ID <= BB THEN 1 ELSE 0 end end = 1Group by b. Group ORDER BY MIN (B.ID) GO

- Call the stored procedure to query EXEC P_QRY '2, 5, 8'go

- Delete Test DROP TABLE Table Drop Proc P_QRY

/*--Test Results