Reputation: 2047
I have this stored procedure:
ALTER PROCEDURE [dbo].[uspPages_HotelPrices_Lookup_Select]
@HotelCode nvarchar(100)
AS
BEGIN
SET NOCOUNT ON;
SELECT *
FROM tPages_HotelPrices_Lookup
WHERE HotelCode IN (SELECT * FROM DBO.ufSplit(@HotelCode, ','))
END
DBO.ufsplit
splits a comma delimited string and returns a table of which each row containing each of the comma separated values.
I am passing a string to this stored procedure with the code below:
static void Main(string[] args)
{
HotelCodesTableAdapter hcTa = new HotelCodesTableAdapter();
DestinationMarketingEntity.HotelCodesDataTable hotelCodesDt = hcTa.GetData();
string hotelCodesString = "";
//Comma separating hotel codes and putting each word in '' to be passed to sql sproc as a list
for (int i = 0; i < hotelCodesDt.Count; i++)
{
hotelCodesString += hotelCodesDt.Rows[i].ItemArray.GetValue(0).ToString() + ",";
}
hotelCodesString = hotelCodesString.TrimEnd(',');
HiltonEEHotelPricesTableAdapter hEETa = new HiltonEEHotelPricesTableAdapter();
WorldWideFeedEntity.HiltonEEHotelPricesDataTable hEEDt= hEETa.GetData(hotelCodesString);
}
The last line is where the stored procedure is being called.
Essentially hotelCodesString
will be similar to "1,2,3"
but this is returning nothing form this stored procedure. But if I run the below:
select *
from tPages_HotelPrices_Lookup
where HotelCode IN
(
SELECT *
FROM DBO.ufSplit('1,2,3',',')
);
It gets back everything that I want. Am I missing something here? Why will it not return anything when passing from values with c#?
Upvotes: 0
Views: 7710
Reputation: 1514
Don't do the split at all. Create a table valued parameter and pass this to your stored procedure. Then change your stored procedure to join to the table valued parameter.
Your sproc will end up looking like this:
CREATE PROCEDURE [dbo].[uspPages_HotelPrices_Lookup_Select]
@HotelCodes dbo.MyCodesTable READONLY
AS
BEGIN
SET NOCOUNT ON;
SELECT *
FROM tPages_HotelPrices_Lookup a
INNER JOIN @HotelCodes b ON (a.ID = b.ID)
END
There are lots of good examples of using table values parameters on SO and the internet. A good method to get used to.
Upvotes: 3
Reputation: 4866
You can try doing the split in C# instead of at the db level.
string[] m_separators = new string[] { "," };
string[] m_stringarray = somestring.Split(m_separators, StringSplitOptions.RemoveEmptyEntries);
Or follow the examples on SO regarding passing an array to a stored proc. It is probably what you want to do anyway.
Upvotes: 1