The stored procedure implementation code of the unfixed parameter _mssql

Source: Internet
Author: User
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, ', '))

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.