How to pass an array to the SQL Server Stored Procedure

Recently writtenProgramIt is required to pass a string array or set to the stored procedure and then use not in for condition judgment.

After careful research, I found a solution to this problem on csdn. I will share it with you.

The general method is to transmit a long string to the stored procedure in the form of a long string. Because sqlserver does not have the splite Function

Therefore, you must define a splite function for processing.

 

Customize a function
Create Function f_splitstr (@ sourcesql varchar (8000), @ strseprate varchar (10 ))
Returns @ temp table (F1. varchar (100 ))
As
Begin
Declare @ I int
Set @ sourcesql = rtrim (ltrim (@ sourcesql ))
Set @ I = charindex (@ strseprate, @ sourcesql)
While @ I> = 1
Begin
Insert @ temp values (left (@ sourcesql, @ i-1 ))
Set @ sourcesql = substring (@ sourcesql, @ I + 1, Len (@ sourcesql)-@ I)
Set @ I = charindex (@ strseprate, @ sourcesql)
End
If @ sourcesql <>''
Insert @ temp values (@ sourcesql)
Return
End
-Execution
Select * From DBO. f_splitstr ('1, 2, 3, 4 ',',')

 

Note: '1, 2, 3, 4 'is the string you pass

You can also use select cunt (*) from DBO. f_splitstr ('1, 2, 3, 4 ',',')

Returns the length of the string array.

If you want to delete this function, use

-- Delete a function
Drop function fsplit

**************************************** *********************************

Another method is to take full advantage of the powerful string function of SQL Server.

However, because the exec command is used, it can only be implemented in the stored procedure.

Create procedure f_splitstr (@ sourcesql varchar (8000), @ strseprate varchar (10 ))
As

Create Table # temp (F1. varchar (100 ))
Declare @ tmpsql varchar (8000)

Select @ tmpsql = 'insert into # temp values ('+ Replace (@ sourcesql, @ strseprate ,')
Insert into # temp values (') + ')'

Exec (@ tmpsql)

Select * from # temp

Drop table # temp
Go

F_splitstr4 '1, 2, 3, 4 ',','

The results are the same.

 

 

 

 

1

2

3

4

5

6

7

8

8

9

9