Requirement:
Multiple Customers were selected from a interface created in VB. A stored procedure was created which accepts the customer codes as a parameter in the form of 100,200,300,400. Wanted to fetch the information for only those customers selected. Ideally we will have to create a dynamic SQL which would be somewhat like this
Set @StrSQL =
‘Select * from Customers where CustomerCode IN (‘
+ @CustomerCode + ‘)’
I wanted to do the same without the use of dynamic SQL.
Solution:
I have tried to find inbuilt procedure/function which splits a delimited string into table.
If u pass a string as '100,200,300,400' then it returns a table having 2 columns as DataValue and ValueAt. The DataValue column contains the values and the ValueAt column will contain the position of the value in the string.
Select *
from DBO.JUF_SplitStringToTable('100,200,300,400, ',')
The result for the above query would be as
DataValue ValueAt
100 1
200 2
300 3
400 4
Now my above query without Dynamic SQL would be as
Eg.: Select * from Customers where CustomerCode IN
(Select DataValue
from DBO.JUF_SplitStringToTable('100,200,300,400, ',')
)