We know that stored procedures do not support unfixed parameters (including array parameters), but sometimes our parameters must be unfixed. What should we do?
We know that stored procedures do not support unfixed parameters (including array parameters), but sometimes our parameters must be unfixed. What should we do?
I think we can use string parameters to solve this problem at this time. We can use the string Splitting Method to separate a parameter into several parameters. Here is an example:
Assume that you have a list of product information (showing the basic information of each product). Now I want to make statistics based on the selected product (select any number of products ), for example, the number of products with prices <10, 11-20, 21-30, 31-40, 41-50, and more than 50 is counted ). If the stored procedure is used, the id of the selected item is required as the parameter, but the number of IDS is not fixed. At this time, it is estimated that someone will write like this:
The Code is as follows:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ===================================================== ======
-- Author: jianxin160
-- Create date: 2010.11.05
-- Description: product statistics
-- ===================================================== ======
Alter procedure StatProductInfo
(
@ Ids VARCHAR (8000)
)
AS
BEGIN
DECLARE @ followingTen INT
DECLARE @ elevenToTwenty INT
DECLARE @ twentyOneToThirty INT
DECLARE @ thirtyOneToFourty INT
DECLARE @ fourtyOneToFifty INT
DECLARE @ polictyormore 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 @ polictyormore = 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', @ polictyormore AS'> $50'
END
GO
In fact, if you test (for example, EXEC dbo. StatProductInfo '3, '), there is a problem. SQL server regards this as a parameter and an error occurs during conversion. In this case, it is easier to operate these fields in a virtual table. However, in a virtual table, other tables can be queried, which of the following virtual tables cannot be queried? A smart friend may have thought of using "Table valued functions ". Yes, the answer is to use the "Table value function ". We know that "Table valued functions" can return a "Table" type variable (equivalent to a virtual Table, stored in memory ), we first split the string and store it in a field of "Table valued function, then we can query it from "Table valued functions" (This example is also a typical application of "Table valued functions ). The specific SQL statement is as follows:
The Code is as follows:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ===================================================== ======
-- Author: cmj
-- Create date: 2010.11.05
-- Description: returns a Table with only one column. The data in each row is a split 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 can slightly modify the stored procedure:
The Code is as follows:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ===================================================== ======
-- Author: jianxin160
-- Create date: 2010.11.05
-- Description: product statistics
-- ===================================================== ======
Alter procedure StatProductInfo
(
@ Ids VARCHAR (8000)
)
AS
BEGIN
DECLARE @ followingTen INT
DECLARE @ elevenToTwenty INT
DECLARE @ twentyOneToThirty INT
DECLARE @ thirtyOneToFourty INT
DECLARE @ fourtyOneToFifty INT
DECLARE @ polictyormore INT
SELECT @ followingTen = COUNT (*)
FROM dbo. Products
WHERE ProductID IN (SELECT sp FROM dbo. GetSplitFieldsByString (@ ids, ',') AND UnitPrice <10
SELECT @ elevenToTwenty = COUNT (*)
FROM dbo. Products
WHERE ProductID IN (SELECT sp FROM dbo. GetSplitFieldsByString (@ ids, ',') AND UnitPrice BETWEEN 11 AND 20
SELECT @ twentyOneToThirty = COUNT (*)
FROM dbo. Products
WHERE ProductID IN (SELECT sp FROM dbo. GetSplitFieldsByString (@ ids, ',') AND UnitPrice BETWEEN 21 AND 30
SELECT @ thirtyOneToFourty = COUNT (*)
FROM dbo. Products
WHERE ProductID IN (SELECT sp FROM dbo. GetSplitFieldsByString (@ ids, ',') AND UnitPrice BETWEEN 31 AND 40
SELECT @ fourtyOneToFifty = COUNT (*)
FROM dbo. Products
WHERE ProductID IN (SELECT sp FROM dbo. GetSplitFieldsByString (@ ids, ',') AND UnitPrice BETWEEN 41 AND 50
SELECT @ polictyormore = COUNT (*)
FROM dbo. Products
WHERE ProductID IN (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', @ polictyormore AS '> $50'
END
GO
In this way, execute EXEC dbo. StatProductInfo '3, 4, 8, 10, 22 'to get the desired result:
Will this be faster?
The Code is as follows:
Select sum (case when UnitPrice <10 THEN 1 ELSE 0 END) '<$10 ',
SUM (case when UnitPrice BETWEEN 11 AND 20 THEN 1 ELSE 0 END) '$11-$20 ',
SUM (case when UnitPrice BETWEEN 21 AND 30 THEN 1 ELSE 0 END) '$21-$30 ',
...
SUM (case when UnitPrice> 50 THEN 1 ELSE 0 END) '> $10'
FROM dbo. Products
WHERE ProductID IN (SELECT sp FROM dbo. GetSplitFieldsByString (@ ids ,','))
