September 8, 2015 at 7:31 am
AFAIU the problem is that ERP's "SQL command window" can get only string in the form
'date1','date2'..
while SQL proc needs
'date1,date2..'
and those "command window" provides no tools for string manipulation. So OP is wondering if it's possible to hadle original input string with some SQL trick.
September 8, 2015 at 7:33 am
aleksandra 81219 (9/8/2015)
So please tell how to use this function to make from 3 strings only one string which I could pass to procedure and execute it somehow 😉You have: '20150401','20150406','20150411' (this text is in erp parameter called @days, can't change it)
You must have: '20150401,20150406,20150411' (dates in one string that you could pass to procedure to its first parameter)
You have been told at least 5 or 6 times that you need to do this INSIDE your called procedure. You were even given an example of the code. Since that apparently is causing confusion let's see if we can clear this up a little bit.
Here is the process:
• The existing code executes and passes a parameter with multiple values.
• The procedure being called need to be changed to split the parameter using DelimitedSplit8K
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 8, 2015 at 7:44 am
But I said also many times that i cant execute procedure with these parameters! This is not a problem with splitting dates! I can split dates inside the procedure and it works perfectly. The problem is at the stage earlier.
Please look one more time at the execution.
execute procedure '20150101','20150106','20150111', 1 -> 4 parameters
create procedure procedure date varchar(4000), status varchar(1) -> 2 parameters
so...
execution fails
if it would be:
execute procedure '20150101,20150106,20150111', 1 -> it would be ok, but i cant change text with dates
September 8, 2015 at 7:56 am
aleksandra 81219 (9/8/2015)
But I said also many times that i cant execute procedure with these parameters! This is not a problem with splitting dates! I can split dates inside the procedure and it works perfectly. The problem is at the stage earlier.Please look one more time at the execution.
execute procedure '20150101','20150106','20150111', 1 -> 4 parameters
create procedure procedure date varchar(4000), status varchar(1) -> 2 parameters
so...
execution fails
if it would be:
execute procedure '20150101,20150106,20150111', 1 -> it would be ok, but i cant change text with dates
The real challenge here is that you haven't provided us the information so we have to keep guessing. I asked at least once how these parameters were being passed. You have to remember that we can't see your screen and have no idea what any of this stuff is doing.
It seems that the ERP code is passing a dynamic number of parameters? That is never going to work. You probably need to go back to the vendor and submit a bug report. There is no trickery you can do to get around that. You might be able to hack something together with a very long list of optional parameters as an intermediate step and kludge it back into something usable but this is not a solid or maintainable approach.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 8, 2015 at 8:24 am
At this point I think we need to see, from the profiler, exactly what's being passed and how it's being passed.
If it looks like this... EXEC dbo.MyStoredProc '2015-01-01,2015-02-02,2015-03-03', 'ps', 't'... No problem...
If it looks like this... EXEC dbo.MyStoredProc '2015-01-01','2015-02-02','2015-03-03', 'ps', 't'... That requires a bit more work.
If it looks like this... EXEC dbo.MyStoredProc 'ps', 't', '2015-01-01','2015-02-02','2015-03-03'... That requires a bit more work but less than the last one.
Until we know the answer to this, I don't think there's any more help we can give.
September 8, 2015 at 8:28 am
option 2, EXEC dbo.MyStoredProc '2015-01-01','2015-02-02','2015-03-03', 'ps', 't'
September 8, 2015 at 8:56 am
aleksandra 81219 (9/8/2015)
option 2, EXEC dbo.MyStoredProc '2015-01-01','2015-02-02','2015-03-03', 'ps', 't'
In that case, you'll need to know the maximum number of dates the ERP can throw at you... The problem is that a proc needs to have a fixed set of parameters... The saving grace is that you can default parameters so that not all of them have to be supplied (you just can't feed it more than it's expecting).
So, based on that, something like the following should do the trick for you...
DECLARE
@Param1 VARCHAR(100) = NULL,
@Param2 VARCHAR(100) = NULL,
@Param3 VARCHAR(100) = NULL,
@Param4 VARCHAR(100) = NULL,
@Param5 VARCHAR(100) = NULL,
@Param6 VARCHAR(100) = NULL,
@Param7 VARCHAR(100) = NULL,
@Param8 VARCHAR(100) = NULL,
@Param9 VARCHAR(100) = NULL;
SELECT
@Param1 = '2015-01-01',
@Param2 = '2015-02-02',
@Param3 = '2015-03-03',
@Param4 = 'ps',
@Param5 = 't';
IF OBJECT_ID('tempdb..#ParameterList') IS NOT NULL
DROP TABLE #ParameterList;
SELECT
p.ID,
p.Value
INTO #ParameterList
FROM (
VALUES (1,@Param1), (2,@Param2), (3,@Param3),(4,@Param4),(5,@Param5),(6,@Param6),(7,@Param7),(8,@Param8),(9,@Param9)
) p (ID, Value);
DECLARE
@ps VARCHAR(5),
@Status VARCHAR(5);
SELECT TOP 1 @ps = pl.Value FROM #ParameterList pl WHERE TRY_CAST(pl.Value AS DATE) IS NULL AND pl.Value IS NOT NULL ORDER BY ID;
SELECT TOP 1 @Status = pl.Value FROM #ParameterList pl WHERE TRY_CAST(pl.Value AS DATE) IS NULL AND pl.Value IS NOT NULL ORDER BY ID DESC;
SELECT
*
FROM dbo.MyTable mt
JOIN #ParameterList pl
ON mt.sth = TRY_CAST(pl.Value AS DATE)
WHERE
mt.ps = @ps
AND mt.Status = @Status
Note that I used 9 parameters in my example... If the ERP can throw 1,000 dates at once, you'll need 1,002 parameters... You get the point...
Hope this helps,
Jason
September 8, 2015 at 8:58 am
If you're saying that the application is attempting to pass 3 parameters but the stored procedure only accepts 2, then the fix is with the app. There's nothing you can do outside the stored procedure, using only T-SQL and SQL Server, to change the number of parameters being passed.
If you can't change the app, then you may need to change the proc. Have it accept 3 parameters. Or, have a wrapper proc that accepts 3, then combine them to 2 and call your original proc from within the wrapper proc.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 8 posts - 31 through 37 (of 37 total)
You must be logged in to reply to this topic. Login to reply