Parameter conversion
-
aleksandra 81219 (9/4/2015)
Hmmm so next procedure to execute first procedure? I would prefer to avoid such step because its embedded next in erp system... What is more now we have only 2 days, but what when there will be more.._
I don't you understand what he is saying. You would receive the parameter of delimited values and split it right in your proc. There is no need for another proc to do the split.
This to me sounds like the perfect time to use a table valued parameter as previously suggested.
_______________________________________________________________
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/ -
aleksandra 81219 (9/4/2015)
Hmmm so next procedure to execute first procedure? I would prefer to avoid such step because its embedded next in erp system... What is more now we have only 2 days, but what when there will be more.._
NO... ALTER the existing procedure.
-
You didnt understand correctly, I can't EXECUTE procedure so modyfing it wont help
See what is underlined on my printcreen, i cant change how it is passed - im getting: 'sth1','sth2' - its like 2 parameters
-
aleksandra 81219 (9/4/2015)
You dont understood correctly, I can't EXECUTE procedure so modyfing it wont helpSee what is underlined on my printcreen, i cant change how it is passed - im getting: 'sth1','sth2' - its like 2 parameters
That is because you have a scalar variable but you are trying to assign two strings as the value.
You need to do it like this if you want to maintain the quotes in the variable.
@days= N'''20150401'',''20150406'''
And if you don't need the quotes it is like this.
@days= N'20150401,20150406'
Where you need to add the split logic is INSIDE the procedure you are calling.
Also, it looks like that procedure is likely using the RETURN_VALUE to return a value from the procedure. You should use an OUTPUT parameter instead. The return value from a procedure is used to indicate the status of the execution, not return a value.
_______________________________________________________________
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/ -
I cant add or reduce quotes......... Its passed from system.... im always getting '20150106','20150101', a can add sth on beginning or end, add function or sth, but it will always be '20150106','20150101', never '20150106,20150101' or sth else
I CAN split it inside procedure, i cant only execute it with such "two" parameters
-
aleksandra 81219 (9/4/2015)
I cant add or reduce quotes......... Its passed from system.... im always getting '20150106','20150101', a can add sth on beginning or end, add function or sth, but it will always be '20150106','20150101', never '20150106,20150101' or sth elseI CAN split it inside procedure, i cant only execute it with such "two" parameters
This is not at all clear. You have a variable being passed in and the contents of that variable are delimited string? If so, then you need to split that parameter when you receive it. Not sure what the challenge is here.
_______________________________________________________________
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/ -
Starting to feel a bit like this...
-
Sean Lange (9/4/2015)
You have a variable being passed in and the contents of that variable are delimited string? .
No, I have n variables from erp system and these n variables (for example 21 different separate dates, not one string!) have to be passed by ONE parameter in procedure (and then splitted fo course to use them in code, that is not a problem) so the problem is how to JOIN separate dates into one comma separated string
-
Ok... Here's yor original post...
aleksandra 81219 (9/3/2015)
Hi! I have some T-sql procedures and function witch are getting from system sth like this@days='20150401,20150406'
In my code many times i have to use where sth in @days
When i try to make sth in (@days) im getting wrong data because it means: sth in ('20150401,20150406')
Could you please help me to change it somehow to do: sth in ('20150401','20150406')?
What you have depicted here, is a single string... And... It's easy enough to contend with.
So... You have two options...
1) Dynamic SQL... Overkill for something like this...
2) Split the string in in the code. Based on your last comment, you want something like the following...
CREATE PROCEDURE dbo.MyProcedure
@days CHAR(17),
@ps CHAR(2),
@status CHAR(1)
AS
BEGIN
SET NOCOUNT ON;
SELECT
*
FROM
dbo.MyTable mt
WHERE
mt.sth IN (SELECT CAST(Item AS DATE) FROM dbo.DelimitedStlit8k(@days, ',') )
AND mt.SomeOtherColumn = @ps
AND mt.Status = @status;
END;
-
aleksandra 81219 (9/4/2015)
Sean Lange (9/4/2015)
You have a variable being passed in and the contents of that variable are delimited string? .
No, I have n variables from erp system and these n variables (for example 21 different separate dates, not one string!) have to be passed by ONE parameter in procedure (and then splitted fo course to use them in code, that is not a problem) so the problem is how to JOIN separate dates into one comma separated string
I strongly suspect a linguistics problem here, in that your English just isn't quite good enough to EASILY and ACCURATELY describe the problem to a largely English-speaking audience, but it's hard to tell because some of the things you've posted appear to be very clear, but then you'll post something that appears to be contradictory. I'm going to guess that what you need is a way to populate this parameter with a comma-delimited list of dates, which is rather hard to determine from your original post, and only started to appear to be the case based on the post I'm quoting here. What you seem to need is something like this:
DECLARE @STH AS varchar(1000) = '(';
SELECT @STH = @STH + '''' + REPLACE(CONVERT(char(10), T.SomeDate, 112), '-', '') + ''','
FROM dbo.SomeTable AS T;
SET @STH = LEFT(@STH, LEN(@STH) - 1) + ')';
Let me know if I'm on the right track.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) -
sgmunson (9/4/2015)
some of the things you've posted appear to be very clear, but then you'll post something that appears to be contradictory.
And now i see where is the problem and why you think that some things apear to be contradictory.
It is not the same case as in the first, original post! It is second case about second completely different problem with more then one date passed to one parameter
-
aleksandra 81219 (9/4/2015)
No, I have n variables from erp system and these n variables (for example 21 different separate dates, not one string!) have to be passed by ONE parameter in procedure (and then splitted fo course to use them in code, that is not a problem) so the problem is how to JOIN separate dates into one comma separated string
And now i see where is the problem and why you think that some things apear to be contradictory.
It is not the same case as in the first, original post! It is second case about second completely different problem with more then one date passed to one parameter
Ok, which way ERP module passes n parameters to SQL code ( function, procedure) ? Are you free to define this way or it's totally fixed and can not be altered?
-
Part with dates is totally fixed. Its always list of separate dates: 'date1','date2','date3',... because these dates are passed by a parameter. For example @dates. I cant modify what is in this parameter.
There is sth like "command window" to execute procedures. So what i can is to write directly Execute procedure and give it a parameter @dates. But where the problem is? @dates is a list of dates, so its like few parameters, not one so execute procedure fails.
-
aleksandra 81219 (9/7/2015)
Part with dates is totally fixed. Its always list of separate dates: 'date1','date2','date3',... because these dates are passed by a parameter. For example @dates. I cant modify what is in this parameter.There is sth like "command window" to execute procedures. So what i can is to write directly Execute procedure and give it a parameter @dates. But where the problem is? @dates is a list of dates, so its like few parameters, not one so execute procedure fails.
You have been presented with the best way to handle this repeatedly, DelimitedSplit8K. Is there some reason this won't work? At this point we are just going around and around in circle with no clarity on the problem.
_______________________________________________________________
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/ -
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)
Viewing 15 posts - 16 through 30 (of 37 total)
You must be logged in to reply to this topic. Login to reply