Where is the one place that you should definitely NOT ...
-
I disagree that building the sql instead the proc is the better option. It's a valid option, but if you're building a really advanced search it's just cleaner to build on the client where you have better string handling. Building the string server side doesn't necessarily eliminate the risk of sql injection either - you still have to validate.
-
I too believe in the 'it depends' variant - the question is too generic to be answered absolute.
(..there's not that many absolutes in this business anyway)
But, for argument's sake - one place (or situation) where it would 'likely be not such a good idea' to code a stored proc, is in the CLR (assuming scope is SQL Server ofc)
OTOH, I'm sure there are 'procs' that would run just fine in CLR instead of T-SQL, but... for the most part, that's one recommendation that's out there.
/Kenneth
-
Kenneth,
actually CLR is text processing, it's not about database at all.
No wonder SQL is not optimal for tasks it was not designed for.
I would suggest not to use T-SQL also for cooking rice and painting walls.
_____________
Code for TallyGenerator -
I would suggest not to use T-SQL also for cooking rice and painting walls. I "paint the walls" with it all the time, if you know what I mean
" (#&($^$%!!!!-ing stupid users!!! "--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Users? Which users?
You not suppose to know where they live!
It should be "(#&($^$%!!!!-ing stupid developers!!!"
_____________
Code for TallyGenerator -
Heh... now you understand my world of pain... I've got users that think they know how to write code/design objects and developers that don't. Ouch!
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Going back to what I said that is the only business case I have ever been given that would warrant looking at not using any. I saw the comment earlier but the reality is with some work you can accomplish writing something generic enough to work against several dozen database types without needing to recode, but alas that reason is shaky because the reality is that all DBs to not even follow the same rules (such as the SQL and Access comment when you consider the wildcard characters to use in LIKE).
Reality is there is no real reason not to use SPs if ou deem them neccessary for performance and security reasons.
Personally I choose never to use Dynamic SQL inside a stored procedure due to the security can of worms as you can setup a parameterized dynamic query in the app just as easy with much better security around the code. However that said some folks choose to do and they choose to handle variable checking themselves otherwise with success. However in my opinion I would say that due to security concerns (especially for Injection Attacks) this is the only other case I give creedance for not using an SP.
Even thou these are the 2 I would see doesn't mean I couldn't someday come across another reason I would accetp as valid, but for now this is it.
-
Well, the question was about where NOT to use a procedure, and the CLR would then qualify, don't you think?
(this notion is in view that it's nowadays quite possible to do so)
/Kenneth
-
I know squat about CLR's... can you call a proc in a CLR?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Even 'worse' - you can code T-SQL stuff inside the CLR - which now gives the 'enemy' (devs <g>
the tools to do the 'right' stuff in the 'wrong' place, so to speak.The CLR is a great tool - provided it's used properly - the 'problem' we're afraid of, is (as always) that tools may be misused, thus creating more problems.
--- brief snip from 2005 BOL, Overview of CLR ---
With the CLR hosted in Microsoft SQL Server (called CLR integration), you can author stored procedures, triggers, user-defined functions, user-defined types, and user-defined aggregates in managed code. Because managed code compiles to native code prior to execution, you can achieve significant performance increases in some scenarios.
......
Transact-SQL is specifically designed for direct data access and manipulation in the database. While Transact-SQL excels at data access and management, it is not a full-fledged programming language. For example, Transact-SQL does not support arrays, collections, for-each loops, bit shifting, or classes. While some of these constructs can be simulated in Transact-SQL, managed code has integrated support for these constructs. Depending on the scenario, these features can provide a compelling reason to implement certain database functionality in managed code.
.......
When writing stored procedures, triggers, and user-defined functions, one decision you must make is whether to use traditional Transact-SQL, or a .NET Framework language such as Visual Basic .NET or Visual C#. Use Transact-SQL when the code will mostly perform data access with little or no procedural logic. Use managed code for CPU-intensive functions and procedures that feature complex logic, or when you want to make use of the BCL of the .NET Framework.
... etc etc etc
--- end snippets ----
... so you see, this beast is a new ballgame entirely - it does indeed place a LOT of responsibility on all aspects of 'designers' when it comes to choose how to implement any given 'function/whatever'.....
In the end, I do believe it gives a lot of opportunitues - however those opportunities are double-edged, and that's the really big scare atm...
/Kenneth
-
So with CLR you can extend the T-SQL language, or just write new functions, just like UDF?
N 56°04'39.16"
E 12°55'05.25" -
As well as constructing buildings and repairing automobiles.
What CLR has in common with T_SQL?
Why you talk about CLR, but not about VB script? SP cannot be used there as well.
C++, Java, they all don't allow to use SP's.
But is it actually a subject?
_____________
Code for TallyGenerator -
Thanks Ken... haven't had the opportunity to work with 2005 to any great extent. Appreciate the time you took to explain it.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Gaddamn this %"&¤) timeout thingy
I'll make it shorter then...
About the CLR and procs - yes they are called the same thing, and work the same way. In my mind, the same word are used to describe the same thing.
--- snip BOL --
The Transact-SQL DML syntax used for creating .NET Framework stored procedures is the same as that used for creating stored procedures written in Transact-SQL.
-- end BOL --
ofc there's a difference between T-SQL (our 'native' way of writing procs), and C# or VB which are currently the two .NET languages you also can write procs in. So, as I read the word 'procedure', it can nowadays be either of those.
I may be misreading you Sergyi, but I took the quote from BOL, because I read it as there is something in common there - a 'procedure' may now be implemented either as a T-SQL proc, or as a .NET proc. For all purposes, it's the same name for the same thing, only implemented in different ways.
....remember to press CTRL-C.....
/Kenneth
-
In all honesty I have mostly just seen demos and presentations on it.
(It's disabled by default on new installations)
It depends on what 'extend' means - it could mean an 'extension' in the sense that it would allow you to use T-SQL to invoke C# or VB.NET code as if it was T-SQL.. (makes sense?)
The subject is absolutely HUGE, and I'm by no means any expert on it, but there's some reading in 2005 BOL and other places 'out there' that tries to explain what you can and can't do, as well as what you should (or could) do and what you shouldn't do with it.
/Kenneth
Viewing 15 posts - 16 through 30 (of 34 total)
You must be logged in to reply to this topic. Login to reply