lyha_zm
asked on
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.
Thanks
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
check following
https://www.experts-exchange.com/questions/24991191/Need-Help-couldn't-find-my-problem.html
and
https://www.experts-exchange.com/questions/25003289/Need-Help-in-Store-Procedure.html
the asker was making some search SP
https://www.experts-exchange.com/questions/24991191/Need-Help-couldn't-find-my-problem.html
and
https://www.experts-exchange.com/questions/25003289/Need-Help-in-Store-Procedure.html
the asker was making some search SP
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
Shank
ASKER
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
now in code use
datatable.Select statement to remove useless data in that case
ASKER
rakeshjaimini: I dont think I understand what you mean, can you give me an example? Thanks
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
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
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
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.