Dovberman
asked on
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(@ListSymbo lName , ',') f1
-- JOIN dbo.ParmsTolist(@ListOpenP rice , ',') f2
-- on f1.row_num = f2.row_num
-- WHERE f1.value is not null
-- and rtrim(f1.value) <> ''
Would you offer further assistance?
Thanks
-- INSERT INTO StockDataWork
-- (SymbolName, OpenPrice)
--SELECT f1.value, f2.value
-- FROM dbo.ParmsTolist(@ListSymbo
-- JOIN dbo.ParmsTolist(@ListOpenP
-- on f1.row_num = f2.row_num
-- WHERE f1.value is not null
-- and rtrim(f1.value) <> ''
Would you offer further assistance?
Thanks
ASKER
The details are in 2014-01-02 at 10:27:29ID28329183
How can this previous question be displayed?
Thanks
How can this previous question be displayed?
Thanks
ASKER
How can I bring this to the attention of the person who helped me?
Can I reopen the closed question?
Can I reopen the closed question?
Provide some sample data with expected result.
post the link for your previous question.
post the link for your previous question.
ASKER
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)
https://www.experts-exchange.com/questions/28329183/Pass-Comma-Delimited-String-as-Parameter-to-Stored-Proc.html
Thanks,
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)
https://www.experts-exchange.com/questions/28329183/Pass-Comma-Delimited-String-as-Parameter-to-Stored-Proc.html
Thanks,
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?
Have you tried testing the individual parts of the SP in query analyser to see what works and what doesn't?
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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(@ListSymbo lName , ',') f1
JOIN dbo.ParmsTolist(@ListOpenP rice , ',') 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.
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(@ListSymbo
JOIN dbo.ParmsTolist(@ListOpenP
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.
ASKER
Query failed when passing paramaeters.
Stack Trace:
at System.Data.SqlClient.SqlP arameter.C oerceValue (Object value, MetaType destinationType, Boolean& coercedToDataFeed, Boolean& typeChanged, Boolean allowStreaming)
at System.Data.SqlClient.SqlP arameter.G etCoercedV alue()
at System.Data.SqlClient.SqlP arameter.V alidate(In t32 index, Boolean isCommandProc)
at System.Data.SqlClient.SqlC ommand.Set UpRPCParam eters(_Sql RPC rpc, Int32 startCount, Boolean inSchema, SqlParameterCollection parameters)
at System.Data.SqlClient.SqlC ommand.Bui ldRPC(Bool ean inSchema, SqlParameterCollection parameters, _SqlRPC& rpc)
at System.Data.SqlClient.SqlC ommand.Run ExecuteRea derTds(Com mandBehavi or cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)
at System.Data.SqlClient.SqlC ommand.Run ExecuteRea der(Comman dBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
at System.Data.SqlClient.SqlC ommand.Int ernalExecu teNonQuery (TaskCompl etionSourc e`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite)
at System.Data.SqlClient.SqlC ommand.Exe cuteNonQue ry()
at StockProMax.Admin.Maintain DataDaily. RunProcedu re(SqlComm and cmd, String strProcName) in c:\Users\Dovberman\Documen ts\Visual Studio 2012\Projects\StockPickerM ax\StockPi ckerMax\Ad min\Mainta inDataDail y.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.
Stack Trace:
at System.Data.SqlClient.SqlP
at System.Data.SqlClient.SqlP
at System.Data.SqlClient.SqlP
at System.Data.SqlClient.SqlC
at System.Data.SqlClient.SqlC
at System.Data.SqlClient.SqlC
at System.Data.SqlClient.SqlC
at System.Data.SqlClient.SqlC
at System.Data.SqlClient.SqlC
at StockProMax.Admin.Maintain
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
strHighPrice = strHighPrice + Convert.ToDecimal(values[3
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
ASKER
Thanks to everyone
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.