Good thoughts with that Thom. SQL Injection, while unlikely in a NVARCHAR(19), could occur. Your solution does eliminate the risk involved of SQL injection.
I suppose if you knew that @comp_cod would only contain integers and a comma, you could do some weird stuff with splitting the string too, but that feels a bit like overkill for this.
In the end, it all depends on what your @comp_cod variable is created from and how easy it is to manipulate the source of that to fit your needs.
IMO, I don't think that using a splitter is overkill. The code required to use one is quite concise:
CREATE TABLE #companies (CompCode NVARCHAR(10));
INSERT #companies
(
CompCode
)
VALUES
(N'2')
,(N'3')
,(N'4');
DECLARE @comp_cod NVARCHAR(19) = N'2,3';
SELECT c.*
FROM
#companies c
CROSS APPLY dbo.udfDelimitedSplit8K(@comp_cod, ',') split
WHERE c.CompCode = split.Item;
The problem with it (that I thought was overkill) was that in that example you need to have a function for the splitter. Not everyone has that installed on their systems (myself included). For everything I use SQL for at my workplace, we do not have a real-world use for a string splitter to be stored in SQL. Splitting strings doesn't feel like a SQL task, but more of an application task (most .NET languages for example do string splitting quite well). So building a function to split the string (yes, I know it has been done on the forum and optimized to death) still feels like overkill to me.
But now I feel like I am getting off topic.
Using same idea i was able to split it using a table valued function and use the result in the in clause.
Thanks for all the replies, i appreciate it.
Nader
Please post the table valued function that you ended up using.