Passing comma delimited string to stored procedure?

Question

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#?

You're not showing the code where you call the stored procedure from C#. — Dustin Kingen, Nov 04 '13 at 16:19
@MarcB: true - but it's better if we don't have to **guess** and instead are **told** by the OP who know what he's using .... — marc_s, Nov 04 '13 at 16:19
Are you sure that `1,2,3` is getting passed into the proc? Try debugging your C# code first to make sure that's the actual value, and also you can temporarily (or permanently) modify your proc so that it logs the parameter value that's passed in. Just to make sure that there's nothing funky going on there. — Joe Enos, Nov 04 '13 at 16:20
you can also try tracing what SQL is generated via [SQL profiler](http://technet.microsoft.com/en-us/library/ms181091%28v=sql.105%29.aspx) — Paolo Falabella, Nov 04 '13 at 16:23
Thank you using SQL Profiler really helped, there was some odd apostrophies being added, but I modified my code and its working now thank you all for your help! — Srb1313711, Nov 04 '13 at 16:41
Instead of passing a comma-separated string, pass a table valued parameter — Panagiotis Kanavos, Nov 04 '13 at 16:41
http://stackoverflow.com/questions/10405373/insert-entire-datatable-into-database-at-once-instead-of-row-by-row/10405622#10405622 — Aaron Bertrand, Nov 04 '13 at 17:09
If your code is now working, please do post your solution and accept it. Thanks :) — quinekxi, Jan 24 '14 at 16:44

score 3 · Accepted Answer · answered Nov 04 '13 at 16:41

3

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.

answered Nov 04 '13 at 16:41

SteveB

1,474
1
13
21

Thank you for your answer It is now working but I will look it to using this method instead, what is a table valued parametre? – Srb1313711 Nov 04 '13 at 16:43
1

It's a table that can be declared in both .net and sql server and it allows you to pass lists of IDs in the manner you want. At the sql end you can then join directly to the table. Have a look on the internet and find some examples - I'm currently working on a project that uses them quite a lot and I've been pleasantly surprised at how easy they are to use. Some caveats but on the whole a +ve experience. Enjoy :-) – SteveB Nov 04 '13 at 16:48

score 1 · Answer 2 · answered Nov 04 '13 at 16:36

1

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.

answered Nov 04 '13 at 16:36

smoore4

4,520
3
36
55

Thank you for responding now but I got it sorted – Srb1313711 Nov 04 '13 at 16:41

Passing comma delimited string to stored procedure?

2 Answers2