In this article we will see as how we can query the result from relational table when the input parameter is sending data in the form of string separated by a delimiter. We will use Product table from AdventureWorks for this article.
To elaborate above scenario let's see an example:
Say we want to return some product Number from Product table and in normal condition our query would be something like this:
select ProductId,Name,ProductNumber from ADWD.saleslt.Product where ProductNumber in (FR-R92B-58,FR-R92R-58,HL-U509-R)
In case, if we wanted to send all these values in one parameter then we would need to separate the values on the basis of their delimiter like comma in this case. In order to accomplish this we would need to first create a string separator function that will just do the job
CREATE FUNCTION [dbo].[StringSeparator](@Value varchar(max), @separator char(1))
returns @hold_data_table TABLE (returnval varchar(max))
as
begin
declare @i as int
declare @separator_index as varchar(8000)
select @i = 1
if len(@Value)<1 or @Value is null or @Value='' return
while @i!= 0
begin
set @i = charindex(@separator,@Value)
if @i!=0
set @separator_index = left(@Value,@i - 1)
else
set @separator_index = @Value
if(len(@separator_index)>0)
insert into @hold_data_table(returnval) values(@separator_index)
set @Value = right(@Value,len(@Value) - @i)
if len(@Value) = 0 break
end
return
end
GO
Once the function is created we can create the Procedure in which we will pass multiple values of Product Number and would separate values using function bulit above to retrieve values as shown below:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[test_param]
@param nvarchar(max)
as
begin
set nocount on;
Declare @tr nvarchar(100)
Declare @SQL nvarchar(max)
select @SQL=''
select @SQL =@SQL+'select ProductId,Name,ProductNumber from ADWD.saleslt.Product where ProductNumber in (select * from dbo.[StringSeparator]('+''''+@param+''''+','',''))'
END
exec sp_executesql @sql
On running above script and creating the procedure let’s execute it as below:
exec [test_param] @param='FR-R92B-58,FR-R92R-58,HL-U509-R'