Stored Procedure

Arun Kurmi

13y1.5k

hi friends,
i know syntax of stored procedure
but what are the place were write it-sql server mgmt studio or server explorer in VS,or .cs file of code. and how to use it
please provide solution step by step

for ex. i have a syntax for stored procedure below. where write this and how to use this....
suppose i want store text of TextBox1 on btn1 click

Create procedure InsertProc(
@name as string)
as
insert into user_tbl(name)values(@name)
go

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Riyaz Akhtar13y ago
Check out below Code:

'Create the following table in sql server
CREATE TABLE abc(
EmpCode numeric(18, 0) NOT NULL,
FName nvarchar(50),
LName nvarchar(50)
)

'Create the following PROCEDURE sql server

Create PROCEDURE procemp
@Empcode numeric,
@FName nvarchar(100),
@LName nvarchar(100),

AS
BEGIN()
Insert into abc(Empcode,FName, LName) values (@Empcode,@FName, @LName)
End

'C#

SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Empcode", 101);
cmd.Parameters.AddWithValue("@FName", "Sachin");
cmd.Parameters.AddWithValue("@LName", "Sharma");
cmd.Connection = con;
if (con.State == ConnectionState.Closed) {
con.Open();
}
cmd.ExecuteNonQuery();
Iftikar Hussain13y ago
Hi,
Please refer the following link

http://www.plsql-tutorial.com/plsql-procedures.htm

Regards,
Iftikar

Remember to click "Mark as Answer" on the post, if it helps you
Sunny Sharma13y ago
Hi Arun,

To write Stored Procedure for SQL Server SQL Server Management Studio is an obvious option. Go for it if you have your database in On SQL Server. You can also use SSMS in case if you have a .MDF file included in your Visual Studio project. You need to attach your file in SSMS and you can go ahead with manipulating your database file.

Steps to attach an .MDF file in SSMS:

Start SSMS > Right click on databases > Click Attach > Add > Select .MDF from your App_data folder location > Click OK.

If everything goes well, you'll see a database has been added in SSMS. Now do anything you want.

Modify your SP Creation Script as below:
--------------------------------------
CREATE PROCEDURE InsertProc(@name AS VARCHAR(100))
AS
BEGIN
INSERT INTO user_tbl(name)VALUES(@name);
END
GO
--------------------------------------

Below is the sample code to use Stored Procedure:
--------------------------------------------------------------------

string ConStr = "Server=DEMO-PC\\SQLEXPRESS;Database=myTable;Integrated Security=true;";
SqlConnection MyCon = new SqlConnection(ConStr);
SqlCommand cmd = new SqlCommand("InsertProc", MyCon); //QueryString would contain only the name of your SP.
cmd.CommandType = CommandType.StoredProcedure; //Here you set the type of command;

try
{
MyCon.Open();
}
catch(Exception ex)
{
Console.WriteLine("Database connection error: " + ex.Message);

}
if (MyCon.State == ConnectionState.Open)
{
cmd.Parameters.AddWithValue("@name", "Arun");
try
{
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
//handle the error.
}
}

if (MyCon.State == ConnectionState.Open)
{
try
{
MyCon.Close();
}
catch { }
}
-------------------------------------------------

Happy Coding :)

Do mark this as answer if it helps.

Thanks.