ASP Query Vs Stored Procedure
-
Hi, I was hoping someone could give me their thoughts on this question:
I have a query that retrieves a single record from a table of about 500,000 records. The columns from that record differ depending on the customer who is retrieving the record.
In my ASP code I have the following query
SELECT sFieldNames FROM Jobs WHERE ID = iID
Note that sFieldNames is a string containing the field names. iID is also a variable containing the selected record.
My question is this:
Is it better to put this query in a stored procedure and pass the sFieldNames and iID as parameters to the sp?
I know that it is always better to put your queries in a stored procedure, but because my query is dynamic I don't know if I will get any benefit from doing so.
I'd appreciate your comments.
Thanks for your time.
Kevin.
I know that if the
Windows 2008 Server | SQL Server 2008
-
Ahh,
Sparking up the big debate.
Your in the gray area that causes everyone to share their opinion.
to me I always lean towards stored procedures because they are closer to the data if you will.
If you are able to properly construct your stored proc using dynamic sql and use sp_executesql you will still maintain the ability for sql to cache the query plan.
I like to also avoid putting tables and schema information in the application, because it allows me the dba the flexability to move things around without the application knowing.
-
Depending upon what sFieldNames could have in it as its string, your code does not necessarily need to be dynamic.
You could use either a CASE statement or flow control (IF... ELSE) if there are only a finite set of possiblities - that way you overcome passing and containing information in your ASP and Dynamic SQL.
I wasn't born stupid - I had to study.
-
Why would you have different fields sent to the application??
-
Actually RGR'us, (man it is hard not calling you Remi) you are absolutely correct! Oh, dopey me...
The application should recieve ALL and determine what to present. That solves any problems with Dynamic SQL, CASE statements or flow control....
I wasn't born stupid - I had to study.
-
Flow control + 2 sps :-).
Wondering if it's because of different level of access to information... maybe the solution is different altogether than what originally thaught.
-
Hey Pals,
Wondering if this would do...!!
Create procedure sp_GetColVal (@COLNAME AS varchar(100), @ID AS INT)
AS
DECLARE @SQLQuery
SET @SQLQuery = 'SELECT ' + @COLNAME + ' FROM Jobs WHERE ID = ' + @ID
EXEC (@SQLQUERY)
GO
Can also add a check if to see the column exists or not.
Tell me if Iam wrong.
ILANGO
-
Wrong solution... should be ahndled by the app and calling the correct code.
-
Initally, you will need to CONVERT @ID into a varchar so @sqlquery is a string.
This is a poor example, but if @ColName was passed in as 'Col1, Col3, Col4; TRUNCATE TABLE TableA GO;' you could possibily allow someone to ruin your database... An over-simplified concept of SQL Injection.
I do not have the URL for the Curses & Blessings of Dynamic SQL right now. Could someone give this person that reference so they could understand why it is better not to use Dynamic SQL when possible?...
I wasn't born stupid - I had to study.
-
Hello all, sorry joined in the discussions, I've been away all weekend.
In reply to Farrell Keough: The application should recieve ALL and determine what to present. That solves any problems with Dynamic SQL, CASE statements or flow control....
Are you saying that the sp should do a SELECT * then the ASP code that displays the data should pick out the fields it wants to use?
By the way, thanks everyone for their input.
Windows 2008 Server | SQL Server 2008
-
As a 'general' rule, you want to specify each individual column in your select, (as opposed to SELECT *), and bring all of the data to your application. That way, you have all columns to handle UPDATES.
But, as RGR'us pointed out - you may have other issues, (such as User Rights) taking place in which case you may not want to present the application with all of the data. We can suggest solutions if this is your situation, but we would need to know specific business rules.
Once you have passed what you need, the application itself can 'display' the appropriate data. This may mean that not all columns are displayed, but they are still all available to the UPDATE/INSERT stored procedures you write.
My personal belief is never display Primary Keys, (if they are IDENTITY keys) to the customer. They generally only confuse the user and can, depending upon the structure, change over time even if the 'name' on the data remains the same...
I wasn't born stupid - I had to study.
-
Ok, thanks Farrell I understand.
Thanks to everyone, their help is much appreciated.
Kevin.
Windows 2008 Server | SQL Server 2008
-
Well retrieving redundant data for one
Depending on how many different 'views' there were and how many columns per 'view' it is possible to retrieve and pass on the network a lot of redundant data.
Another may be simplicity.
e.g. If there were ten different 'views' then by creating ten procedures, decide which one to use based on the user and create one set of code to produce output with headings.
This would easier to understand, read and maintain.
But then.... great in theory..... in practice !!!
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply