Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 425
  • Last Modified:

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,
0
Dovberman
Asked:
Dovberman
  • 7
  • 5
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
0
 
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

0
 
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/
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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,
0
 
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

0
 
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) <> '')
0
 
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

0
 
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.
0
 
DovbermanAuthor Commented:
Your help has reduced a 15 minute query down to 10 seconds.

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

Resolved using a different technique.
0
 
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
0
 
DovbermanAuthor Commented:
Thank you again
0
 
DovbermanAuthor Commented:
Guy Hengel,

Your suggestion indeed was the best.

My solution is simply an alternative.

How can I clear this up?

Thanks
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 7
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now