[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL statement in cs file vs Store procedure

Posted on 2010-01-08
9
Medium Priority
?
490 Views
Last Modified: 2012-05-08
Hi Expert,

I want to create a search funtion in my project, I am using VS 2005 and SQL server 2005.
in my search there is one category that I want to allow users to select mutilply records, so I only thought about two ways of doing this, one is crease a sql statement in my cs file and pass it to sql and run the query. another way is crease a store procedure and pass a parameter in the store procedure and canculate it there. which is the better way of doing this? or is there any other ways? which way is fater? what's the benifit of using either of these two.

Thanks
0
Comment
Question by:lyha_zm
  • 3
  • 2
  • 2
  • +2
9 Comments
 
LVL 18

Expert Comment

by:carlnorrbom
ID: 26212995
Hi,

Well, you can create a sql statement and pass parameters as well, you don't need a stored procedure for that. I believe, depending on the complexity of the query, that performance is better from a stored proc. If you use parameterized query, security is not issue, but don't don't use concatenated strings for your query as that might cause security issues. I normally use queries in code, but then usually abstract it off into a class of it's own to form a Data Access Layer.

/Carl.
0
 
LVL 10

Expert Comment

by:Rakesh Jaimini
ID: 26213208
0
 
LVL 2

Expert Comment

by:shankwheat
ID: 26213405
You can do it either way but putting your query into a stored provcedure and passing the parameters to it is much more secure because it greatly reduces your chances of a SQL injection attack which are becoming very common.  You also gain some performance benefit by putting your code into a stored procedure.  It also just makes your code more manageable and easier to maintain.

Shank
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:lyha_zm
ID: 26213581
shankwheat, I totally agree with your idea, but here comes an example, if I have a query, and it's involove 10 tables, I have 5 places need to use these query, and each of these 5 places are using part of the result. (you just need add different where), and I dont want to create 5 storeprocedures that are similar, because if later I need make a change, it will be lots of work and easy to make mistakes. I also dont want to put it into my cs file because of the security reason, so I dont knowwhat should I do now! I thought about using View in sql, but is that going to affect my speed? because the data from these 10 tables are very large.
0
 
LVL 2

Accepted Solution

by:
shankwheat earned 2000 total points
ID: 26213987
It's probably going to take some experimentation on your part to see what right combination of balancing out these queries will be.  You could write a single stored procedure against the one view

CREATE PROCEDURE [dbo].[p_Search]
      @FieldName varchar(300) = NULL                  
AS                                                                    

SELECT FieldName FROM v_MyView WHERE FieldName = @FieldName
0
 
LVL 10

Expert Comment

by:Rakesh Jaimini
ID: 26214029
create stored procedure using joins and call this procedure from all the places
now in code use
datatable.Select statement to remove useless data in that case
0
 

Author Comment

by:lyha_zm
ID: 26214054
rakeshjaimini: I dont think I understand what you mean, can you give me an example? Thanks
0
 
LVL 10

Expert Comment

by:Rakesh Jaimini
ID: 26246752
i can help you in framing your SP
please provide some sample and required output
what your 10 tables contains and what you are searching for
there might be some possibility of using sys columns to get result faster
 
0
 
LVL 3

Expert Comment

by:hdesouky
ID: 26275227
You can pass additional parameter to ur SP to decide which table to query

Then u can navigate through the result using data reader or dataset

For me, I would create 5 stored procedures to support my query, more modular
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question