How to handle multiple strings in one variable in SQL server procedure

Question

CREATE PROCEDURE GetData
(
    @ProjectFile nvarchar(125),
    @Culture nvarchar(50)
)
AS
SET NOCOUNT ON;

SELECT tr.ProjectFile, tr.ResourceFile, tr.ResourceName,tr.ResourceValue, trt.Culture,    trt.TranslatedFlag, trt.TranslatedValue, tr.Comments, tr.IsApproved, tr.ParserError AS  'ignore'
FROM  tblResourcesTranslated_NEW trt
INNER JOIN tblResources_NEW  tr  
ON trt.ResourceID = tr.ResourceID AND trt.Culture = @Culture AND tr.ProjectFile=   @ProjectFile
Order by ResourceValue ASC;

This is my procedure to load data by culture and project, right now I'm wondering how to handle it if @Culture and @ProjectFile contains multiple strings separated by comma?

For example, in original design, @culture should be something like 'en', and @ProjectFile should be something like 'translation', but how do I rewrite my code if @Culture = 'en,fr,nl' and ProjectFile = 'translation,accounting,communication,test' ?

Answer 1

You have a couple of options.

The direct answer to your question is to split the strings, then insert multiple rows. See How do I split a string so I can access item x? for a method for splitting strings in SQL.

Normally, I'd say that if you wind up in this situation, you're doing it wrong. You might consider something like this.

project_files
-----------------
id
....

project_resources
------------------------
file_id // foreign key to project_files.id
culture_id
..... resource-specific data

project_cultures
_______________
id
name /// en, fr, etc. 

etc.

Split your culture strings before sending anything to your database server.

Then, you could either do a bulk insert, or just call the stored procedure multiple times.

Or, just keep the culture as a comma-delimited list in-column, and make sure your application is aware of that.

Beware of over-normalization. This could be complete overkill for your application.