Store Procedure..............

Function:-

Function is mainly used in the case where it must return a value. Where as a procedure may or may not return a value or may return more than one value using the OUT parameter.

Function can be called from SQL statements where as procedure can not be called from the sql statements

Functions are normally used for computations where as procedures are normally used for executing business logic.

You can have DML (insert,update, delete) statements in a function. But, you cannot call such a function in a SQL query.

Function returns 1 value only. Procedure can return multiple values (max 1024).

Stored Procedure: supports deferred name resolution. Example while writing a stored procedure that uses table named tabl1 and tabl2 etc..but actually not exists in database is allowed only in during creation but runtime throws error Function wont support deferred name resolution.


Stored procedure:-

Stored procedure returns always integer value by default zero. where as function return type could be scalar or table or table values

Stored procedure is precompiled execution plan where as functions are not.

A procedure may modify an object where a function can only return a value The RETURN statement immediately completes the execution of a subprogram and returns control to the caller.




Create Table:-

Create Table OwnerDetails (OwnerId int Identity(1,1) Primary Key, OwnerFirstName nvarchar(255), OwnerMiddleName nvarchar(255), OwnerLastName nvarchar(255));

Create Table VehicalDetails (VehicalId int identity(1,1) Primary Key, OwnerId int Foreign Key References OwnerDetails(OwnerId))

Alter Table VehicalDetails Add VehicalName nvarchar(255), ChechisNo int, VehicalNo nvarchar(255)

---------------
Writting Procedure:-

 To Insert
Create Proc usp_AddOwnerDetails
@OwnerFirstName nvarchar(255),
@OwnerMiddleName nvarchar(255),
@OwnerLastName nvarchar(255)
as begin
insert into OwnerDetails(OwnerFirstName,OwnerMiddleName,OwnerLastName)Values(@OwnerFirstName,@OwnerMiddleName,@OwnerLastName)
Select Scope_Identity( )
end
-----------------------------

 To Update

Create Proc usp_UpdateOwnerDetails
@OwnerFirstName nvarchar(255),
@OwnerMiddleName nvarchar(255),
@OwnerLastName nvarchar(255),
@OwnerId int
as begin
Update OwnerDetails set OwnerFirstName=@OwnerFirstName,OwnerMiddleName=@OwnerMiddleName,OwnerLastName=@OwnerLastName where OwnerId=@OwnerId
end

-----------------------------------
 ToDelete

Create Proc usp_DeleteOwnerDetails
@OwnerId int
as begin
Delete From OwnerDetails where OwnerId=@OwnerId
end

------------------------------------------
 To Search

Create Proc usp_SearchOwnerDetails
@OwnerId int
as begin
Select* From OwnerDetails where OwnerId=@OwnerId
end


----------------------------------
 To GridView:-
SP for GridView By FirstName:-

Create proc usp_GetOwnerDetailsForGridview
@OwnerFirstName nvarchar(255)
as
begin
Select o.OwnerId,o.OwnerFirstName,o.OwnerMiddleName,o.OwnerLastName,v.VehicalName,v.VehicalNo,v.ChechisNo from OwnerDetails o inner join VehicalDetails v on v.OwnerId=o.OwnerId where o.OwnerFirstName like '%'+@OwnerFirstName+'%'
end

----------------------