Solved

Pass Comma Delimited String as Parameter to Stored Proc

Posted on 2014-01-02
14
408 Views
Last Modified: 2014-01-05
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,
0
Comment
Question by:Dovberman
  • 7
  • 5
14 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39751569
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39751578
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

0
 
LVL 13

Expert Comment

by:Jesus Rodriguez
ID: 39751681
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/
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

Author Comment

by:Dovberman
ID: 39751881
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,
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39751895
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

0
 

Author Comment

by:Dovberman
ID: 39751979
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) <> '')
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 39752000
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

0
 

Author Comment

by:Dovberman
ID: 39752036
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.
0
 

Author Comment

by:Dovberman
ID: 39752038
Your help has reduced a 15 minute query down to 10 seconds.

Thank you.
0
 

Author Comment

by:Dovberman
ID: 39756215
I've requested that this question be deleted for the following reason:

Resolved using a different technique.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39756216
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
0
 

Author Closing Comment

by:Dovberman
ID: 39757208
Thank you again
0
 

Author Comment

by:Dovberman
ID: 39757588
Guy Hengel,

Your suggestion indeed was the best.

My solution is simply an alternative.

How can I clear this up?

Thanks
0

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question