SQL statement in cs file vs Store procedure

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.

Who is Participating?
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                  

SELECT FieldName FROM v_MyView WHERE FieldName = @FieldName

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.

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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.

lyha_zmAuthor Commented:
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.
Rakesh JaiminiCommented:
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
lyha_zmAuthor Commented:
rakeshjaimini: I dont think I understand what you mean, can you give me an example? Thanks
Rakesh JaiminiCommented:
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
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.