In my earlier article, I developed a solution How to use multiple values for IN clause using same parameter (SQL Server). Recently, I received a query, inquiring how to pass multiple values through one parameter in a stored procedure.
Given below are the two different Methods :
Method 1 : Using XQuery
In this solution, you need to pass a single comma delimiter string to the stored procedure. Once it is passed, you need to convert the string parameter to xml variable and split it using comma delimiter and then you can query it.
Given below is the script.
--This script is compatible with SQL Server 2005 and above.
USE AdventureWorks2012
GO
CREATE PROCEDURE usp_Employeelist
@Cities NVARCHAR(30)
AS
DECLARE @CitiesXML AS XML
SET @CitiesXML = cast(('<a>'+replace(@Cities,',' ,'</a><a>')
+'</a>') AS XML)
SELECT
BusinessEntityID
, FirstName
, JobTitle
, City
FROM HumanResources.vEmployee
WHERE City IN
(
SELECT
A.value('.', 'varchar(max)')
FROM @CitiesXML.nodes('A') AS FN(A)
)
ORDER BY BusinessEntityID
GO
--Execute the stored procedure using multiple values
--through one parameter in this stored procedure
USE AdventureWorks2012
GO
EXEC usp_Employeelist
'Cambridge,Newport Hills,Berlin,Bordeaux'
GO
--OUTPUT
Method 2 : Using Dynamic queryhe
In this solution, you need to pass a single comma delimiter string to the stored procedure. Once it is passed, you need to replace the parameter with single quote and create a dynamic query and execute it.
Given below is the script.
--This script is compatible with SQL Server 2005 and above. USE AdventureWorks2012 GO CREATE PROCEDURE usp_Employeelist @Cities NVARCHAR(30) AS DECLARE @Sql VARCHAR(MAX) SET @Cities = REPLACE(@Cities,',',''',''') SET @Sql = 'SELECT BusinessEntityID , FirstName , JobTitle , City FROM HumanResources.vEmployee WHERE City IN ( ''' + @Cities + ''' ) ORDER BY BusinessEntityID' EXEC (@Sql) GO --Execute the stored procedure using multiple values --through one parameter in this stored procedure USE AdventureWorks2012 GO EXEC usp_Employeelist 'Cambridge,Newport Hills,Berlin,Bordeaux' GO --OUTPUT
SQL Server Portal 
This is good however, since you are using dynamic SQL, directly injecting the CSV into your string, you should probably mention how SQL injection could affect this. Another alternative which I prefer is actually in the stored proc if you have a helper function that converts a CSV to a table, then you are able to join on that which is much safer than just sending the input directly to the query.
Hi Andrew,
Thank you for your feedback. You are absolutely right. Not only SQL injection, it also replaces single quotes even if you need to filter any integer column as well in Method 2.
But if you use a helper function, it may reduce the performance.
Imran
Mr.Imran,
trying to use your code like bellow, within WHERE clause, I am getting message that query was executed successfully but, upon checking, certain update on the table was not accomplished. How big deal for you to go through this code
USE [DBVoterNovi2012]
GO
/****** Object: StoredProcedure [dbo].[KontPodj_with_param] Script Date: 07/12/2013 08:03:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
— Batch submitted through debugger: SQLQuery162.sql|7|0|C:\Users\nebojsa.klacar\AppData\Local\Temp\~vs6A17.sql
— =============================================
— Author:
— Create date:
— Description:
— =============================================
ALTER PROCEDURE [dbo].[KontPodj_with_param]
— Add the parameters for the stored procedure here
@PollingStation varchar(10)
AS
DECLARE @PollingStationXML AS XML
SET @PollingStationXML = cast((‘‘+replace(@PollingStation,’,’ ,’‘)
+’‘) AS XML)
BEGIN
— SET NOCOUNT ON added to prevent extra result sets from
— interfering with SELECT statements.
SET NOCOUNT ON;
— Insert statements for procedure here
merge dbo.tbl_V_tblVoterNoviWithRowIdAndSplitPS as target
using (select OutputColumn, LastName, RegID from dbo.tbl_V_tblVoterNoviWithRowIdAndSplitPS WHERE PollingStation IN(
SELECT
A.value(‘.’, ‘varchar(max)’)
FROM @PollingStationXML.nodes(‘A’) AS FN(A)
) AND RowID=998) as source
on ( target.RowID>998 and target.LastName = source.LastName)
WHEN MATCHED THEN
UPDATE SET
target.OutputColumn=source.OutputColumn;
select * from dbo.tbl_V_tblVoterNoviWithRowIdAndSplitPS
END
BEGIN
— SET NOCOUNT ON added to prevent extra result sets from
— interfering with SELECT statements.
SET NOCOUNT ON;
— Insert statements for procedure here
merge dbo.tbl_V_tblVoterNoviWithRowIdAndSplitPS as target
using (select OutputColumn, LastName, RegID from dbo.tbl_V_tblVoterNoviWithRowIdAndSplitPS WHERE PollingStation IN(
SELECT
A.value(‘.’, ‘varchar(max)’)
FROM @PollingStationXML.nodes(‘A’) AS FN(A)
) AND RowID=1998) as source
on ( target.RowID>1998 and target.LastName = source.LastName)
WHEN MATCHED THEN
UPDATE SET
target.OutputColumn=source.OutputColumn;
select * from dbo.tbl_V_tblVoterNoviWithRowIdAndSplitPS
END
BEGIN
— SET NOCOUNT ON added to prevent extra result sets from
— interfering with SELECT statements.
SET NOCOUNT ON;
— Insert statements for procedure here
merge dbo.tbl_V_tblVoterNoviWithRowIdAndSplitPS as target
using (select OutputColumn, LastName, RegID from dbo.tbl_V_tblVoterNoviWithRowIdAndSplitPS WHERE PollingStation IN(
SELECT
A.value(‘.’, ‘varchar(max)’)
FROM @PollingStationXML.nodes(‘A’) AS FN(A)
) AND RowID=2998) as source
on ( target.RowID>2998 and target.LastName = source.LastName)
WHEN MATCHED THEN
UPDATE SET
target.OutputColumn=source.OutputColumn;
select * from dbo.tbl_V_tblVoterNoviWithRowIdAndSplitPS
END
BEGIN
— SET NOCOUNT ON added to prevent extra result sets from
— interfering with SELECT statements.
SET NOCOUNT ON;
— Insert statements for procedure here
merge dbo.tbl_V_tblVoterNoviWithRowIdAndSplitPS as target
using (select OutputColumn, LastName, RegID from dbo.tbl_V_tblVoterNoviWithRowIdAndSplitPS WHERE PollingStation IN(
SELECT
A.value(‘.’, ‘varchar(max)’)
FROM @PollingStationXML.nodes(‘A’) AS FN(A)
) AND RowID=3998) as source
on ( target.RowID>3998 and target.LastName = source.LastName)
WHEN MATCHED THEN
UPDATE SET
target.OutputColumn=source.OutputColumn;
select * from dbo.tbl_V_tblVoterNoviWithRowIdAndSplitPS
END
GO
USE [DBVoterNovi2012]
GO
EXEC KontPodj_with_param
‘036A001,029B100’
GO
Hi,
It is very easy to debug. Basically you need to print @PollingStationXML and check its value and also instead of merge first select the query and check if it returns the result or not.
Or you can use SQL Server debugger as well to debug.
Thank you
Imran
Msg 257, Level 16, State 3, Procedure KontPodj_with_parametersArray, Line 15
Implicit conversion from data type xml to nvarchar is not allowed. Use the CONVERT function to run this query.
Msg 257, Level 16, State 3, Procedure KontPodj_with_parametersArray, Line 34
Implicit conversion from data type xml to nvarchar is not allowed. Use the CONVERT function to run this query.
Msg 2812, Level 16, State 62, Line 2
Could not find stored procedure ‘KontPodj_with_parametersArray’.
Please send me some sample @PollingStation data.
‘036A001,029B092,083B020,074B014’ etc…..400 of them….
Please check the line number in the stored procedure where it generates the error.
Msg 257, Level 16, State 3, Procedure KontPodj_with_parametersArray_NeRadi_15jul, Line 14
Implicit conversion from data type xml to nvarchar is not allowed. Use the CONVERT function to run this query.
Msg 257, Level 16, State 3, Procedure KontPodj_with_parametersArray_NeRadi_15jul, Line 28
Implicit conversion from data type xml to nvarchar is not allowed. Use the CONVERT function to run this query.
Msg 2812, Level 16, State 62, Line 2
Could not find stored procedure ‘KontPodj_with_parametersArray_NeRadi_15jul’.
Mr. Imran, this very moment must leave the office, bus is waiting…can be back in 90 minutes, sorry that I must do that…I am really sorry..
Thank yo so for any help…will switch on computer at home in 90 minutes…
Most welcome
Sorry for this update but, previously sent error report pertains to slightly changed your code. Beneath is error report that pertains to your code. Sorry but, it is almost the same report…
Msg 257, Level 16, State 3, Procedure KontPodj_with_parametersArray_NeRadi_15jul, Line 14
Implicit conversion from data type xml to nvarchar is not allowed. Use the CONVERT function to run this query.
Msg 257, Level 16, State 3, Procedure KontPodj_with_parametersArray_NeRadi_15jul, Line 33
Implicit conversion from data type xml to nvarchar is not allowed. Use the CONVERT function to run this query.
Msg 2812, Level 16, State 62, Line 2
Could not find stored procedure ‘KontPodj_with_parametersArray_NeRadi_15jul’.
Hi,
I’ve a webform that has five fields,, the first four fields has textboxes and the fifth one is a checkbox list. All the data of textboxes has to be saved in a table tbl_Workshop.
the checkbox list has list of trainers, a user can select for that particular wokrshop.. the checkbox selected items should go in tbl_WorkshopTrainers that has columns workshopId and trainerID.
I wrote a below storedprocedure to insert the webform data into my db.
but this is taking just one trainerID, how abt inserting as many trainers as the user selects from the chkboxlist?
alter procedure sp_InsertWorkshopTrainers
(
@Title as varchar(50),
@Topic as varchar(50),
@Date as date,
@Duration as varchar(50),
@CreatedDate as date,
@UpdatedDate as date,
@TrainerID as int
)
as
begin try
begin transaction tr_Insert
insert into dbo.tbl_Workshop
values(@Title,@Topic,@Date,@Duration,@CreatedDate,@UpdatedDate)
declare @WorkshopID as int
SET @WorkshopID=SCOPE_IDENTITY()
insert into dbo.tbl_WorkshopTrainer
values(@TrainerID,@WorkshopID)
commit transaction
end try
begin catch
rollback transaction tr_insert
end catch
Hi Salwa,
Are you using dot net as a front end ?
Imran
Thanks Friend…
XML is case sensitive,so change the node name to ‘a’ in selecting query…
The Method 2 : Using Dynamic queryhe, i am passing about 10 values.. but the result is coming only first 3 values. Can you tell me why and what need to change, if i want the results for all 10 values.