Where is the one place that you should definitely NOT ...
-
Ran into an interesting question today... "Even if you were a stong advocate of using stored procedures, where is the one place that you should definitely NOT use a stored procedure?"
I can find the larger arguments between the "use stored procedures for everything" folks and the "use only dynamic SQL from the app" folks, but I can't find the answer to the smaller question above... any ideas?
--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)
Intro to Tally Tables and Functions -
wow Jeff you sure ask the toughies 
Wish I knew the answer
I use procs for everything (as far as I remember), the only time I never did was building dynamic sql in an app for complex searching with many variables.
Don't know about the 'definitely NOT', will wait in anticipation
Far away is close at hand in the images of elsewhere.
Anon. -
Guess that's what I get when I only ask one question a year, huh?
Heh... yeah... I had a similar reaction as you did, David... I know you can't make it brush your teeth without the correct interface
, but I just don't know what they were getting at.Anyone else have any ideas on this?
--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)
Intro to Tally Tables and Functions -
TRIGGER?
N 56°04'39.16"
E 12°55'05.25" -
You know there is only one correct answer to this:
It depends!
-
Dunno, Peter... I can see UDF's being a place where you'd definitely NOT want to use a stored proc... but, I really don't know what they were getting at. I can see maybe using a common stored proc to send an email alert on a zero row condition in triggers on an import staging tables or something like that, so I'm thinking it's not a trigger answer they were looking at. Heck, maybe it's one of those "trick" questions to determine if you steer towards doing everything in the app or you're a hard-core CRUD advocate. I dunno...
If anyone knows the answer to this one, I'd sure like to know 'cause it's bugging the heck out of me that I can't find a concrete answer on this
. I should have asked them what the answer was but didn't think of it in time...--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)
Intro to Tally Tables and Functions -
That's kinda where I went with it... it depends. But, I think they had something specific in mind... or, like I said, maybe a trick question to determine which "camp" I was in...
--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)
Intro to Tally Tables and Functions -
It's an interesting question, and one designed, IMHO, to see how you think. Proving a negative is always hard and to me the question doesn't make much sense without more context. Do you mean in an application? In administration? Anytime?
In a query, a UDF works, but a stored proc doesn't apply, so it doesn't really fit the question. If you're talking an application, then I guess it doesn't seem that there's a good place not to use one. UDFs work better in places, but stored procs work great as well.
If I were asked in an interview, I'd probably think about places where dynamic SQL is more suited, or there are a large number of variable params, but even then I'd probably go to something like XML or a CLR Parser for a comma delimited string than not use a stored proc. I'd also turn it around and see if they guy had any answers or just pulled it out of the rear lower section of his chair.
Should have sent this for a Friday poll!!
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 -
In applications that have to be fully portable between multiple database backends or talk to multiple types of backends at the same time, better off staying in the apps code itself. In addition you have to go generic ANSI SQL and no special T-SQL extension items in your SQL code.
-
Yep, thanks Antares... knew that. Not entirely sure that's what they were looking for, though. Really strange question. I think both Steve and I are right... it was one of those questions designed to see how someone would react.
--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)
Intro to Tally Tables and Functions -
Should have sent this for a Friday poll!! Didn't know there was such a thing... I'll check it out! Thanks Steve.
Strange question, huh? Sure got me thinking... I think Antares is probably on the right trail...
--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)
Intro to Tally Tables and Functions -
I think Antares is probably on the right trail... Hmm...
Is it easier to embed generic SQL into application comparing to simple SP calls?
Are you sure same SQL will always work on 2 different products of the same company - MS SQL Server and MS Access?
So, how many versions of SQL you have to include into your code?
If we are talking about generic application why to limit it the range by only SQL databases?
And for file system databases this question does not make any sense.
And don't forget, this world in not limited to the States. I saw database engines having Cyrillic programming language - no Latin letters used at all! There is no SELECT, there is ???? instead. (Sorry, this site does not support Cyrillic letters)
If you are talking about really universal application you suppose to include support for this language into your application as well. Can you?
So, there is no such thing as "universal application". It always is limited to some set of supported database engines, database connection strings and database schemas. You cannot build a query without knowing all queried tables are there. And you cannot include system tables calls into your query builder because they are different even for different versions of MS SQL Server. And not everybody will allow you to perform system objects queries.
From another side same SP call will work on different DB engines (if they support support SP functionality, of course), against databases with absolutely different table structures. If schema-dependent part of code is embedded into SP within database it makes application really universal.
Don't you think?
_____________
Code for TallyGenerator -
Heh... Oh, I agree with you, Serqiy... no such thing as a "Universal Application". Maybe they're that "misinformed" or ... not.
Nah, it's gotta be something else. I'll try to get ahold of them and find out...
--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)
Intro to Tally Tables and Functions -
I use procs for everything except dynamic searches, ones where they can select the columns, tables, etc based on some type of search UI. Just too ugly to do any other way.
Probably a case to be made for using dynamic sql for things like cross tabs, recursion, in some situations anyway.
-
Yes, Andy,
you are right about dynamic SQL.
But it does not mean dynamic SQL must be built outside SP.
SQL injections, security issues (e.g. not every DBA is happy to let a stranger access any table in database) are still here.
So, it's better to build dynamic query inside SP.
_____________
Code for TallyGenerator
Viewing 15 posts - 1 through 15 (of 34 total)
You must be logged in to reply to this topic. Login to reply