Sometimes you may need to pass a group of integer primary key values like User Id, Country Ids from .NET to SQL Server stored procedure. You cannot pass an array from .NET to SQL Server procedure. A work around solution is that you can concatenate the integers separated by comma and pass it to the procedure as a string parameter. But this string parameter cannot be used in ‘IN’ clause of a SELECT statement in the stored procedure as the primary key IDs are integers in this scenario. Therefore you need to convert the string parameter into a table using a user defined table-valued function.
A table valued function is a SQL Server function which return a TABLE data type. The following user defined function split the comma separated integers passed as a VARCHAR parameter and returns output as a TABLE.
ALTER FUNCTION [dbo].[fn_Split_Up_Ids]
(
@Param_Ids varchar(500)
)
RETURNS @Id_Table TABLE(IDField int)
AS
BEGIN
IF (LEN(@Param_Ids) <= 0)
RETURN
DECLARE @CommaPos smallint
SET @CommaPos = CHARINDEX(',', RTRIM(LTRIM(@Param_Ids)))
IF @CommaPos = 0
INSERT INTO @Id_Table
VALUES(CONVERT(BIGINT ,RTRIM(LTRIM(@Param_Ids))))
ELSE
BEGIN
WHILE LEN(@Param_Ids) > 1
BEGIN
SET @CommaPos = CHARINDEX(',', RTRIM(LTRIM(@Param_Ids)))
INSERT INTO @Id_Table
VALUES(CONVERT(INT ,SUBSTRING(RTRIM(LTRIM(@Param_Ids)),1, @CommaPos - 1)))
SET @Param_Ids = SUBSTRING(RTRIM(LTRIM(@Param_Ids)), @CommaPos + 1 , LEN(RTRIM(LTRIM(@Param_Ids))))
SET @CommaPos = CHARINDEX(',', RTRIM(LTRIM(@Param_Ids)))
IF @CommaPos = 0
BEGIN
INSERT INTO @Id_Table VALUES(CONVERT(INT ,RTRIM(LTRIM(@Param_Ids))))
BREAK
END
END
END
RETURN
END
The above function will work in SQL Server 2000 and 2005 versions.
You can use this function as below
SELECT * FROM dbo.fn_Split_Up_Ids('9,4,2,6,10')
The output will be as below
IDColumn
9
4
2
6
10
To use the id in the IN clause of the SELECT statement
SELECT * FROM COUNTRY
WHERE Country_Id IN (SELECT * FROM dbo.fn_Split_Up_Ids('9,4,2,6,10'))
This method can also be used in SQL Server 2005 Reporting Services(SSRS) to pass multi-value paramters to a stored procedure.
If we use a multi-value paramter in a SELECT statement in SSRS, the parameter will be automatically split into IDs by the system. For example, the below query will work in a dataset in SSRS.
SELECT * FROM COUNTRY
WHERE Country_Id IN (@Country_List)
But this will not work if you write this query in a stored procedure. Therefore you need to use the above function to pass the multi-value paramters to a stored procedure.
|
No responses found. Be the first to respond and make money from revenue sharing program.
|