Stored Procedure

Question

hi friends i know syntax of stored procedure but what are the place were write it-sql server mgmt studio or server explorer in VSor .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

Riyaz Akhtar · Answer

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(EmpcodeFName 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();

Sunny Sharma · Answer

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.

Iftikar Hussain · Answer

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