just think to pass comma separated Id as a parameter or how to use comma delimited value as parameter and use that in SQL server, since there is no way to use parameter as an array so we can use string for multiple IDs or values but if it's integer then we cannot use it in IN clause in SQL Server even if it is string (varchar). So how to use multiple values or IDs as parameter and use that in IN clause. we see some tricks to use them and get our result as we want.
Let's first discuss the simplest way, suppose you want to get all the product in Products table where ProductId in (100, 101, 102, 110, 500, 600, 700, 800, 900, 1000) then
Declare @Ids Varchar(50) Set @Ids = ',100,101,102,110,500,600,700,800,900,1000,' SELECT * FROM Products WHERE Charindex(',' + CAST(ProductId as Varchar)+ ',', @Ids) > 0
Note the "," at beginning and end in Ids, you can convert it into procedure and use comma separated string as parameter starting with comma as shown in example.
Other way is to use the dynamic query, convert entire query into string and finally use Exec (@string) to execute the query see this (no need to keep comma at begginig and end in ids)
CREATE PROCEDURE procedure_name @OrderList VARCHAR(1000) AS Declare @SQL VARCHAR(1000) SET @SQL = 'SELECT * FROM Products' + 'WHERE ProductId IN (' + @OrderList + ')' Exec ( @SQL) GO
Third way is to create a function which can return all the IDs or values as a table, let's see function
CREATEFUNCTION [dbo].[SplitString] (@String varchar(8000), @Delimiter char(1)) returns @temptable TABLE (items varchar(100)) AS BEGIN DECLARE @idx int DECLARE @slice varchar(8000) Select @idx = 1 if len(@String)<1 or @String is null return while @idx!= 0 BEGIN set @idx = charindex(@Delimiter,@String) if @idx!=0 set @slice = left(@String,@idx - 1) else set @slice = @String if(len(@slice)>0) Insert into @temptable(Items) values(@slice) set @String = right(@String,len(@String) - @idx) if len(@String) = 0 break END return END
Now use the above created function to get the ID and use them in IN clause
SELECT * FROM Products WHERE ProductID IN (SELECT Items FROM [dbo].[SplitString](@IDs, ','))
Having 13+ years of experience in Microsoft Technologies (C#, ASP.Net, MVC and SQL Server). Worked with Metaoption LLC, for more than 9 years and still with the same company. Always ready to learn new technologies and tricks.
|By Ali Adravi On 02 May, 13 Viewed: 9,817|
We can not directly change a column to identity columns, say we have a primary key column or any normal column in our table and want to make it identity column then we cannot do it directly by altering the table but there are only two way to change column to identity column 1. Create a new... By Ali Adravi On 02 May 2013 Viewed: 1,076
There are many ways which can help us to save our time to write some repeated code or to do some repeated action, in this post we will see some very common code which we need very frequently in our development life. I am not saying that after this post you will save your half say work every day but... By Ali Adravi On 31 Mar 2013 Viewed: 920
In SQL server there are decimal and money data type to store precision and scale both together or say decimal values. Suppose we want to get precision and scale separately then how to get it. There are many ways to do this but there is a function in SQL named PARSENAME which can be used to get... By Myghty On 26 Mar 2013 Viewed: 3,253
To write a good performing procedure or function we should be aware about available wildcards in SQL Server. I found most developer depends on ‘%’ wildcard only while we have others as well. In this article we will discuss all the available wildcards in detail with examples. First of all let’s see... By Dev D On 25 Feb 2013 Viewed: 658
When we need to concatenate two columns simply we can use `+` sign and that is correct, but what if any of them is null, Will it return what we want, NO, it will return null. So let's discuss how we should concatenate two or more columns without creating extra space or extra comma problem. Let's... By Ali Adravi On 13 Feb 2013 Viewed: 12,419