July 17, 2008 at 5:41 am
We have several asp pages querying the same table, just different columns. I'm moving these queries into stored procedures. I had thought of writing just one stored procedure, taking an input parameter to indicate which columns to return, and then do something like:
if @parm = 'bills'
select bills from table
if @parm = "name"
select name from table
Is this OK, or would it be better to write many stored procedures? Does it matter as far as performance goes?
Thanks
elaine
July 17, 2008 at 5:54 am
Its always good practice to differentiate your objects, so it won't affect your functionality. In this case instead of writing IF ELSE you can also create a Dynamic query which takes the column name as input parameter
e.g: SELECT "+ @param +" FROM table
July 17, 2008 at 6:02 am
elaine (7/17/2008)
We have several asp pages querying the same table, just different columns. I'm moving these queries into stored procedures. I had thought of writing just one stored procedure, taking an input parameter to indicate which columns to return, and then do something like:if @parm = 'bills'
select bills from table
if @parm = "name"
select name from table
Is this OK, or would it be better to write many stored procedures? Does it matter as far as performance goes?
Thanks
elaine
Hi Elaine,
Why u r unnecessarily using "IF", Also it is better to use just one Stored Procedure.
Refer following code:
----------------------------------------------------------
DECLARE @parm VARCHAR(50)
DECLARE @SQLstmt VARCHAR(max)
SET @parm = 'bills'
SET @SQLstmt = 'SELECT ' + @parm + ' FROM table'
EXEC (@SQLstmt)
----------------------------------------------------------
Samarth
July 17, 2008 at 6:11 am
I thought it was better to stay away from dynamic sql, which is why i did all the if statements.
July 17, 2008 at 6:58 am
Actually, if I were doing it, first, I'd try to avoid dynamic sql unless you use sp_executesql and pass it parameters (this creates execution plans that are reusable). For what you want, you could have a single stored procedure act as a wrapper and then within the IF statements have it call other stored procedures. This way, each individual stored procedure can get it's very own execution plan. Otherwise, you are reduced to dynamic sql or, you get a recompile almost every time the stored procedure is called. This happens because the first time through it hits your first IF statement and executes, creating a nice plan for that first query. The second time through it passes the first IF and goes into the second. It can't use the execution plan in cache, so it dumps it and creates a beautiful new plan for the second query. The third time through, it hits the first IF statement.... You get the picture.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 17, 2008 at 7:10 am
I wasn't sure if the execution plan would be reused or not, but was suspicious that it might not be. Thanks for your explanation and the suggestion for using this first stored procedure as a wrapper. I will try that.
Thanks very much
Elaine
July 17, 2008 at 7:38 am
I would take a slightly different approach. I would create one stored procedure that returns all of the fields from the table. I would then do one of the following:
a) Have the program/class (depending on the language) that executes the stored procedure and returns the data to the webpage do the If statement or
b) Have a two different programs/classes execute the one stored procedure returning the specific information that each webpage needs.
Using either method you only have one stored procedure that doesn't need to know anything about the program that executed it.
The benefit to option b is if you ever decided you need to return different fields from that same table for a different page, you don't have to change the stored procedure or any existing code. All you would need to do is to create a new program/class to return only the data needed for this page.
Of course, this is just my opinion.
July 17, 2008 at 7:59 am
meichner (7/17/2008)
I would take a slightly different approach. I would create one stored procedure that returns all of the fields from the table. I would then do one of the following:a) Have the program/class (depending on the language) that executes the stored procedure and returns the data to the webpage do the If statement or
b) Have a two different programs/classes execute the one stored procedure returning the specific information that each webpage needs.
Using either method you only have one stored procedure that doesn't need to know anything about the program that executed it.
The benefit to option b is if you ever decided you need to return different fields from that same table for a different page, you don't have to change the stored procedure or any existing code. All you would need to do is to create a new program/class to return only the data needed for this page.
Of course, this is just my opinion.
I would have to disagree with this as best practices for performance stipulate that you should only return the data you need. It also would mean that you may end up with bookmark (key) lookups because you are not using a covering index because of the extra data returned.
I would agree with Grant that you should use multiple stored procedures, but I would likely implement it in the asp pages as you state that
We have several asp pages querying the same table, just different columns.
. What do you really gain by having the one "master" procedure? If you had one asp page with a dropdown list that changed the output then I would consider the master procedure, but calling from multiple pages I would directly call the correct procedure. You also do not mention if there are different search criteria for each call, which would probably change the solution as well.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 17, 2008 at 8:03 am
You should take into consideration the danger of using dynamic SQL statements, in that they provide a path through database security for injection attacks, and unauthorized data access.
July 17, 2008 at 8:12 am
I'm happy to see someone else come out with the old data saw, only move the data you need and only when you need it. I would have added that.
As to the dynamic SQL being a security risk... only if you do it wrong.
If you do this:
DECLARE @mysql nvarchar(max)
SET @mysql = 'SELECT * FROM ' + @TableName + ' WHERE ' + @MyParam
EXEC @mysql
Then yeah, you're dead.[/url] But you can build your strings & execute them like this (from BOL):
DECLARE @IntVariable int;
DECLARE @SQLString nvarchar(500);
DECLARE @ParmDefinition nvarchar(500);
/* Build the SQL string one time.*/
SET @SQLString =
N'SELECT * FROM AdventureWorks.HumanResources.Employee
WHERE ManagerID = @ManagerID';
SET @ParmDefinition = N'@ManagerID tinyint';
/* Execute the string with the first parameter value. */
SET @IntVariable = 197;
EXECUTE sp_executesql @SQLString, @ParmDefinition,
@ManagerID = @IntVariable;
/* Execute the same string with the second parameter value. */
SET @IntVariable = 109;
EXECUTE sp_executesql @SQLString, @ParmDefinition,
@ManagerID = @IntVariable;
That's safe.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 17, 2008 at 9:31 am
From a DBA point of view, I'd go with multiple procs, no master proc. From a "make life easy on the devs" point of view, I'd go with the master proc and a parameter for columns.
But keep in mind that the master proc is going to have to be rewritten often, as more apps are built or as changes cause the columns-needed list to vary.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply