Sqlserver-passing array parameters using strings and delimiters in Stored Procedures)

1 Introduction

In advanced languages, it is easy to compile functions with array parameters. But it is not so easy in the stored procedure of the database, because the stored procedure parameters can only take some basic types as parameters. We want the array as a parameter to be very common. For example, there is a table (ID int, daTa nvarchar (50), you need to store a batch of data to the table at a time. If the stored procedure uses the basic data type as a parameter, it is defined as insertdata (@ DaTa nvarchar (50), you need to call this stored procedure multiple times.

To make the stored procedure support array parameters, you need to make some modifications. The data to be input can be converted into a string separated by a separator to form a large string, which can be expressed by the basic type text or ntext. This string is passed to the stored procedure. The Stored Procedure parses it internally, that is, remove the separator, put this batch of data into the zero-time table or a variable, and finally insert it into the data table in batches.

Second, parse the process of generating table variables using strings containing delimiters

3. parse the user function script that generates table variables using strings containing delimiters

Set ansi_nulls on

Go

Set quoted_identifier on

Go

If exists (select * From sys. objects where object_id = object_id (n' [DBO]. [splittexttostringarray] ') and type in (n'fn', n'if', n'tf', n'fs', n'ft '))

Drop function [DBO]. [splittexttostringarray]

Go

-- ===================================================== ======

-- Author: fishinthewind

-- Create Date: 7/10/2007

-- Description: Split string variant with type of ntext

-- ===================================================== ======

Create Function [DBO]. [splittexttostringarray]

(

@ Text ntext,

@ Delimiter char (1)

)

Returns @ arraytable table (idx bigint, [value] nvarchar (200 ))

As

Begin

Declare @ splitlen int

Set @ splitlen = 4000

Declare @ idx int set @ idx = 0

-- Define the starting position of the substring

Declare @ textsplit bigint

Set @ textsplit = 1

While (@ textsplit <= datalength (@ text ))

Begin

-- Because many string processing functions cannot be used for ntext Data Types

-- Therefore, ntext Strings need to be processed cyclically in batches and retrieved in batches.

-- Characters are placed in nvarchar (4000) type variables.

Declare @ string nvarchar (4000)

Select @ string = substring (@ text, @ textsplit, @ splitlen)

-- Can retrieve all characters

If Len (@ string) = @ splitlen

Begin

-- Ensure that the extracted characters are a complete string combination separated by delimiters.

Declare @ lastcomma int

Select @ lastcomma = charindex (@ delimiter, reverse (@ string), 1)

-- The strings following the last separator are incomplete and should be discarded.

If @ lastcomma> 0

Begin

Select @ string = substring (@ string, 1, @ splitlen-@ lastcomma)

-- Set the starting position of the next character extraction from @ text

Select @ textsplit = @ textsplit + @ splitlen-@ lastcomma + 1

End

-- The string following the last separator is complete.

Else

Begin

Select @ textsplit = @ textsplit + @ splitlen + 1

End

End

-- Remove less than characters

Else

Begin

Select @ textsplit = @ textsplit + @ splitlen + 1

End

-- Parse @ string and retrieve the substring with the delimiter as the limit

Declare @ I1 int set @ I1 = 1

Declare @ I2 int set @ I2 = 1

While @ I1 <= Len (@ string)

Begin

Set @ I2 = charindex (@ delimiter, @ string, @ I1 + 1)

If @ I2 = 0

Set @ I2 = Len (@ string) + 1

Insert @ arraytable (idx, value)

Select @ idx, substring (@ string, @ I1, @ I2-@ I1)

Set @ I1 = @ I2 + 1

Set @ idx = @ idx + 1

End

End

Return

End

Go

4. process scripts that use this string to separate functions

Set ansi_nulls on

Go

Set quoted_identifier on

Go

If exists (select * From SYS. objects where object_id = object_id (n' [DBO]. [getcategoryrevisionids] ') and type in (n'p', n'pc '))

Drop procedure [DBO]. [getcategoryrevisionids]

Go

-- ===================================================== ======

-- Author: fishinthewind

-- Create Date: 7/10/2007

-- Description: Use the character segmentation function.

-- ===================================================== ======

Create procedure [DBO]. [getcategoryrevisionids]

(

@ Stringarray ntext

)

As

Begin

-- Set nocount on added to prevent extra result sets from

-- Interfering with select statements.

Set nocount on;

Declare @ stringarrayt table (idx bigint, [value] nvarchar (200 ))

Insert into @ stringarrayt (idx, [value])

(

Select idx, [value]

From splittexttostringarray (@ stringarray ,',')

)

Select * From @ stringarrayt

End

Go # Database Technology