April 2, 2010 at 7:25 am
David-Leibowitz (4/2/2010)
Optimal is relative to what you're trying to accomplish. And there are always 20 ways to skin a cat.I find it's always a balance between user needs and dev support goals.
The method works without any over engineering.
And the overhead on the type of data you pass into it...Let's be honest, if you're passing in a string of 20k items..you have report/query design issues you need to figure out separately.
My point was simply that your original post presented only one alternative - one that encourages poor design practices.
My general advice is to review the different methods available, and then make an informed choice based on the circumstances.
You seem happy enough with a multi-statement TVF and a WHILE loop...but others reading this thread now and in the future might want to take a different approach.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 2, 2010 at 8:39 am
The worry that I'd have, and I think Paul has, is that someone will read this as a good tool to use in this situation and add it to their toolbox as a "go to" solution without investigating other possibilities. I do feel that it's good that Paul pointed out there can be issues and warning to use this as a blanket method.
It's not a knock on choosing this as a tool, but appears to me that it was more of a warning that there can be performance issues with multi-statement TVFs. There is a lot of space between passing in a couple of parameters and 20k. Where and when using a TVF v other solutions probably depends on many things, and you might choose other solutions at 20 or 30 parameters.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
April 2, 2010 at 9:16 am
Paul White NZ (4/2/2010)
David-Leibowitz (4/2/2010)
Optimal is relative to what you're trying to accomplish. And there are always 20 ways to skin a cat.I find it's always a balance between user needs and dev support goals.
The method works without any over engineering.
And the overhead on the type of data you pass into it...Let's be honest, if you're passing in a string of 20k items..you have report/query design issues you need to figure out separately.
My point was simply that your original post presented only one alternative - one that encourages poor design practices.
My general advice is to review the different methods available, and then make an informed choice based on the circumstances.
You seem happy enough with a multi-statement TVF and a WHILE loop...but others reading this thread now and in the future might want to take a different approach.
Paul
Of course it's only 1 method. There are always multiple methods...isn't that what "20 ways to skin a cat means"? I didn't realize that in each article we had to provide every possible method to a solution. Will keep that in mind before the next post...
And of course people should review in context the various and appropriate methods to use. Each has its virtues and weaknesses.
You don't know what I'm "happy enough" with or understand the method of thought, so don't put words in my mouth.
Good design and "poor design" (as you call it) is within a margin of grey - it's not unilaterally within your purview. Taking a purely academic approach to design without regarding the context of the solution (any solution) is a wasted exercise.
I would think twice before crapping on a comment someone's provided next time... but that's what I'm "happy" with.
April 2, 2010 at 9:34 am
David-Leibowitz (4/2/2010)
...stuff...
Take a breath, David. It's only a forum, and people are allowed to hold differing views...:laugh:
It concerns me that you seem to have taken it all very personally...and there is really no need.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 2, 2010 at 11:04 am
I think it's worth pointing out that there are a lot of people who are here specifically because they want to find out the best ways to accomplish goals, not just possible ways. I understand the need, at times, to take a satisfactory path because time doesn't necessarily permit a more highly engineered approach. At the same time, I have an incredible appreciation for the people on this board that spend a lot of their time on these discussions to take discussions to a more detailed level.
April 9, 2010 at 2:02 am
Hi All,
Below is the solution I used to resolve my issue.
Thanks for all your help
DECLARE @Company_Codes VARCHAR(MAX)
--drop temp table
IF OBJECT_ID(N'TEMPDB.dbo.#temp',N'U') IS NOT NULL
BEGIN
DROP TABLE #temp
END
CREATE TABLE #temp (CODE VARCHAR(100))
--find company variable and add comma in this sequence
IF (CHARINDEX(',',@Company_Name_Code) > 0 )
BEGIN
SELECT @Company_Codes = 'INSERT INTO #temp SELECT ''' +
REPLACE(@Company_Name_Code,',',''' UNION ALL SELECT ''')
SET @Company_Codes = @Company_Codes + ''''
END
ELSE
BEGIN
-- If there one company is selected insert it into the #temp
INSERT INTO #temp SELECT @Company_Name_Code
END
PRINT(@Company_Codes)
EXEC(@Company_Codes)
SELECT
Data_Type_Code
, Department_Group_Code
, Company_Name_Code
, Master
, Insured
, Number
, Claim_Title
, Date_of_Loss
, Currency_Type
FROM vw_Table_Summary
WHERE Period_End=@Period_End
AND Data_Type_Code=@Data_Type_Code
AND Branch_Name_Code=@Branch_Name_Code
AND (@Department_Group_Code = '*ALL' OR Department_Group_Code = @Department_Group_Code)
AND (@Company_Name_Code = '*ALL' OR Company_Name_Code IN (SELECT FROM #temp))
AND Currency_Type = @Currency_Type
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply