Web developers notes,programming tutorials.

Stored procedure

A stored procedure is a program (or procedure) which is physically stored within a database. They are usually written in a database language. The advantage of a stored procedure is that when it is run, in response to a user request, it is run directly by the database engine, which usually runs on a separate database server. As such, it has direct access to the data it needs to manipulate and only needs to send its results back to the user, doing away with the overhead of communicating large amounts of data back and forth.

User-defined function

A user-defined function is a routine that encapsulates useful logic for use in other queries. While views are limited to a single SELECT statement, user-defined functions can have multiple SELECT statements and provide more powerful logic than is possible with views.

User defined functions have 3 main categories

Scalar-valued function - returns a scalar value such as an integer or a timestamp. Can be used as column name in queries

Inline function - can contain a single SELECT statement.

Table-valued function - can contain any number of statements that populate the table variable to be returned. They become handy when you need to return a set of rows, but you can’t enclose the logic for getting this rowset in a single SELECT statement.

Differences between Stored procedure and User defined functions

UDF can be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section where as Stored procedures cannot be.

UDFs that return tables can be treated as another rowset. This can be used in JOINs with other tables.

Inline UDF’s can be though of as views that take parameters and can be used in JOINs and other Rowset operations.

Example of Stored Procedures and User Defined Functions

CREATE PROCEDURE dbo.SP_example /*
(
@param1 datatype = default value,
@param2 datatype OUTPUT
)
*/
AS
/* SET NOCOUNT ON */
RETURN

CREATE FUNCTION dbo.function_example (
/*
@param1 datatype = default value,
@param2 datatype
*/
)
RETURNS /* datatype */
AS
BEGIN
/* sql statement … */
RETURN /* value */
END

Errors may occur in T-SQL in several possible ways, including hardware failures, network failures, bugs in programs, out of memory and other reasons.

We may not know which error would at anytime. But we need to handle all such errors and provide some meaningful messages to the user instead of making the user confused with error messages that are impossible to understand.

An exception is generally a runtime error which gets raised by SQL Server runtime when a T-SQL block is in the process of execution. Handling the exception is something like trapping the error (or exception) and inserting that error into the error_log table including date, error message, and other details. Storing error messages in the error_log table makes it easy to trace for future maintenance.

It doesn’t mean that errors are in the table only for maintenance; we can take certain actions programmatically when an error occurs. Error handling is a very monotonous task and we should make it as simple as possible. If error handling is too complex, bugs might creep into the error handling and should be tested after each statement.

Another special case is the use of transactions. We need to issue a “ROLLBACK TRANSACTION” to undo a transaction when an error creeps in.

Example :

Lets have a Simple Tables mytable and myerrorlog
————————————————

CREATE TABLE [dbo].[mytable] (
[myid] [int] NOT NULL ,
[myname] [varchar] (50),
[age] [int] NULL ,
[gender] [varchar](50) NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[myerrorlog]
(
[LogDate] [datetime] NULL ,
[Source] [varchar] (50),
[ErrMsg] [nvarchar] (255) ,
[Remarks] [varchar] (50)
)
GO

Stored Procedure in SQL SERVER 2005 with Exception Handling
———————————————————

createprocedure [dbo].[sp_mytable_insert]
(
@myid int,
@myname varchar(20),
@age int,
@gender varchar(20)
)
as
begin

end

Explanation
———–

The statements between “begin try” and “end try” will be simply TRIED by SQL Server run time to execute. If the statements between “begin try” and “end try” get executed successfully without any errors, the transaction is saved successfully with the help of COMMIT TRANSACTION.

If any error occurs while executing the statements between “begin try” and “end try,” the flow of execution automatically gets jumped to the “catch” block. Within the “catch” block (or between “begin catch” and “end catch”), we simply rollback (in other words, cancel) the transaction and finally log the error to the “error_log” table using a simple INSERT statement (as above). Another flexibility in SQL Server 2005 is the “error_message().” It gives us the immediate error message that occurred.

From the above code, you can observe that we are trying to eliminate all “goto” statements and make it “structured.” This really makes our script easily understandable to the depth of any number of exceptions.

Writing code that generates correct SQL code for calling stored procedures, or with UPDATE and INSERT statements can often be a real pain - especially when worrying about escaping strings, or getting date formats correct. Using the SqlCommand/OleDbCommand object allows us to provide a SQL statement with variables waiting to be filled - and we can then set the value of these variables seperately in our code, and let ADO.NET worry about sending it to the database correctly! First, we create the SqlCommand object as usual. If we are calling a stored procedure, then we just need to provide its name.

Calling stored procedure in C#

Create connectionString variable for sql and assign it to sqlconnection object. 

SqlConnection conn = new SqlConnection(connectionString); 

SqlCommand cmd = new SqlCommand(”SP_Insert”,conn);

//where SP_Insert is the name of the stored procedure

cmd.CommandType = CommandType.StoredProcedure;

// tell the command that this is a stored procedure

We specify the parameters by adding SqlParameter/OleDbParameter objects to the Command object’s Parameters property. The constructor we’ll use here accepts a string for the parameters name, and an OleDbType or SqlDbType for the data type. We then add this new Parameter object to the collection, and set its Value property:

cmd.Parameters.Add(New SqlParameter(”@fname”,SqlDBType.Varchar(255)));

cmd.Parameters.Add(New SqParameter(”@lname”,SqlDBType.Varchar(255)));

cmd.Parameters[”@fname”].Value = “Gary”;

cmd.Parameters[”@lname”].Value = “Harwell”;

cmd.ExecuteNonQuery();

Creating the stored procedure in sqlserver 2005

create procedure SP_Insert

@fname varchar(max),@lname varchar(maX)

as insert into tbluser values(@fname,@lname) 

For altering the procedures alter keyword is used in place of create.