• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 426
  • Last Modified:

Parse Delimited String Passed as Parameter to Stored Procedure

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
Dovberman
Asked:
Dovberman
  • 3
1 Solution
 
Kyle AbrahamsSenior .Net DeveloperCommented:
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
 
Kevin CrossChief Technology OfficerCommented:
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
 
DovbermanAuthor Commented:
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
 
DovbermanAuthor Commented:
You noticed a bit of detail that I had missed.

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

// not Decimal

Thank you.
0
 
DovbermanAuthor Commented:
Great attention to detail.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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