Solved

Parse Delimited String Passed as Parameter to Stored Procedure

Posted on 2014-01-03
5
417 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 60

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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

ASP.Net to Oracle Connectivity Recently I had to develop an ASP.NET application connecting to an Oracle database.As I am doing it first time ,I had to solve several problems. This article will help to such developers  to develop an ASP.NET client…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
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…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

687 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