Posts Tagged ‘ stored procedure ’

Cursor with Stored Procedure in MySQL Server

Let’s start with Cursor directly. If you are not familiar with MySQL Stored Procedure, have a look at this posting first. Cursor is used to navigate thorough Result Set returned by a Query.

First of all, Cursor has to be declared. Cursor declaration is very simple.

DECLARE cursor_name CURSOR FOR select_statement;

Now, you need to use OPEN Command to start using this Cursor.

OPEN cursor_name;
Continue reading

Advertisements

Stored Procedure in MySQL Server

Stored Procedure is a collection of SQL commands and this is not executed until or unless called but compiled at the time of creation. Thus, this helps to reduce execution time and increases Data Integrity as well. In MySQL Server, Stored Procedure accepts three types of argument.

1) IN
2) OUT
3) INOUT
Continue reading

MySQL Stored Procedure, Function Problems with PHPMyAdmin

I wasted almost a day trying to write a Stored Procedure in MySQL using PHPMyAdmin. The first lesson I learnt is it’s not a good idea to start writing your Stored Procedure or User Defined Function using PHPMyAdmin. Try to download any of the free MySQL GUI Tool; I assure you will have a lot easier living with MySQL Database.
Continue reading

how to use SQL Server Stored Procedure in C#, VB6

Using Stored Procedure to insert data is sometimes proved worthy in case of robust applications. More appropriately, it is at least better than creating a long SQL insert query string for database interaction. However, using Stored Procedure in C# is very simple and effective as explained below.


CREATE PROCEDURE udp_InsertGuest
@guest_Name varchar50),
@guest_Address varchar(50),
@guest_PhoneNumber varchar(50),
@guest_VisitPurpose varchar(50),
@guest_Id int output
as
BEGIN
  insert into
t_Guest
 (guest_Name,guest_Address,guest_PhoneNumber,
  guest_VisitPurpose,guest_EntryDateAndTime)
  values
 (@guest_Name,@guest_Addresss,@guest_PhoneNumber,
  @guest_VisitPurpose, getdate ())
  set @guest_Id = SCOPE_IDENTITY ()
END


First of all, I have written Stored Procedure udp_InsertGuest to insert data in t_Guest table that returns identity of last inserted row as well.


sqlConnection = DbConnector.GetConnection;
sqlConnection.Open();
sqlCommand = new SqlCommand(“udp_InsertGuest”,sqlConnection);
sqlCommand.CommandType = CommandType.StoredProcedure;


Here, I have created sqlCommand object of SqlCommand class using Stored Procedure and Database Connection as parameters as well as assigned Command Type to it.


sqlCommand.Parameters.Add(“@guest_Name”, SqlDbType.VarChar, 50);
sqlCommand.Parameters[“@guest_Name”].Value = guestObj.Name;

sqlCommand.Parameters.Add(“@guest_Address”, SqlDbType.VarChar, 50);
sqlCommand.Parameters[“@guest_Address”].Value = guestObj.Address;

sqlCommand.Parameters.Add(“@guest_PhoneNumber”, SqlDbType.VarChar, 50);
sqlCommand.Parameters[“@guest_PhoneNumber”].Value = guestObj.Phone;

sqlCommand.Parameters.Add(“@guest_VisitPurpose”, SqlDbType.VarChar, 50);
sqlCommand.Parameters[“@guest_VisitPurpose”].Value = guestObj.VisitCause;

sqlCommand.Parameters.Add(“@guest_Id”, SqlDbType.Int);
sqlCommand.Parameters[“@guest_Id”].Direction = ParameterDirection.Output;


This code snippet shows how to add parameters and assign values in sqlCommand object. Please follow the output parameter requires a direction assignment.


sqlCommand.ExecuteNonQuery();
int guestId = (int)sqlCommand.Parameters[“@guest_Id”].Value;


Finally, after sqlCommand’s execution, it’s quite easy to retrieve the value of output parameter.



Dim guestId As Integer

'Creating Command
Dim command As ADODB.command
Set command = New ADODB.command
command.CommandType = adCmdStoredProc
command.ActiveConnection = ProjectConnection
command.CommandText = "udp_InsertGuest"

'Adding Input Parameters
command.Parameters.Append command.CreateParameter("guest_Name", adVarChar, adParamInput, 50, guestName)
command.Parameters.Append command.CreateParameter("guest_Address", adVarChar, adParamInput, 50, guestAddress)
command.Parameters.Append command.CreateParameter("guest_PhoneNumber", adVarChar, adParamInput, 50, guestPhoneNumber)
command.Parameters.Append command.CreateParameter("guest_VisitPurpose", adVarChar, adParamInput, 50, guestVisitPurpose)

'Adding Output Parameters
command.Parameters.Append command.CreateParameter("guest_id", adInteger, adParamOutput)

'Executing Command
command.Execute

'Retrieving Output Parameters
guestId = command("guest_Id")


This code snippet shows how to use stored procedure with VB6.

Thanks
A Rahim Khan

How to Write Stored Procedure in SQL Server 2005

I always prefer writing stored procedure over writing query to insert data in table. If you have an auto-increment field in table as key, you will require this key value of last inserted data for various purposes. Writing stored procedure is the simplest way to address this scenario.

//Create Table
create table t_EmployeeInformation
(
employee_ID int identity(1,1) NOT NULL,
employee_Name varchar(50),
employee_EmailID varchar(20)
)

//Create Procedure
create procedure p_InsertEmployee
@employee_Name varchar(50),
@employee_EmailID varchar(40),
@employee_ID int output
as
begin
insert into t_EmployeeInformation(employee_Name,employee_EmailID)
values(@employee_Name,@employee_EmailID)
set @employee_ID = SCOPE_IDENTITY()
end

//Use Procedure to Insert Data
declare @employee_ID int
exec p_InsertEmployee
'khan', 'khan.rahim@gmail.com',@employee_ID out
select @employee_ID

Thanks
A Rahim Khan