Pass Comma Delimited String as Parameter to Stored Proc

I can build and pass a comma delimited string to a stored procedure.

This is the strSymbolName string:
"AADR,AAMC,AAU,ACCU,ACIM,ACU,ACWV,ACY,ADGE,"

This is the stored procedure:

ALTER PROCEDURE [dbo].[usp_getEODExtract]
@ListSymbolName VARCHAR(MAX)

This is how I pass the parameter:

SqlCommand cmd2 = new SqlCommand("usp_getEODExtract", pconStockSelect);
        cmd2.CommandTimeout = 360;
        cmd2.CommandType = System.Data.CommandType.StoredProcedure;
        cmd2.CommandText = "usp_getEODExtract";
        cmd2.Parameters.Clear();
        cmd2.Parameters.Add("@ListSymbolName", SqlDbType.NVarChar).Value = strSymbolName;

I need to parse the string and

INSERT INTO StockDataWork
      (SymbolName)
      VALUES(@SymbolName)  for each substring of @ListSymbolName

I have seen this done in examples, but cannot find them now.

Please help.

Thanks,
DovbermanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
Guy Hengel [angelIII / a3]Billing EngineerCommented:
the code of the stored procedure would become something like this:
INSERT INTO StockDataWork
      (SymbolName)
SELECT f.value
  FROM dbo.ParmsTolist(@ListSymbolName , ',') f
  where f.value is not null
    and rtrim(f.value) <> '' 

Open in new window

Jesus RodriguezIT ManagerCommented:
or you can do something like this in your store procedure after passing the variable

DECLARE @your_chain nvarchar(max),@str varchar(100),@delimiter varchar(10)
SET @str= '1,2,3,4,5,6,7,8,9,10,11,12,13,14,15'
SET @delimiter =','
SET @your_chain= ''''+replace(@str,@delimiter ,''',''')+''''
SELECT @your_chain

also look at this example that do the same as you need
http://rmanimaran.wordpress.com/2010/09/24/insert-comma-separated-values-to-a-table-in-a-single-insert/
OWASP: Forgery and Phishing

Learn the techniques to avoid forgery and phishing attacks and the types of attacks an application or network may face.

DovbermanAuthor Commented:
Guy Hengle

This works:

ALTER PROCEDURE [dbo].[usp_getEODExtract]
@ListSymbolName VARCHAR(MAX),
@ListOpenPrice VARCHAR(MAX),
@ListHighPrice VARCHAR(MAX),
@QuoteDate datetime
 
AS

BEGIN
   -- SET NOCOUNT ON added to prevent extra result sets from
   -- interfering with SELECT statements.
      SET NOCOUNT ON;
      
      INSERT INTO StockDataWork
      (SymbolName)
SELECT f.value
  FROM dbo.ParmsTolist(@ListSymbolName , ',') f
  where f.value is not null
    and rtrim(f.value) <> ''

      INSERT INTO StockDataWork
      (SymbolName,OpenPrice)

--Will this work when I include the @ListOpenPrice parameter?
-- How do I account for insertion of a second column in StockDataWork?

SELECT f.value
  FROM dbo.ParmsTolist(@ListOpenPrice , ',') f
  where f.value is not null
    and rtrim(f.value) <> ''


END

Thanks,
Guy Hengel [angelIII / a3]Billing EngineerCommented:
yes, presuming the prices are given in the same order, and even the same number of items
  INSERT INTO StockDataWork
      (SymbolName, OpenPrice)
SELECT f1.value, f2.value
  FROM dbo.ParmsTolist(@ListSymbolName , ',') f1
  JOIN dbo.ParmsTolist(@ListOpenPrice , ',') f2
    on f1.row_num = f2.row_num
 WHERE f1.value is not null
    and rtrim(f1.value) <> ''

Open in new window

DovbermanAuthor Commented:
Yes, SymbolName can be used as a key  and is never null.  
There are 6 columns including the SymbolName column.

Can I use additional INSERT INTO statements as follows?

 INSERT INTO StockDataWork
      (SymbolName, OpenPrice)
SELECT f1.value, f2.value
  FROM dbo.ParmsTolist(@ListSymbolName , ',') f1
  JOIN dbo.ParmsTolist(@ListOpenPrice , ',') f2
    on f1.row_num = f2.row_num
 WHERE f1.value is not null
    and rtrim(f1.value) <> ''

There are 4 more parameters to update.
I tried this, but it needs a WHERE statement.

UPDATE StockDataWork
      SET HighPrice =
( SELECT f1.value, f2.value
  FROM dbo.ParmsTolist(@ListSymbolName , ',') f1
  JOIN dbo.ParmsTolist(@ListHighPrice , ',') f2
    on f1.row_num = f2.row_num
 WHERE f1.value is not null
    and rtrim(f1.value) <> '')
Guy Hengel [angelIII / a3]Billing EngineerCommented:
for each additional values, you add a join:
 INSERT INTO StockDataWork
      (SymbolName, OpenPrice, HighPrice)
SELECT f1.value, f2.value, f3.value
  FROM dbo.ParmsTolist(@ListSymbolName , ',') f1
  JOIN dbo.ParmsTolist(@ListOpenPrice , ',') f2
    on f1.row_num = f2.row_num
  JOIN dbo.ParmsTolist(@ListHighPrice , ',') f3
    on f1.row_num = f3.row_num
 WHERE f1.value is not null
    and rtrim(f1.value) <> '' 

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DovbermanAuthor Commented:
I was hoping that multiple joins could be used.

The SP compiled.

I know that I will need to CAST f2..value and f3.value as Decimal(8.2)

Your help has reduced a 15 minute query down to 10 seconds.

Thank you.
DovbermanAuthor Commented:
Your help has reduced a 15 minute query down to 10 seconds.

Thank you.
DovbermanAuthor Commented:
I've requested that this question be deleted for the following reason:

Resolved using a different technique.
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I think I have to disagree on deletion.
you accepted by saying the time was taken down from 15 minutes down to 10 seconds, and even if you did find another technique, it doesn't make my suggestion invalid
DovbermanAuthor Commented:
Thank you again
DovbermanAuthor Commented:
Guy Hengel,

Your suggestion indeed was the best.

My solution is simply an alternative.

How can I clear this up?

Thanks
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.