Solved

Parse Delimited String Passed as Parameter to Stored Procedure

Posted on 2014-01-03
5
410 Views
Last Modified: 2014-01-03
I have the following SP that works when the parameters are hard coded.

ALTER PROCEDURE [dbo].[usp_getEODExtract]
AS
Declare @ListSymbolName VARCHAR(MAX),
 @ListOpenPrice VARCHAR(MAX),
 @List nvarchar(MAX),
 @ListItem nvarchar(10),  
 @Pos int,
 @Pos1 int,
 @ListPrice nvarchar(10)
 
--set @ListSymbolName = 'ABC,DEF,GHIJK,LMNOPQRS,T,UV,WXY,Z'

SET @List = 'ABC,DEF,GHIJK,LMNOPQRS,T,UV,WXY,Z'
SET @ListOpenPrice = '27.1,24.5,12.3,34.5,67.1,74.2,37.1,12.8'

-- Loop while the list string still holds one or more characters

WHILE LEN(@List) > 0
Begin

-------

I will be passing the parameters from c#.

     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;

cmd2.Parameters.Add("@ListOpenPrice", SqlDbType.Decimal).Value = strOpenPrice; //Convert.ToDecimal(values[2]);

        RunProcedure(cmd2, "cmdUpdate_Click");   // Execute the stored procedure
        cmd2.Dispose();

What do I need to change in my stored procedure statement?

I know that the following set statements should be removed.
SET @List = 'ABC,DEF,GHIJK,LMNOPQRS,T,UV,WXY,Z'
SET @ListOpenPrice = '27.1,24.5,12.3,34.5,67.1,74.2,37.1,12.8'

Thanks,
0
Comment
Question by:Dovberman
  • 3
5 Comments
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 39754430
You need to change the declaration:
ALTER PROCEDURE [dbo].[usp_getEODExtract]
  @List nvarchar(MAX),
 @ListOpenPrice VARCHAR(MAX)
AS
Declare @ListSymbolName VARCHAR(MAX),
  @ListItem nvarchar(10),  
 @Pos int,
 @Pos1 int,
 @ListPrice nvarchar(10)


remove your set statements and you should be good to go.

Here's a link with a working sample of both the stored proc and the C# code to call it:
http://net-informations.com/csprj/data-providers/cs-procedure-parameter.htm
0
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 500 total points
ID: 39754447
Minor correction to the above comment.  

You have the following in the VB code:
cmd2.Parameters.Add("@ListSymbolName", SqlDbType.NVarChar).Value = strSymbolName;
cmd2.Parameters.Add("@ListOpenPrice", SqlDbType.Decimal).Value = strOpenPrice;

Therefore, the stored procedure declaration of input parameters must match.  

Hence, it should be:
ALTER PROCEDURE [dbo].[usp_getEODExtract]
  @ListSymbolName NVARCHAR(MAX),
  @ListOpenPrice VARCHAR(MAX)
AS
...

Secondly, you have to change the VB declaration of parameter @ListOpenPrice.  You are passing a comma-delimited string of decimal values, so the parameter is VARCHAR not DECIMAL.
0
 

Author Comment

by:Dovberman
ID: 39754525
Thanks,

This may resolve a conversion from string to decimal error.

37.6,900,1.13,28.35,62.11,14.39,63.5,17.16,1.66,4.33,26.3,70.01,30.25,12.89,106.38,22.19,16.75,110.41,47.67,9,23.21,1.66,

I will try again.
0
 

Author Comment

by:Dovberman
ID: 39754544
You noticed a bit of detail that I had missed.

cmd2.Parameters.Add("@ListOpenPrice", SqlDbType.VarChar).Value = strOpenPrice;

// not Decimal

Thank you.
0
 

Author Closing Comment

by:Dovberman
ID: 39754545
Great attention to detail.
0

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Suggested Solutions

For those of you who don't follow the news, or just happen to live under rocks, Microsoft Research released a beta SDK (http://www.microsoft.com/en-us/download/details.aspx?id=27876) for the Xbox 360 Kinect. If you don't know what a Kinect is (http:…
Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
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…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

832 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