February 23, 2012 at 9:31 am
Hi Every One,
Select col1,col2,col3 from TableA a
left join dbo.fn_split(@parameter1,',')b on a.col1 = b.element
left join dbo.....
....
..
where a.col1 = case when @parameter1 = '' then a.col1 else b.element end
and a.col2 = case ....
and a.col3 = ...
The above mentioned is my sample coding, it works like an inner join if we pass any parameters, if we are not passing any parameters it will as it is like left join this thing i did for to avoid the dynamic..
Like that i am adding lot parameters for the same table
will it eats the performance
Thanks in Advance...
February 23, 2012 at 9:48 am
Its better to anlyze it through Query execution plan.
http://www.sql-server-performance.com/2006/query-execution-plan-analysis/
February 23, 2012 at 9:51 am
sumitagarwal781 (2/23/2012)
Its better to anlyze it through Query execution plan.http://www.sql-server-performance.com/2006/query-execution-plan-analysis/
Instead of using this code, is there any other way round....
Please....
February 23, 2012 at 9:53 am
You haven't explained what you are doing well enough for us to determine if there is another way to code this.
You'd need to provide more information on how the query is structured, and what it is intending. Left joins are different than inner joins. The addition of parameters doesn't usually change that.
February 23, 2012 at 10:01 am
Steve Jones - SSC Editor (2/23/2012)
You haven't explained what you are doing well enough for us to determine if there is another way to code this.You'd need to provide more information on how the query is structured, and what it is intending. Left joins are different than inner joins. The addition of parameters doesn't usually change that.
I am generating a report.it contains lot of filter's to display the report... they can able to select the multiple values and they are sending like a string '1,2,3,4' like this.. now what i am doing by using the split function i am putting the left join. and finally in where clause i am giving the case condition.. if they sending the values it will works like a inner join, if not it will fetch all the records from the actual table... like this i am having 15 filters like comma separated...
so for all these i am putting left join with the split function...
Help me out 🙂
February 23, 2012 at 10:21 am
I know we have a language barrier here, but you're not providing details. You should provide the procedure code, and explain more clearly (we can't see your environment), what data is being requested, what the parameters will be, and what tables will be selected from.
You need to provide more code and details, not more summaries.
February 23, 2012 at 11:34 am
will it eats the performance
To answer this question:
It depends on the code behind the split function. As long as it is not an in-line-table valued function, the answer most definitely definitely is yes.
It also looks like you're planning to design a catch-all-query[/url]. This might further degrade performance.
If you need any further assistance, please provide ready to use sample data as described in the first link in my signature together with your expected result based on those sample data.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply