Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Pass Comma Delimited String as Parameter to Stored Proc

Posted on 2014-01-02
13
Medium Priority
?
416 Views
Last Modified: 2014-01-03
I thought this was resolved. The stored procedure compiled but did not run.

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

Would you offer further assistance?

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
  • 8
  • 4
13 Comments
 
LVL 21

Expert Comment

by:Dale Burrell
ID: 39752324
Well maybe you could offer us further information about what is actually happening!

We don't any idea of what your data is, nor what the code of your SP is, nor what the code of your function ParmsTolist is, nor what testing/validating you have done.
0
 

Author Comment

by:Dovberman
ID: 39752352
The details are in 2014-01-02 at 10:27:29ID28329183

How can this previous question be displayed?

Thanks
0
 

Author Comment

by:Dovberman
ID: 39752358
How can I bring this to the attention of the person who helped me?

Can I reopen the closed question?
0
Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
LVL 41

Expert Comment

by:Sharath
ID: 39752372
Provide some sample data with expected result.

post the link for your previous question.
0
 

Author Comment

by:Dovberman
ID: 39752438
Everything is here

I wish to use a comma delimited lists as a input parameters to a stored procedure.

Within the stored procedure, I need to parse the lists.
Each list has 1800 values.

@SymbolNameList:  "AA.P, AADR,"
@OpenPriceList:  "76.55,37.6"

For Each value in the lists
INSERT INTO tablename (SymbolName, OpenPrice)
VALUES( @SymbolNameList.Item, OpenPriceList.Item)

http://www.experts-exchange.com/Programming/Languages/.NET/ASP.NET/Q_28329183.html

Thanks,
0
 
LVL 21

Expert Comment

by:Dale Burrell
ID: 39752455
So what has changed? You said it reduced a 15m query down to 10s - so it clearly worked then, but stopped?

Have you tried testing the individual parts of the SP in query analyser to see what works and what doesn't?
0
 

Author Comment

by:Dovberman
ID: 39752599
It worked until I used the Join.

The query using the Join compiled but did not run.  It failed when I tested it from the c# code.
My fault for thinking that it worked.

I will test the individual parts in the query analyser.

How can I reopen the question ?

Thanks
0
 
LVL 21

Accepted Solution

by:
Dale Burrell earned 1500 total points
ID: 39752876
Now we know what question you are referring to, its fine.

The following code works for me. If its not working for you, either you have a mistake in your version of dbo.ParmsTolist, or your data you are passing in is different to what you have written.

I suggest you post the results you get when you run the same query below.

declare @SymbolNameList varchar(max), @OpenPriceList varchar(max)

set @SymbolNameList = 'AA.P,AADR'
set @OpenPriceList =  '76.55,37.6'

select * from dbo.ParmsTolist(@ListSymbolName, ',') f1
select * from dbo.ParmsTolist(@ListOpenPrice, ',') f2

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: 39753218
This works when the parameter names in the declare statement match the parameter names in the SELECT statement.

However this is not the issue.

The following works when the declared parameters are set:

declare @ListSymbolName varchar(max), @ListOpenPrice varchar(max)

set @ListSymbolName = 'AA.P,AADR,'
set @ListOpenPrice =  '76.55,37.6,'

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

The query fails when the parameters are passed from my c# code.

--set @ListSymbolName = 'AA.P,AADR,'
--set @ListOpenPrice =  '76.55,37.6,'


I will test again and send you the results.
0
 

Author Comment

by:Dovberman
ID: 39753247
Query failed when passing paramaeters.

Stack Trace:
   at System.Data.SqlClient.SqlParameter.CoerceValue(Object value, MetaType destinationType, Boolean& coercedToDataFeed, Boolean& typeChanged, Boolean allowStreaming)
   at System.Data.SqlClient.SqlParameter.GetCoercedValue()
   at System.Data.SqlClient.SqlParameter.Validate(Int32 index, Boolean isCommandProc)
   at System.Data.SqlClient.SqlCommand.SetUpRPCParameters(_SqlRPC rpc, Int32 startCount, Boolean inSchema, SqlParameterCollection parameters)
   at System.Data.SqlClient.SqlCommand.BuildRPC(Boolean inSchema, SqlParameterCollection parameters, _SqlRPC& rpc)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at StockProMax.Admin.MaintainDataDaily.RunProcedure(SqlCommand cmd, String strProcName) in c:\Users\Dovberman\Documents\Visual Studio 2012\Projects\StockPickerMax\StockPickerMax\Admin\MaintainDataDaily.aspx.cs:line 897

Error:

Failed to convert parameter value from a String to a Decimal.

I need to build the ListOpenPrice values to decimal.

I tried this;

strSymbolName = strSymbolName + values[0] + strComma;
                strOpenPrice = strOpenPrice + Convert.ToDecimal(values[2]) + strComma;
                strHighPrice = strHighPrice + Convert.ToDecimal(values[3]) + strComma;

Same error: Failed to convert parameter value from a String to a Decimal.

How do I resolve this?

Evidently I need to Cast in the stored proc.
0
 
LVL 21

Assisted Solution

by:Dale Burrell
Dale Burrell earned 1500 total points
ID: 39753287
I'd need to see your code to be able to have any idea why you are getting an error like that. But either your SP has the wrong datatype set for the parameter. Or you are creating a parameter in code of the wrong datatype. Or you really are trying to assign the wrong value to the parameter.

You should be able to step through your code and debug it?
0
 

Author Comment

by:Dovberman
ID: 39754121
Stepping thru does not help.

SQL Server is seeing the following as a string: "34.56" , notices the embedded decimal point and tries to handle the string as an unconverted decimal data type.
0
 

Author Closing Comment

by:Dovberman
ID: 39754559
Thanks to everyone
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
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…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

610 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