|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
The case of the inadequate case functionmultiple directions depending on the value of some switch variable. My situation right now is that I have a variable, @PostType, that determines which stored procedure should be called. I tried to use the CASE statement, but it doesn't work--it appears to be only useful for expressions that return values. Is there any way to do this branching without the uglo solution of nesting a bunch of if statements? The following code doesn't work but it should give you an idea of what I'm trying to do: --------------------------------------------- CREATE PROCEDURE WishThisWorkedButNooo @PostType varchar(10), @ParticipantID tinyint, @HypothesisID tinyint, @Subject varchar(100), @Body varchar(5000) AS DECLARE @TimeStamp AS datetime SET @TimeStamp = GETDATE() SELECT CASE @PostType WHEN 'IPost' THEN EXECUTE SetIPostData @ParticipantID, @HypothesisID, @TimeStamp, @Subject, @Body WHEN 'Entry' THEN EXECUTE SetEntryData @ParticipantID, @TimeStamp, @Subject, @Body WHEN 'Summary' THEN EXECUTE SetAssessmentData @ParticipantID, @TimeStamp, @Body END --------------------------------------------- Thanks in advance, -Dan >> In most programming languages, there is a way to branch your code in multiple directions depending on the value of some switch variable. <<BRANCH?! SWITCH??!! DIRECTION???!!! This is absurd in the SQL world. SQL is a **declarative** language!!! Why are you talking about procedural concepts? You have missed the whole point of SQL. Next, you will be looking for WHILE loops in LISP. >> My situation right now is that I have a variable, @PostType, that determines which stored procedure should be called. <<You have never had a Software Engineering course. This is MUCH more basic than just SQL. You do not know how to program. What would you call such a magical thing? What is the name procedure in ISO-11179 rules? "Get_BritanySpearsRecords_or_Squids" A procedure shodul do one and only one well-defined job. >> I tried to use the CASE statement, but it doesn't work -- it appears to be only useful for expressions that return values. Is there any wayto do this branching without the ugly solution of nesting a bunch of if statements? << Really? There is no CASE statement in SQL; there is a CASE **expression** however. This is a fundamental concept in SQL or any declarative language. You need some SERIOUS FUNDAMENTAL help and you are not going to get it on a newsgroup. --CELKO-- Please post DDL in a human-readable format and not a machine-generated one. This way people do not have to guess what the keys, constraints, DRI, datatypes, etc. in your schema are. Sample data is also a good idea, along with clear specifications. *** Sent via Developersdex http://www.developersdex.com *** LOL. Actually, that's one of the best responses I've gotten on a
newsgroup. Not only do I have the answer to my question (can't be done), but I now realize what kind of person I am for even contemplating such a question (complete ass). Most importantly, I now realize what all the programming I've done over the years amounts to (bupkiss). This is a far better lesson than anything I could have learned by calling 1-900-PSYCHIC. So, thanks & Happy New Year, -Dan If you cannot easily spot that all this poster wants is IF ELSE then you
have a significant amount to learn yourself, I think you should concentrate on an entry level SQL Server course rather than dissing posters here. There is nothing wrong with the stored procedure names, nor parameter names - there fairly descriptive to me and I've not even seen the business requirement! Again you highlight your complete lack of programming experience. Show quote "--CELKO--" <remove.jcelko***@earthlink.net> wrote in message news:%23vSoprlDGHA.2076@TK2MSFTNGP09.phx.gbl... >>> In most programming languages, there is a way to branch your code in > multiple directions depending on the value of some switch variable. << > > BRANCH?! SWITCH??!! DIRECTION???!!! This is absurd in the SQL world. > SQL is a **declarative** language!!! Why are you talking about > procedural concepts? You have missed the whole point of SQL. Next, you > will be looking for WHILE loops in LISP. > >>> My situation right now is that I have a variable, @PostType, that > determines which stored procedure should be called. << > > You have never had a Software Engineering course. This is MUCH more > basic than just SQL. You do not know how to program. > > What would you call such a magical thing? What is the name procedure in > ISO-11179 rules? "Get_BritanySpearsRecords_or_Squids" A procedure > shodul do one and only one well-defined job. > >>> I tried to use the CASE statement, but it doesn't work -- it appears > to be only useful for expressions that return values. Is there any way > to do this branching without the ugly solution of nesting a bunch of if > statements? << > > Really? There is no CASE statement in SQL; there is a CASE > **expression** however. This is a fundamental concept in SQL or any > declarative language. > > You need some SERIOUS FUNDAMENTAL help and you are not going to get it > on a newsgroup. > > --CELKO-- > Please post DDL in a human-readable format and not a machine-generated > one. This way people do not have to guess what the keys, constraints, > DRI, datatypes, etc. in your schema are. Sample data is also a good > idea, along with clear specifications. > > > *** Sent via Developersdex http://www.developersdex.com *** Tony Rogerson (tonyroger***@sqlserverfaq.com) writes:
> If you cannot easily spot that all this poster wants is IF ELSE then you I think that Celko is perfectly capable of that.> have a significant amount to learn yourself, However, he appears to be driven by the eager to bash as many inexperience posters as possible. Which is not honourable at all. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx I think you have hit the nail on the head there Erland.
There is no excuse for how rude and unhelpful he is to people on here and other forums - the guy really needs a lesson on how to behave in public. It is amusing to people who frequent these forums regularly and certainly from my point of view realise he is not the expert he thinks he is, but for the one off visitor it can be a big put off - it's happened to me on a non-SQL forum, being belittled for asking a perfectly reasonable but to the crowd simple question, not by this guy but of the same ilk. Show quote "Erland Sommarskog" <esq***@sommarskog.se> wrote in message news:Xns973E9482C70B9Yazorman@127.0.0.1... > Tony Rogerson (tonyroger***@sqlserverfaq.com) writes: >> If you cannot easily spot that all this poster wants is IF ELSE then you >> have a significant amount to learn yourself, > > I think that Celko is perfectly capable of that. > > However, he appears to be driven by the eager to bash as many inexperience > posters as possible. Which is not honourable at all. > > > > > -- > Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se > > Books Online for SQL Server 2005 at > http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx > Books Online for SQL Server 2000 at > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx Actually, I am pretty much a SQL novice, and I've never followed this
forum, but I still thought CELKO's post was amusing :) I mean, I've seen a lot of flames on the internet over the years, but few have been as over the top as that one. You guys are right, though, that a lot of people would be driven away forever by stuff like that, and it's cool that you all stepped up to say something. Anyway, thanks to everyone who clued me in to "ELSE IF." It's a lot cleaner than nesting the IF's. -Dan >> Actually, I am pretty much a SQL novice, and I've never followed this forum, but I still thought CELKO's post was amusing :) I mean, I've seen a lot of flames on the internet over the years, but few have been as over the top as that one << Hey, I was not anywhere near my best! If you think I am bad, you oughtto see my wife, the Zen Monk, who beats people with sticks! >> Anyway, thanks to everyone who clued me in to "ELSE IF." It's a lot cleaner than nesting the IF's. << You should not thank them. You have been given a kludge that will letyou write highly proprietatry, procedural code in a declarative language. You are not learning SQL or how to think in a different paradigm. You really need to start with the foundations. I get what you're saying (pretty much). Why write procedues that aren't
of general use? Why make SQL do things it wasn't designed for? Why piss off CELKO? All I can say is, I'm just trying to get this thing done. I like learning new things, but, I'm sorry, I didn't have time to read the theoretical foundations of SQL. I started out with simple SELECT and INSERT statements. When I needed something more, I looked it up. If you saw some of the other procedures I wrote, maybe you wouldn't be so freaked out. Or maybe you would. Either way, you'll keep doing what you do, and so will I. -Dan >> I'm just trying to get this thing done. << No, try to learn something from this thing. Bouncing from job to job,instead of seeing it as a path to wisdom is the difference between being a mere "day laboror" and a craftsman in the making. >> I like learning new things, but, I'm sorry, I didn't have time to read the theoretical foundations of SQL.<<Make the time! Make the time! Make the time! Be a professional. Do you want to just have a certificate after your name or do you want something more? And it is "theoretical foundations of RDBMS", not SQL. Based on teaching RDBMS and SQL in academia and industry for a few decades, it will take about a year of hard work. >> I started out with simple SELECT and INSERT statements. When I needed something more, I looked it up. << No, you are going to Newsgroups, which is like going to a drug dealer, to get over a hump. You get a kludge or a non-standard solution in a dialect that will not port. You are encouraged to be "cowboy coder" -- get it to work in the current release of a dialect, with the current situation and never consider that someone else will have to maintain it. >> If you saw some of the other procedures I wrote, maybe you wouldn't be so freaked out. Or maybe you would. <<Maybe, maybe not. I have ~35 years in the IT trade, and helped with some of the early Software Engineering work in the U.S. Army and DoD -- the largest and most diverse users of IT on Earth. I used to write magazine columns on proper procedural coding practices based on my experience and a few million dollars of research. I never wrote a book on it, for some reason. What freaks me out is that you are writing PROCEDURAL procedures in SQL, as if T-SQL was Pascal, Algol, C, BASIC, etc. You want to write declarative procedures instead. Turn the FOR-loops into Sequence auxiliary tables, the IF-THEN-ELSE statements into CASE expressions inside the code, etc. Aside from that language consideration, procedures should have good coupling and cohesion. If you do not know what those words mean, then **fundamental programming concepts** is your problem, not SQL. >> Either way, you'll keep doing what you do, and so will I. << Aim higher! Be better! Or tell you your boss (assuming his businessgrows) that he needs to call me in 1-2 years when your code collapses into an un-maintainable mess. --CELKO-- Please post DDL in a human-readable format and not a machine-generated one. This way people do not have to guess what the keys, constraints, DRI, datatypes, etc. in your schema are. Sample data is also a good idea, along with clear specifications. *** Sent via Developersdex http://www.developersdex.com *** Just to see if I'd get a warm, fuzzy feeling, I dropped the dreaded
ELSEIF procedure and coded it up in VB.NET instead. It's pretty much the same code, except I got to use a real case statement. Is this better? I have no idea. Perhaps my relational database management system is more cohesive and less coupled now. Or maybe it's just six of one, a half dozen of the other. Despite the fact that programming is only one of several things I do at my job (and my degree is not in computer science), don't think I'm *completely* clueless. If I find myself writing the same code twice, I turn it into a procedure/function/subroutine/whatever, hopefully one with a good name that will make sense to me and whoever else is reading it. If a block of code gets too long and hard to read, I break it up into smaller functions. And when it comes to designing relational database tables, I try to make sure that the same data is not repeated in multiple tables and rely instead on foreign keys and joins to put the data in the format I need. Finally, the project I'm working on is a web app for an experiment that will be run a grand total of once. If it becomes an unmaintainable mess in one to two years, I'm pretty sure my company won't come knocking at your door, but I will pass a resume on to them if it will help you sleep at night. Oh, and just for the record, I've definitely learned more from doing this one project than I have from taking a whole semester of [fill in programming language here]. -Dan --CELKO-- (remove.jcelko***@earthlink.net) writes:
> No, try to learn something from this thing. Bouncing from job to job, The only thing that people can learn from you is how not to behave in > instead of seeing it as a path to wisdom is the difference between being > a mere "day laboror" and a craftsman in the making. public. If you want to actually teach people things, you need to change your attitude seriously. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx If you are so against IF ELSE statements then why do you advocate them in
your programming styles book? You do not seem to practice what you preach. Show quote "--CELKO--" <remove.jcelko***@earthlink.net> wrote in message news:eBPm7o$DGHA.1816@TK2MSFTNGP11.phx.gbl... >>> I'm just trying to get this thing done. << > > No, try to learn something from this thing. Bouncing from job to job, > instead of seeing it as a path to wisdom is the difference between being > a mere "day laboror" and a craftsman in the making. > >>> I like learning new things, but, I'm sorry, I didn't have time to > read the theoretical foundations of SQL.<< > > Make the time! Make the time! Make the time! Be a professional. Do you > want to just have a certificate after your name or do you want something > more? And it is "theoretical foundations of RDBMS", not SQL. Based on > teaching RDBMS and SQL in academia and industry for a few decades, it > will take about a year of hard work. > >>> I started out with simple SELECT and INSERT statements. When I needed > something more, I looked it up. << > > No, you are going to Newsgroups, which is like going to a drug dealer, > to get over a hump. You get a kludge or a non-standard solution in a > dialect that will not port. You are encouraged to be "cowboy coder" -- > get it to work in the current release of a dialect, with the current > situation and never consider that someone else will have to maintain it. > >>> If you saw some of the other procedures I wrote, maybe you wouldn't > be so freaked out. Or maybe you would. << > > Maybe, maybe not. I have ~35 years in the IT trade, and helped with some > of the early Software Engineering work in the U.S. Army and DoD -- the > largest and most diverse users of IT on Earth. I used to write magazine > columns on proper procedural coding practices based on my experience and > a few million dollars of research. I never wrote a book on it, for some > reason. > > What freaks me out is that you are writing PROCEDURAL procedures in SQL, > as if T-SQL was Pascal, Algol, C, BASIC, etc. You want to write > declarative procedures instead. Turn the FOR-loops into Sequence > auxiliary tables, the IF-THEN-ELSE statements into CASE expressions > inside the code, etc. > > Aside from that language consideration, procedures should have good > coupling and cohesion. If you do not know what those words mean, then > **fundamental programming concepts** is your problem, not SQL. > >>> Either way, you'll keep doing what you do, and so will I. << > > Aim higher! Be better! Or tell you your boss (assuming his business > grows) that he needs to call me in 1-2 years when your code collapses > into an un-maintainable mess. > > --CELKO-- > Please post DDL in a human-readable format and not a machine-generated > one. This way people do not have to guess what the keys, constraints, > DRI, datatypes, etc. in your schema are. Sample data is also a good > idea, along with clear specifications. > > > *** Sent via Developersdex http://www.developersdex.com *** > You should not thank them. You have been given a kludge that will let Go on then (this will be interesting), how would you do it?> you write highly proprietatry, procedural code in a declarative > language. You are not learning SQL or how to think in a different > paradigm. You really need to start with the foundations. The only other way is to write a 3/4GL programming to do it which would involve compiling etc... Thats a lot of work just to replace something that is already in the langauge. Its yet another example where this portability stuff you keep ranting on about falls flat on its face - instead of 30 seconds of typing you are now talking about a hour or so of coding it up in another language - you've just raised the maintanence and skills set required by 100x, instead of just knowing basic Transact-SQL you will now need to know how to program in a 4GL. Instead of bashing people on here, go out and get an entry level job as a programmer and get some needed experience - quickly too! Show quote "--CELKO--" <jcelko***@earthlink.net> wrote in message news:1136170112.870077.278090@g43g2000cwa.googlegroups.com... >>> Actually, I am pretty much a SQL novice, and I've never followed this >>> forum, but I still thought CELKO's post was amusing :) I mean, I've seen >>> a lot of flames on the internet over the years, but few have been as >>> over the top as that one << > > Hey, I was not anywhere near my best! If you think I am bad, you ought > to see my wife, the Zen Monk, who beats people with sticks! > >>> Anyway, thanks to everyone who clued me in to "ELSE IF." It's a lot >>> cleaner than nesting the IF's. << > > You should not thank them. You have been given a kludge that will let > you write highly proprietatry, procedural code in a declarative > language. You are not learning SQL or how to think in a different > paradigm. You really need to start with the foundations. >
Show quote
"Daniel Manes" <danth***@cox.net> wrote in message Don't mind Joe.news:1136066718.570966.10850@o13g2000cwo.googlegroups.com... > In most programming languages, there is a way to branch your code in > multiple directions depending on the value of some switch variable. > > My situation right now is that I have a variable, @PostType, that > determines which stored procedure should be called. I tried to use the > CASE statement, but it doesn't work--it appears to be only useful for > expressions that return values. Is there any way to do this branching > without the uglo solution of nesting a bunch of if statements? > > The following code doesn't work but it should give you an idea of what > I'm trying to do: > With TSQL's procedural extensions you can to use stacked IF statements for this: CREATE PROCEDURE WishThisWorkedButNooo @PostType varchar(10), @ParticipantID tinyint, @HypothesisID tinyint, @Subject varchar(100), @Body varchar(5000) AS DECLARE @TimeStamp AS datetime SET @TimeStamp = GETDATE() if @PostType = 'IPost' begin EXECUTE SetIPostData @ParticipantID, @HypothesisID, @TimeStamp, @Subject, @Body end else if @PostType = 'Entry' begin EXECUTE SetEntryData @ParticipantID, @TimeStamp, @Subject, @Body end else if @PostType = 'Summary' begin EXECUTE SetAssessmentData @ParticipantID, @TimeStamp, @Body end David Daniel Manes (danth***@cox.net) writes:
> In most programming languages, there is a way to branch your code in No. This is like Perl, you have to run a suite of IF, ELSE IF, ELSE.> multiple directions depending on the value of some switch variable. > > My situation right now is that I have a variable, @PostType, that > determines which stored procedure should be called. I tried to use the > CASE statement, but it doesn't work--it appears to be only useful for > expressions that return values. Is there any way to do this branching > without the uglo solution of nesting a bunch of if statements? And just like Perl, T-SQL does not have a CASE statement. It has a CASE expression, which is like Perl (and C's) ?: operator on steroids. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx This is but one of many reasons TSQL sucks as a procedural language. Rather
than try to add all the procedural bells an whistles to make TSQL a fully functional procedural language, the SQL team decided to concentrate on the declarative aspects of SQL and host the CLR languages to handle the things that have to be procedural. Because TSQL is an interpreted language, the extra overhead of parsing and compiling the full syntactic complexity of a real object-oriented, procedural language are justified in a language that is primarily non-procedural. -- Show quoteThis posting is provided "AS IS" with no warranties, and confers no rights. Use of included script samples are subject to the terms specified at http://www.microsoft.com/info/cpyright.htm "Daniel Manes" <danth***@cox.net> wrote in message news:1136066718.570966.10850@o13g2000cwo.googlegroups.com... > In most programming languages, there is a way to branch your code in > multiple directions depending on the value of some switch variable. > > My situation right now is that I have a variable, @PostType, that > determines which stored procedure should be called. I tried to use the > CASE statement, but it doesn't work--it appears to be only useful for > expressions that return values. Is there any way to do this branching > without the uglo solution of nesting a bunch of if statements? > > The following code doesn't work but it should give you an idea of what > I'm trying to do: > > --------------------------------------------- > CREATE PROCEDURE WishThisWorkedButNooo > @PostType varchar(10), > @ParticipantID tinyint, > @HypothesisID tinyint, > @Subject varchar(100), > @Body varchar(5000) > AS > > DECLARE @TimeStamp AS datetime > > SET @TimeStamp = GETDATE() > > SELECT CASE @PostType > WHEN 'IPost' THEN > EXECUTE SetIPostData > @ParticipantID, > @HypothesisID, > @TimeStamp, > @Subject, > @Body > WHEN 'Entry' THEN > EXECUTE SetEntryData > @ParticipantID, > @TimeStamp, > @Subject, > @Body > WHEN 'Summary' THEN > EXECUTE SetAssessmentData > @ParticipantID, > @TimeStamp, > @Body > END > --------------------------------------------- > > Thanks in advance, > > -Dan > >> This is but one of many reasons TSQL sucks as a procedural language. << In the old Sybase days, we were told not to write more than 50 lines orto use PRINT in a procedure. It sucked that bad. It is a one-pass, no-optimization language that would be a grad student project today. It was a kludge to get around the weak parts of the Sybase dialect. >> Rather than try to add all the procedural bells an whistles to make TSQL a fully functional procedural language, the SQL team decided to concentrate on the declarative aspects of SQL and host the CLR languages to handle the things that have to be procedural. << A serious error. Instead of CLR, MS should have added SQL/PSM to theproduct. The nightmare that CLR will give us is 42+ different languages to maintan **inside** the RDBMS. I am old; I do not wish to learn all those X3J and proprietary languages like C# and VB. > A serious error. Instead of CLR, MS should have added SQL/PSM to the Yet again you mis the point and talk about something you don't understand.> product. The nightmare that CLR will give us is 42+ different > languages to maintan **inside** the RDBMS. I am old; I do not wish to > learn all those X3J and proprietary languages like C# and VB. > CLR is not maintained in the database at all, you do all that in your x3J tool for instance Visual Studio .NET. They added CLR and not SQL/PSM (of which is partly implemented in Transact SQL) because they listened to what their customers wanted and implemented that. The majority of developers that code using SQL are proficient in a .NET language so that makes sense, also, there are other .NET implementations of the other favourites, COBOL, Java etc... so the implementation is inclusivive and the developer doesn't need to implement yet another syntax, and that, a poor and non-extendable langauge. With CLR we can do anything, which is a danger, but when used properly will be fantastic; consider domain validation, that can now be done using a CLR function on a check constraint - the power is fantastic and offers up some real benefits in performance and maintainability. Stop thinking you know everything and actually start using these tools its the only way you are going answer questions properly and not look so stupid. Show quote "--CELKO--" <jcelko***@earthlink.net> wrote in message news:1136250977.477238.86070@f14g2000cwb.googlegroups.com... >>> This is but one of many reasons TSQL sucks as a procedural language. << > > In the old Sybase days, we were told not to write more than 50 lines or > to use PRINT in a procedure. It sucked that bad. It is a one-pass, > no-optimization language that would be a grad student project today. > It was a kludge to get around the weak parts of the Sybase dialect. > >>> Rather than try to add all the procedural bells an whistles to make TSQL >>> a fully functional procedural language, the SQL team decided to >>> concentrate on the declarative aspects of SQL and host the CLR languages >>> to handle the things that have to be procedural. << > > A serious error. Instead of CLR, MS should have added SQL/PSM to the > product. The nightmare that CLR will give us is 42+ different > languages to maintan **inside** the RDBMS. I am old; I do not wish to > learn all those X3J and proprietary languages like C# and VB. > --CELKO-- (jcelko***@earthlink.net) writes:
> In the old Sybase days, we were told not to write more than 50 lines or A no-optimization language? For an RDBMS? Geez, whatever you are smoking,> to use PRINT in a procedure. It sucked that bad. It is a one-pass, > no-optimization language that would be a grad student project today. > It was a kludge to get around the weak parts of the Sybase dialect. you are harder on it that I thought. By the way, I have SELECT statements that are over 250 lines. Our longest stored procedure is around 3000 lines. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Show quote
"--CELKO--" <jcelko***@earthlink.net>: I agree with CELKOnews:1136250977.477238.86070@f14g2000cwb.googlegroups.com... >>> This is but one of many reasons TSQL sucks as a procedural language. << > > In the old Sybase days, we were told not to write more than 50 lines or > to use PRINT in a procedure. It sucked that bad. It is a one-pass, > no-optimization language that would be a grad student project today. > It was a kludge to get around the weak parts of the Sybase dialect. > >>> Rather than try to add all the procedural bells an whistles to make TSQL >>> a fully functional procedural language, the SQL team decided to >>> concentrate on the declarative aspects of SQL and host the CLR languages >>> to handle the things that have to be procedural. << > > A serious error. Instead of CLR, MS should have added SQL/PSM to the > product. The nightmare that CLR will give us is 42+ different > languages to maintan **inside** the RDBMS. I am old; I do not wish to > learn all those X3J and proprietary languages like C# and VB. > "entities should not be multiplied without necessity" (Ockham's razor also spelled OCCAM'S RAZOR, also called LAW OF ECONOMY, or LAW OF PARSIMONY, principle stated by William of Ockham (1285-1347/49), a scholastic, that NON SUNT MULTIPLICANDA ENTIA PRAETER NECESSITATEM) even if it should bring profit :) --- Andrey Odegov avode***@yandex.ru (remove GOV to respond) TSQL "sucks" as a procedural language, much in the same way that a
screwdriver "sucks" as a hammer. Roger Wolter[MSFT] wrote: Show quote > This is but one of many reasons TSQL sucks as a procedural language. Rather > than try to add all the procedural bells an whistles to make TSQL a fully > functional procedural language, the SQL team decided to concentrate on the > declarative aspects of SQL and host the CLR languages to handle the things > that have to be procedural. Because TSQL is an interpreted language, the > extra overhead of parsing and compiling the full syntactic complexity of a > real object-oriented, procedural language are justified in a language that > is primarily non-procedural. > CREATE PROCEDURE WishThisWorkedButNooo
@PostType varchar(10), @ParticipantID tinyint, @HypothesisID tinyint, @Subject varchar(100), @Body varchar(5000) AS BEGIN DECLARE @TimeStamp datetime SET @TimeStamp = GETDATE() IF @PostType = 'IPost' BEGIN EXECUTE SetIPostData @ParticipantID, @HypothesisID, @TimeStamp, @Subject, @Body END ELSE IF @PostType = 'Entry' BEGIN EXECUTE SetEntryData @ParticipantID, @TimeStamp, @Subject, @Body END ELSE IF @PostType = 'Summary' BEGIN EXECUTE SetAssessmentData @ParticipantID, @TimeStamp, @Body END ELSE BEGIN PRINT 'Unknown PostType passed.' END END Show quote "Daniel Manes" <danth***@cox.net> wrote in message news:1136066718.570966.10850@o13g2000cwo.googlegroups.com... > In most programming languages, there is a way to branch your code in > multiple directions depending on the value of some switch variable. > > My situation right now is that I have a variable, @PostType, that > determines which stored procedure should be called. I tried to use the > CASE statement, but it doesn't work--it appears to be only useful for > expressions that return values. Is there any way to do this branching > without the uglo solution of nesting a bunch of if statements? > > The following code doesn't work but it should give you an idea of what > I'm trying to do: > > --------------------------------------------- > CREATE PROCEDURE WishThisWorkedButNooo > @PostType varchar(10), > @ParticipantID tinyint, > @HypothesisID tinyint, > @Subject varchar(100), > @Body varchar(5000) > AS > > DECLARE @TimeStamp AS datetime > > SET @TimeStamp = GETDATE() > > SELECT CASE @PostType > WHEN 'IPost' THEN > EXECUTE SetIPostData > @ParticipantID, > @HypothesisID, > @TimeStamp, > @Subject, > @Body > WHEN 'Entry' THEN > EXECUTE SetEntryData > @ParticipantID, > @TimeStamp, > @Subject, > @Body > WHEN 'Summary' THEN > EXECUTE SetAssessmentData > @ParticipantID, > @TimeStamp, > @Body > END > --------------------------------------------- > > Thanks in advance, > > -Dan >  |
|||||||||||||||||||||||
Other interesting topics