Solved

Pass Comma Delimited String as Parameter to Stored Proc

Posted on 2014-01-02
14
413 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 143

Expert Comment

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

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
Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

 

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 143

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 143

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 143

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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

User art_snob (http://www.experts-exchange.com/M_6114203.html) encountered strange behavior of Android Web browser on his Mobile Web site. It took a while to find the true cause. It happens so, that the Android Web browser (at least up to OS ver. 2.…
A theme is a collection of property settings that allow you to define the look of pages and controls, and then apply the look consistently across pages in an application. Themes can be made up of a set of elements: skins, style sheets, images, and o…
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…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

740 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