Parameter conversion

Question

Post reply

Parameter conversion

aleksandra 81219

SSC Veteran

Points: 278
More actions
September 3, 2015 at 5:12 am

#302878

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')?

Viewing 15 posts - 1 through 15 (of 37 total)

You must be logged in to reply to this topic. Login to reply

patilar SSC Veteran Points: 218 More actions · Answer 1

you can use Fn_split () function . The Code for it is available in google

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 2

patilar (9/3/2015)
you can use Fn_split () function . The Code for it is available in google

Quick suggestion, don't use the Fn_split function, use DelimitedSplit8K [/url]instead

😎

aleksandra 81219 SSC Veteran Points: 278 More actions · Answer 3

aleksandra 81219

SSC Veteran

Points: 278

September 3, 2015 at 5:53 am

#1824248

Thanks for such a fast reply! 🙂

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 4

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 Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

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/

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

Eirikur Eiriksson (9/3/2015)
patilar (9/3/2015)
you can use Fn_split () function . The Code for it is available in google
Quick suggestion, don't use the Fn_split function, use DelimitedSplit8K [/url]instead
😎

For your amusement, here's one such fn_split() function.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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

Jason A. Long SSC-Insane Points: 23786 More actions · Answer 7

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;

Sean Lange SSC Guru Points: 286590 More actions · Answer 8

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 google
Quick 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/

aleksandra 81219 SSC Veteran Points: 278 More actions · Answer 9

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)

aleksandra 81219 SSC Veteran Points: 278 More actions · Answer 10

Anyone? How to pass such list of dates into one parameter?:ermm:

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 11

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 Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

aleksandra 81219 SSC Veteran Points: 278 More actions · Answer 12

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'

Jason A. Long SSC-Insane Points: 23786 More actions · Answer 13

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;

aleksandra 81219 SSC Veteran Points: 278 More actions · Answer 14

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..

_