Pass multi values into procedures

Question

Pass multi values into procedures

Viewing 6 posts - 16 through 20 (of 20 total)

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

Paul White SSC Guru Points: 150467 More actions · Answer 1

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

Steve Jones - SSC Editor SSC Guru Points: 736193 More actions · Answer 2

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.

David-Leibowitz SSChasing Mays Points: 610 More actions · Answer 3

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.

SQLScrubs: SSRS Audit, Optimization & Management reports

Paul White SSC Guru Points: 150467 More actions · Answer 4

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

Ben Teraberry SSChampion Points: 11845 More actions · Answer 5

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.

└> bt

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

felix-659856 SSC Veteran Points: 291 More actions · Answer 6

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