Shortcuts

Asp.net and SQL server - Stored procedure

Regarding using the SQL Server stored procedure... What is the best way (Prefferd) to use, put all sql commnads as a stored procedure, and when they are needed just to call the stored procedure (View code 1)? or just to put a complex sql syntax - commnads
- in a stored procedure, and all other simple sql commnads (Like: select * from Table1) to call as below (Code 2):

Re: Asp.net and SQL server - Stored procedure

Depends on what you want. We use stored procedure to insert and update & to do complex select statements (like search queries). But for simple select queries, we use queries directly. Both have their own advantages & based on your need, you can use either.

For example, in complex search queries, we may create a temp table and save search result rows to it. In this case, stored proc should be much more ideal. But if you want to select 10 to 15 rows based on user input values (on the fly), then direct query
should be easier to handle.

Re: Asp.net and SQL server - Stored procedure

Bader

Regarding using the SQL Server stored procedure... What is the best way (Prefferd) to use, put all sql commnads as a stored procedure, and when they are needed just to call the stored procedure (View code 1)? or just to put a complex sql syntax - commnads - in
a stored procedure, and all other simple sql commnads

the preferred would be to use stored procedures for most cases...

even if you use sql queries directly into asp.net code... even then u should avoid to pass parameters inside query as a string... like this

Re: Asp.net and SQL server - Stored procedure

The benefits of using stored procedures in SQL Server rather than application code stored locally on client computers include:

They allow modular programming.

They allow faster execution.

They can reduce network traffic.

They can be used as a security mechanism.

You can create a stored procedure once, store it in the database, and call it any number of times in your program.You can perform an operation that requires hundreds of lines of T-SQL code through a single
statement that executes the code in a procedure, rather than by sending hundreds of lines of code over the network.

Placing SQL in your code:

While stored procedures offer a number of benefits, placing SQL directly in your application code isn't necessarily wrong. One excellent example is SQL that is generated on the fly. Its dynamic nature negates many of the advantages
of a stored procedure.

That is, you cannot create and cache an execution plan since it's different each time it's called. In this scenario, you face assembling the SQL in your code or using the T-SQL exec command (or the sp_executesql system stored procedure).
The difference with this scenario is where the code is executed (i.e., the server), so the final decision will depend on your environment.

Another situation in which you might have to decide between constructing dynamic T-SQL in your application or on the database server is the passing of data to the stored procedure. The dynamic nature of the database call implies
that you must base your decision on data values and so forth. For instance, do you want to pass data values to a backend stored procedure, or use the values in the application code to construct the T-SQL string?