不固定参数的存储过程实现代码
前端之家收集整理的这篇文章主要介绍了
不固定参数的存储过程实现代码，
前端之家小编觉得挺不错的，现在分享给大家，也给大家做个参考。
我想此时不妨使用字符串参数来帮助我们解决这种情况，利用字符串分割的方法将一个参数分割成数个参数来解决。下面我们看一个例子： 假设现在给你一个产品信息列表（显示出各个商品的基本信息），现在我想要根据所选择商品进行统计（任意选择几种），例如统计出价格<10,11-20,21-30,31-40,41-50,50以上的商品个有多少个（姑且认为就统计这些）。此时如果使用存储过程就势必需要传入所选商品的id作为参数，但是id个数是不固定的。此时估计会有人这样写：
<div class="codetitle"><a style="CURSOR: pointer" data="96003" class="copybut" id="copybut96003" onclick="doCopy('code96003')"> 代码如下:
<div class="codebody" id="code96003">
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: jianxin160
-- Create date: 2010.11.05
-- Description:
统计商品
-- =============================================
ALTER PROCEDURE StatProductInfo
(
@ids VARCHAR(8000)
)
AS
BEGIN
DECLARE @followingTen INT
DECLARE @elevenToTwenty INT
DECLARE @twentyOneToThirty INT
DECLARE @thirtyOneToFourty INT
DECLARE @fourtyOneToFifty INT
DECLARE @fiftyOrMore INT SELECT @followingTen=COUNT(
)
FROM dbo.Products
WHERE ProductID IN(@ids) AND UnitPrice<10 SELECT @elevenToTwenty=COUNT()
FROM dbo.Products
WHERE ProductID IN(@ids) AND UnitPrice BETWEEN 11 AND 20 SELECT @twentyOneToThirty=COUNT(
)
FROM dbo.Products
WHERE ProductID IN(@ids) AND UnitPrice BETWEEN 21 AND 30 SELECT @thirtyOneToFourty=COUNT()
FROM dbo.Products
WHERE ProductID IN(@ids) AND UnitPrice BETWEEN 31 AND 40 SELECT @fourtyOneToFifty=COUNT(
)
FROM dbo.Products
WHERE ProductID IN(@ids) AND UnitPrice BETWEEN 41 AND 50 SELECT @fiftyOrMore=COUNT()
FROM dbo.Products
WHERE ProductID IN(@ids) AND UnitPrice>50 SELECT @followingTen AS '<$10',@elevenToTwenty AS '$11-$20',
@twentyOneToThirty AS '$21-$30',@thirtyOneToFourty AS '$31-$40',
@fourtyOneToFifty AS '$41-$50',@fiftyOrMore AS '>$50'
END
GO
其实如果你测试一下（例如：EXEC dbo . StatProductInfo '3,4,8,10,22' ）是有问题的，
