I think it's time to use string parameters to help us solve this situation, using string segmentation to divide a parameter into several parameters to solve. Let's take a look at the following example:
Suppose you now give a list of product listings (showing the basics of each item), now I want to make a statistic based on the selected items (choose a few), such as Price < How many items are there in the 10,11-20,21-30,31-40,41-50,50 (let's say it counts). If you use a stored procedure at this point, you will need to pass in the ID of the selected item as an argument, but the number of IDs is not fixed. At this point it is estimated that someone will write:
Copy Code code as follows:
SET ANSI_NULLS on
Go
SET QUOTED_IDENTIFIER ON
Go
-- =============================================
--author:jianxin160
--Create date:2010.11.05
--Description: Statistical products
-- =============================================
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 20
SELECT @twentyOneToThirty =count (*)
FROM dbo. Products
WHERE ProductID in (@ids) and UnitPrice BETWEEN and 30
SELECT @thirtyOneToFourty =count (*)
FROM dbo. Products
WHERE ProductID in (@ids) and UnitPrice BETWEEN and 40
SELECT @fourtyOneToFifty =count (*)
FROM dbo. Products
WHERE ProductID in (@ids) and UnitPrice BETWEEN 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
In fact, if you test it (for example, EXEC dbo.) Statproductinfo ' 3,4,8,10,22 ') is problematic, SQL Server thinks this whole is a parameter, the conversion error. At this point we think about if these fields in a virtual table is easier to operate, but the general virtual table is the other table through the query, and now simply can not query where the virtual table? Smart friends may have thought you could use "table-valued functions". Yes, the answer is to use "table-valued functions." We know that a table-valued function can return a variable of type "table" (equivalent to a virtual table, stored in memory), we first place the string split into a field in the table-valued function, and then we can query from the table-valued function (This example is also "table-valued Function"). A typical application). The specific SQL is as follows:
Copy Code code as follows:
SET ANSI_NULLS on
Go
SET QUOTED_IDENTIFIER ON
Go
-- =============================================
--AUTHOR:CMJ
--Create date:2010.11.05
--Description: Returns a table, only one column, each row of data is a segmented string
-- =============================================
CREATE FUNCTION getsplitfieldsbystring
(
@toSplitString varchar (1000),
@splitChar varchar (10)
)
RETURNS
@tb TABLE (sp varchar (100))
As
BEGIN
DECLARE @i INT
SET @toSplitString =rtrim (LTRIM (@toSplitString))
SET @i=charindex (@splitChar, @toSplitString)
While @i>0
BEGIN
INSERT @tb VALUES (left (@toSplitString, @i-1))
SET @toSplitString =right (@toSplitString, LEN (@toSplitString)-@i)
SET @i=charindex (@splitChar, @toSplitString)
End
IF LEN (@toSplitString) >0
INSERT @tb VALUES (@toSplitString)
Return
End
Go
Then we modify the stored procedure a little bit:
Copy Code code as follows:
SET ANSI_NULLS on
Go
SET QUOTED_IDENTIFIER ON
Go
-- =============================================
--author:jianxin160
--Create date:2010.11.05
--Description: Statistical products
-- =============================================
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 (the SELECT SP from dbo. Getsplitfieldsbystring (@ids, ', ')) and unitprice<10
SELECT @elevenToTwenty =count (*)
FROM dbo. Products
WHERE ProductID in (the SELECT SP from dbo. Getsplitfieldsbystring (@ids, ', ')) and UnitPrice BETWEEN 20
SELECT @twentyOneToThirty =count (*)
FROM dbo. Products
WHERE ProductID in (the SELECT SP from dbo. Getsplitfieldsbystring (@ids, ', ')) and UnitPrice BETWEEN and 30
SELECT @thirtyOneToFourty =count (*)
FROM dbo. Products
WHERE ProductID in (the SELECT SP from dbo. Getsplitfieldsbystring (@ids, ', ')) and UnitPrice BETWEEN 40
SELECT @fourtyOneToFifty =count (*)
FROM dbo. Products
WHERE ProductID in (the SELECT SP from dbo. Getsplitfieldsbystring (@ids, ', ')) and UnitPrice BETWEEN and 50
SELECT @fiftyOrMore =count (*)
FROM dbo. Products
WHERE ProductID in (the SELECT SP from dbo. Getsplitfieldsbystring (@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
This is done by executing the EXEC dbo. Statproductinfo ' 3,4,8,10,22 ' will get the results you want:
Try it, will it be quicker?
Copy Code code as follows:
Select sum (case when UnitPrice < THEN 1 ELSE 0) ' <$10 ',
SUM (Case of UnitPrice BETWEEN THEN 1 ELSE 0 end) ' $11-$20 ',
SUM (UnitPrice BETWEEN THEN 1 ELSE 0 end) ' $21-$30 ',
...
SUM (case if UnitPrice > THEN 1 ELSE 0 end) ' >$10 '
from dbo. Products
WHERE ProductID in the SELECT SP from dbo. Getsplitfieldsbystring (@ids, ', '))
