In this post i will write about how to pass multiple comma separated values as a single parameter into a function in sql server.You can use the same approach to pass multiple comma separated values as a single parameter into a stored procedure as well
Let me create a table into DemoDB database and I will be going to insert the data into the table as shown below.
Now the figure below displays the data in the table
Now I am creating a function which has a single parameter @str of varchar.The @str parameter can accept multiple comma separated values as a single parameter .Because this function has multiple statements inside it , I have to enclose the definition of the function between begin and end statement. This is shown in the image below
Now I write a select command which is calling the function and passing multiple comma separated values withing a single function as shown below.
Now I will explain you what actually this function is doing .
As shown in the image above that inside the definition of the function, I am converting the comma separated parameter into XML using the replace function. Then I click on the highlighted xml link and another window gets opened as shown in the image below.
It means that i have converted comma separated string text value into XML data and later on with the help of Nodes Method ,(which is one of the XmlMethods), I have converted the xml data into the tabular result as shown in the image below.
In this way you can convert multiple comma separated values as a single parameter into a function
Download the script
Script–How to pass multiple comma separated values as a single parameter into a function