Solved

Pass Comma Delimited String as Parameter to Stored Proc

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

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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
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…
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…

910 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now