May 24, 2017 at 12:23 pm
Did anyone come across this scenario where a developer/analyst comes in and pass multiple values for Parameter so he/she can see the data as a table with bunch of fields.
May 24, 2017 at 12:28 pm
wweraw25 - Wednesday, May 24, 2017 12:23 PMDid anyone come across this scenario where a developer/analyst comes in and pass multiple values for Parameter so he/she can see the data as a table with bunch of fields.
I don't fully understand what you mean. Can you post an example?
May 24, 2017 at 1:57 pm
Something like below:
Exec dbo. Sp_Number '1,2,3,4,5'
Result Set
1 USA Chicago
2 UK England
3 AUS Melbourne
4 CAN Halifax
5 UAE Dubai
1,2,3,4,5 are values for single parameter
May 24, 2017 at 3:31 pm
wweraw25 - Wednesday, May 24, 2017 1:57 PMSomething like below:Exec dbo. Sp_Number '1,2,3,4,5'
Result Set
1 USA Chicago
2 UK England
3 AUS Melbourne
4 CAN Halifax
5 UAE Dubai1,2,3,4,5 are values for single parameter
Check the function in the following article. It will split the parameters into rows.
http://www.sqlservercentral.com/articles/Tally+Table/72993/
May 24, 2017 at 10:13 pm
Here's reference for User-Defined Table Types:
https://technet.microsoft.com/en-us/library/bb522526%28v=sql.105%29.aspx
https://docs.microsoft.com/en-us/sql/relational-databases/tables/use-table-valued-parameters-database-engine
May 26, 2017 at 9:33 am
wweraw25 - Wednesday, May 24, 2017 1:57 PMSomething like below:Exec dbo. Sp_Number '1,2,3,4,5'
Result Set
1 USA Chicago
2 UK England
3 AUS Melbourne
4 CAN Halifax
5 UAE Dubai1,2,3,4,5 are values for single parameter
You need to read a book on RDBMS. In particular, pay attention to the normal forms, which are the foundation of SQL. The first normal form (1NF) assumes that all values in the columns of a table are scalars. We do not have repeated groups or structured data of any kind. You're trying to put in a sequential list structure! If you can prove you're smarter than Dr. Codd, you might be worth listening to.
What you want to use is called non-first normal form databases. PICK is probably the most common product of this kind of database. If you don't care about doing things right, you can kludges with vendor extensions which will not port and that have horrible performance.
Please post DDL and follow ANSI/ISO standards when asking for help.
May 26, 2017 at 9:41 am
jcelko212 32090 - Friday, May 26, 2017 9:33 AMwweraw25 - Wednesday, May 24, 2017 1:57 PMSomething like below:Exec dbo. Sp_Number '1,2,3,4,5'
Result Set
1 USA Chicago
2 UK England
3 AUS Melbourne
4 CAN Halifax
5 UAE Dubai1,2,3,4,5 are values for single parameter
You need to read a book on RDBMS. In particular, pay attention to the normal forms, which are the foundation of SQL. The first normal form (1NF) assumes that all values in the columns of a table are scalars. We do not have repeated groups or structured data of any kind. You're trying to put in a sequential list structure! If you can prove you're smarter than Dr. Codd, you might be worth listening to.
What you want to use is called non-first normal form databases. PICK is probably the most common product of this kind of database. If you don't care about doing things right, you can kludges with vendor extensions which will not port and that have horrible performance.
There's no violation of 1NF here. The OP is asking how to send multiple values in a single parameter with each item be compared to scalar values in a table.
July 5, 2017 at 1:12 pm
wweraw25 - Wednesday, May 24, 2017 1:57 PM
There's no violation of 1NF here. The OP is asking how to send multiple values in a single parameter with each item be compared to scalar values in a table.
Let's go back to pure Dr. Codd rules. First Normal Form (1NF) starts with the idea that all values are scalar, and are expressed as columns, in the rows of tables, in the schema. It's a pretty strict hierarchy. This means that any parameter will have to be a scalar vlaue to use a theta operator against a table. That means no parameter can be a structured data type (and this is part of ANSI/ISO SQL/PSM Standards).
The proper way to do this is with a long parameter list. I've done two articles on this at simple talk. This is also how DB2 and Oracle internally handle SQL engine level procedures. The advantage of passing everything as an explicit separate scalar parameter is at all that work which the smart guys back at the lab spent on designing a compiler will be applied when you invoke the procedure. As I recall, DB2 can handle up to 2K parameters and Oracle can handle up to 4K parameters, but I really have to look to be sure; the point is, it's insanely large that you should never have to do it in a user defined procedure.
Okay I just gave an argument to authority and examples in the trade for not using non-scaler parameters. Will you please do the same?
Please post DDL and follow ANSI/ISO standards when asking for help.
July 5, 2017 at 1:48 pm
Joe,
Why is it that you insist on unnecessary rules when highly performant alternatives are readily available? If you can't apply all your high-falutin' principles to the task at hand with at least a little pragmatic advice, you probably shouldn't be posting. The DelimitedSplit8K function and it's derivatives are high function high value code that also performs extremely well. Adding additional parameters actually makes everyone's work more complicated, as you might never know just how many things a user might choose to select from any sizable list, and forcing the procedure to have as many parameters as there are possible choices is highly impractical for that reason. You can have your standards, but work still needs to get done without wasting time doing things that don't really help and just complicate the work unnecessarily. I can't even imagine trying to handle 1,000 parameters, as that would be a colossal waste of time and effort, and would likely have a much higher chance of experiencing performance issues.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 5, 2017 at 5:32 pm
sgmunson - Wednesday, July 5, 2017 1:48 PMJoe,Why is it that you insist on unnecessary rules when highly performant alternatives are readily available? If you can't apply all your high-falutin' principles to the task at hand with at least a little pragmatic advice, you probably shouldn't be posting. The DelimitedSplit8K function and it's derivatives are high function high value code that also performs extremely well. Adding additional parameters actually makes everyone's work more complicated, as you might never know just how many things a user might choose to select from any sizable list, and forcing the procedure to have as many parameters as there are possible choices is highly impractical for that reason. You can have your standards, but work still needs to get done without wasting time doing things that don't really help and just complicate the work unnecessarily. I can't even imagine trying to handle 1,000 parameters, as that would be a colossal waste of time and effort, and would likely have a much higher chance of experiencing performance issues.
>> Why is it that you insist on unnecessary rules when highly performant alternatives are readily available? <<
My unnecessary rules are actually called standards. I've been in IT since 1965 (my first paying job was at a research lab doing defense contracting). I've watched cowboy coders violate standards do sloppy code do all kinds of tricks that didn't port, that allowed errors and it fell apart on the next release of even the same product. My first boss used to tell us that we had to make sure we did good correct portable code because we would kill the wrong people. Wrong people. That's that is not a mistake.
>> Adding additional parameters actually makes everyone's work more complicated, as you might never know just how many things a user might choose to select from any sizable list, and forcing the procedure to have as many parameters as there are possible choices is highly impractical for that reason. <<
Why is the user selecting from a list in the database layer? That should of been done in the front end, before a procedure gets to the database. Again, I'll go back to DB2 and how some of the internal procedures pass 2K parameters. I hope you don't think that DB2 is a poorly designed product, and that the guys at IBM are trying for the most complicated things he can put together. After all, they really do have to maintain their own code 🙂
How many developers actually have to maintain their own code? In the real world, we have cowboy coders who just try to "get her done" and move on. I always thought the phrase "legacy code" ought to be replaced with the phrase "the family curse" code. This means that most people write crappy code and never expect get called on it. Once it runs fast enough, they are happy. We can do that in the defense industry. Every piece of code we wrote was traced directly back to us. It had to pass a FIPS flagger (software that validated the Fortran conformed to standards).
>> I can't even imagine trying to handle 1,000 parameters, as that would be a colossal waste of time and effort, and would likely have a much higher chance of experiencing performance issues. <<
Well, that's one reason you're not working on large database engine internals, like Oracle and DB2. There is no limit to the size of a set; they can be completely empty or (in theory) infinitely large. Did you read my two articles on long parameter list? I'm not going to cut and paste a few thousand words to reiterate the arguments of correctness over speed that I made there. . In practice, I have seldom had to use anything larger than 361 rows in a parameter list (19 x 19 positions on the Go board). However, I have run into people who have had to have several hundred parameters for medical records that record test results.
These are extreme. Many decades ago, Dijkstra did some research at the Burroughs Corporation on procedure calls in Algol. The magic number five appeared again; most procedures had five or fewer parameters and most recursion was five or fewer levels deep.
Please post DDL and follow ANSI/ISO standards when asking for help.
July 5, 2017 at 6:21 pm
"Tricks that don't port" is a trolling comment if I've ever heard one. The myth of portability is just that - a myth.
Example: Define a stored procedure with a single variable and SQL Server and Oracle are incompatible.
For storage, yes, store discrete values discretely. For passing parameters, delimited lists work fine, can greatly simplify logic and can run faster that 361 parameters.
July 6, 2017 at 7:20 am
jcelko212 32090 - Friday, May 26, 2017 9:33 AMwweraw25 - Wednesday, May 24, 2017 1:57 PMThere's no violation of 1NF here. The OP is asking how to send multiple values in a single parameter with each item be compared to scalar values in a table.
Let's go back to pure Dr. Codd rules. First Normal Form (1NF) starts with the idea that all values are scalar, and are expressed as columns, in the rows of tables, in the schema. It's a pretty strict hierarchy. This means that any parameter will have to be a scalar vlaue to use a theta operator against a table. That means no parameter can be a structured data type (and this is part of ANSI/ISO SQL/PSM Standards).
The proper way to do this is with a long parameter list. I've done two articles on this at simple talk. This is also how DB2 and Oracle internally handle SQL engine level procedures. The advantage of passing everything as an explicit separate scalar parameter is at all that work which the smart guys back at the lab spent on designing a compiler will be applied when you invoke the procedure. As I recall, DB2 can handle up to 2K parameters and Oracle can handle up to 4K parameters, but I really have to look to be sure; the point is, it's insanely large that you should never have to do it in a user defined procedure.
Okay I just gave an argument to authority and examples in the trade for not using non-scaler parameters. Will you please do the same?
If all values are scalar, why does ANSI/ISO SQL/PSM Standards defined the Array, Multiset, Row and XML types? Before SQL Server 2008, there was no support for a set of values to be sent as a parameter. From 2008 we have the option of using Table-valued parameters, although we're limited by developers' knowledge to work with them. I'm really not sure why do you insist on using long parameter lists when we can have a set of values. After all, we work with tables, it's what we know. Maybe you're still thinking about punch cards?
July 6, 2017 at 12:37 pm
jcelko212 32090 - Wednesday, July 5, 2017 5:32 PMsgmunson - Wednesday, July 5, 2017 1:48 PMJoe,Why is it that you insist on unnecessary rules when highly performant alternatives are readily available? If you can't apply all your high-falutin' principles to the task at hand with at least a little pragmatic advice, you probably shouldn't be posting. The DelimitedSplit8K function and it's derivatives are high function high value code that also performs extremely well. Adding additional parameters actually makes everyone's work more complicated, as you might never know just how many things a user might choose to select from any sizable list, and forcing the procedure to have as many parameters as there are possible choices is highly impractical for that reason. You can have your standards, but work still needs to get done without wasting time doing things that don't really help and just complicate the work unnecessarily. I can't even imagine trying to handle 1,000 parameters, as that would be a colossal waste of time and effort, and would likely have a much higher chance of experiencing performance issues.
>> Why is it that you insist on unnecessary rules when highly performant alternatives are readily available? <<
My unnecessary rules are actually called standards. I've been in IT since 1965 (my first paying job was at a research lab doing defense contracting). I've watched cowboy coders violate standards do sloppy code do all kinds of tricks that didn't port, that allowed errors and it fell apart on the next release of even the same product. My first boss used to tell us that we had to make sure we did good correct portable code because we would kill the wrong people. Wrong people. That's that is not a mistake.
>> Adding additional parameters actually makes everyone's work more complicated, as you might never know just how many things a user might choose to select from any sizable list, and forcing the procedure to have as many parameters as there are possible choices is highly impractical for that reason. <<
Why is the user selecting from a list in the database layer? That should of been done in the front end, before a procedure gets to the database. Again, I'll go back to DB2 and how some of the internal procedures pass 2K parameters. I hope you don't think that DB2 is a poorly designed product, and that the guys at IBM are trying for the most complicated things he can put together. After all, they really do have to maintain their own code 🙂
How many developers actually have to maintain their own code? In the real world, we have cowboy coders who just try to "get her done" and move on. I always thought the phrase "legacy code" ought to be replaced with the phrase "the family curse" code. This means that most people write crappy code and never expect get called on it. Once it runs fast enough, they are happy. We can do that in the defense industry. Every piece of code we wrote was traced directly back to us. It had to pass a FIPS flagger (software that validated the Fortran conformed to standards).
>> I can't even imagine trying to handle 1,000 parameters, as that would be a colossal waste of time and effort, and would likely have a much higher chance of experiencing performance issues. <<
Well, that's one reason you're not working on large database engine internals, like Oracle and DB2. There is no limit to the size of a set; they can be completely empty or (in theory) infinitely large. Did you read my two articles on long parameter list? I'm not going to cut and paste a few thousand words to reiterate the arguments of correctness over speed that I made there. . In practice, I have seldom had to use anything larger than 361 rows in a parameter list (19 x 19 positions on the Go board). However, I have run into people who have had to have several hundred parameters for medical records that record test results.
These are extreme. Many decades ago, Dijkstra did some research at the Burroughs Corporation on procedure calls in Algol. The magic number five appeared again; most procedures had five or fewer parameters and most recursion was five or fewer levels deep.
I'm not going to argue standards. It's the insistence on applicability that bothers me along with your attitude. I've had enough of wasting any more of my time dealing with you.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply