SQL Stored Procedure – tham số là danh sách

–Hàm tách chuỗi

CREATE  FUNCTION SplitString

(

@Input NVARCHAR(MAX),

@Character CHAR(1)

)

RETURNS @Output TABLE (

CT int

)

AS

BEGIN

DECLARE @StartIndex INT, @EndIndex INT

SET @StartIndex = 1

IF SUBSTRING(@Input, LEN(@Input) – 1, LEN(@Input)) <> @Character

BEGIN

SET @Input = @Input + @Character

END

WHILE CHARINDEX(@Character, @Input) > 0

BEGIN

SET @EndIndex = CHARINDEX(@Character, @Input)

INSERT INTO @Output(CT)

SELECT SUBSTRING(@Input, @StartIndex, @EndIndex – 1)

SET @Input = SUBSTRING(@Input, @EndIndex + 1, LEN(@Input))

END

RETURN

END

GO

–Gọi hàm trong Stored Proc

GO

CREATE proc [dbo].[TestProc2]

@ct varchar(100)

as

select * from products

where productid in (select CT from dbo.SplitString(@ct,’,’))

 

–Chay thu

testproc2 ‘1,2,3,4,5’

testproc2 ‘1,2,3’

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s