SQL Server

How to pass multiple comma separated values as a single parameter into a function in sql server

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.

How to pass multiple comma separated values as a single parameter into a function1

 

Now  the figure below displays the data in the table

 

How to pass multiple comma separated values as a single parameter into a function

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

 

How to pass multiple comma separated values as a single parameter into a function

 

Now I write a select command which is calling the function  and passing multiple comma separated values withing a single function as shown below.

 

How to pass multiple comma separated values as a single parameter into a function

Now I will explain you what actually this function is doing .

 

How to pass multiple comma separated values as a single parameter into a function

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.

 

How to pass multiple comma separated values as a single parameter into a function

 

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.

 

How to pass multiple comma separated values as a single parameter into a function

 

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

Leave a Reply

Your email address will not be published.