September 3, 2015 at 5:12 am
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')?
September 3, 2015 at 5:28 am
you can use Fn_split () function . The Code for it is available in google
September 3, 2015 at 5:36 am
September 3, 2015 at 5:53 am
Thanks for such a fast reply! π
September 3, 2015 at 6:54 am
Another vote for using Jeff's tally table method. It's going to work so much better.
----------------------------------------------------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
September 3, 2015 at 7:13 am
Here is vote #3 for the DelimitedSplit8K function. You will notice there is a link to it in my signature. π
_______________________________________________________________
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 3, 2015 at 7:45 am
Eirikur Eiriksson (9/3/2015)
patilar (9/3/2015)
you can use Fn_split () function . The Code for it is available in googleQuick suggestion, don't use the Fn_split function, use DelimitedSplit8K [/url]instead
π
For your amusement, here's one such fn_split() function.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 3, 2015 at 8:15 am
Yet another vote for the DelimitedSplit8k function... If a split function is needed...
IF the parameter is ALWAYS going to contain just the 2 dates in that EXACT format, the following will work without the need of a split function...
DECLARE
@days CHAR(17) ='20150401,20150406',
@BegDate DATE,
@EndDate DATE;
SELECT
@BegDate = SUBSTRING(@days, 1, 8),
@EndDate = SUBSTRING(@days, 10, 8);
SELECT @BegDate AS BegDate, @EndDate AS EndDate;
September 3, 2015 at 8:27 am
ChrisM@Work (9/3/2015)
Eirikur Eiriksson (9/3/2015)
patilar (9/3/2015)
you can use Fn_split () function . The Code for it is available in googleQuick suggestion, don't use the Fn_split function, use DelimitedSplit8K [/url]instead
π
For your amusement, here's one such fn_split() function.
YIKES!!! :w00t::w00t::w00t:
_______________________________________________________________
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 4, 2015 at 12:52 am
Thank you all;) I have used DelimitedSplit8K function because i have already used it once previously π
One more case. What if Im getting such parameters?
EXECUTE [dbo].[procedure]
@days = '20140101', '20140106',
@ps = N'ps',
@status = N't'
I have 3 parameters but if I would execute it in this way, db thinks i try to pass values for 4 parameters. I cant change way of passing, the only I can do is encapsulate it somehow, for example ('20140101', '20140106') and/or change sth in procedure
And of course I have to use it as previously with ('20150401','20150406') in my sql where conditions...
Procedure gets varchars: procedure [dbo].[procedure]@days varchar(4000)
In the code I have both a cursor to use each value separately and code where i need to use in (@days)
September 4, 2015 at 3:31 am
Anyone? How to pass such list of dates into one parameter?:ermm:
September 4, 2015 at 5:33 am
I would split those the same way. An alternate method is to use a table valued parameter.
----------------------------------------------------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
September 4, 2015 at 5:39 am
Cant split it in the same way because i cant execute procedure as it has wrong number of parameters then, I have to change sth during executing it because im getting multiple values into one parameter so i have not to split it but firstly join
I am executing the proc in ERP system to cant put there 'full code'
The only what is probably working is some standard commands, for example
DECLARE @return_value int
EXEC @return_value = [dbo].[procedure]
@days= N'20150401','20150406'
@ps = N'ps',
@status= N'1'
SELECT 'Return Value' = @return_value
So I have to concat some values
Im getting: 'a','b','c','d'
I must have 'a,b,c,d'
September 4, 2015 at 6:59 am
Your need to split the parameter inside the proc... As a part of the proc's code.
Something like this...
CREATE PROCEDURE dbo.MyProcedure
@days CHAR(17),
@ps CHAR(2),
@status CHAR(1)
AS
BEGIN
SET NOCOUNT ON;
DECLARE
@date1 DATE,
@date2 DATE;
SELECT
@date1 = MAX(CASE WHEN sc.ItemNumber = 1 THEN sc.Item END),
@date2 = MAX(CASE WHEN sc.ItemNumber = 2 THEN sc.Item END)
FROM
dbo.DelimitedSplit8K (@days, ',') sc;
SELECT
*
FROM
dbo.MyTable mt
WHERE
mt.SomeDate >= @date1
AND mt.SomeDate < dateadd(dd, 1, @date2)
AND mt.SomeOtherColumn = @ps
AND mt.Status = @status;
END;
September 4, 2015 at 7:06 am
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..
_
Viewing 15 posts - 1 through 15 (of 37 total)
You must be logged in to reply to this topic. Login to reply