Pass Comma Delimited String as Parameter to Stored Proc

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?

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale BurrellDirectorCommented:
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.
DovbermanAuthor Commented:
The details are in 2014-01-02 at 10:27:29ID28329183

How can this previous question be displayed?

DovbermanAuthor Commented:
How can I bring this to the attention of the person who helped me?

Can I reopen the closed question?
OWASP: Avoiding Hacker Tricks

Learn to build secure applications from the mindset of the hacker and avoid being exploited.

SharathData EngineerCommented:
Provide some sample data with expected result.

post the link for your previous question.
DovbermanAuthor Commented:
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)

Dale BurrellDirectorCommented:
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?
DovbermanAuthor Commented:
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 ?

Dale BurrellDirectorCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DovbermanAuthor Commented:
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,'

      (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.
DovbermanAuthor Commented:
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


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.
Dale BurrellDirectorCommented:
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?
DovbermanAuthor Commented:
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.
DovbermanAuthor Commented:
Thanks to everyone
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.