Some times , we need to pass more than one integer value to query for IN Operator , normally it is not possible ,
because array parameter is taken to one string values like this
Select * from PriceList Where ID IN ('1,2,3,4')
it is not a valid syntax for sql , so you can use charindex method for processing this issue.
The following steps are used to pass multiple values to stored procedure
Step1 : Create Table Named as PriceList
CREATE TABLE [dbo].[PriceList](
[PID] [int] NULL,
[PName] [nvarchar](50) NULL,
[Price] [numeric](6, 0) NULL
)
Step2: Insert the rows for pricelist table
INSERT INTO PriceList Values(1,'A',1000)
INSERT INTO PriceList Values(2,'B',1200)
INSERT INTO PriceList Values(3,'C',1300)
INSERT INTO PriceList Values(4,'D',1500)
INSERT INTO PriceList Values(5,'E',2300)
INSERT INTO PriceList Values(6,'F',2500)
INSERT INTO PriceList Values(7,'G',3200)
INSERT INTO PriceList Values(8,'H',4000)
INSERT INTO PriceList Values(9,'I',4500)
Step3: create a procedure for retrieving pricelist rows based on input parameter which accepts array of product Ids
Create Procedure GetPriceList(@ProductIDs nvarchar(100))
AS
BEGIN
Set @ProductIDs=@ProductIDs + ','
Select * from PriceList Where CHARINDEX(CAST(PID AS NVARCHAR(10))+',',@ProductIDs)>0
END
Step4: Use Execute method to invoke GetPriceList Stored procedure
EXEC GetPriceList '3,4,5'
Conclusion
There is no straight method for passing multi values into sql querys , so we can use charindex method to find the column values present in the rows.
This method is also applicable for multi values of string arrays.
|
| Author: RDRaja 10 Dec 2009 | Member Level: Gold Points : 0 |
Please Wait , I will format the article
|