SQL – Збережена процедура

Transact-SQL

• SQL – змінні @
• if-else
• for, while, do-while

Процедури – нічого не повертають;  функції – повертають значення

Rutine name: getAuthors

Parameters: +/-

Definition: SELECT * FROM authors;

SQL data sccess: CONTAINS SQL

Натискаємо Go

 

---

 

Microsft SQL Server Management Studio

USE Anketa
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
— =============================================
— Author: <Author,,Name>
— Create date: <Create Date,,>
— Description: <Description,,>
— =============================================
CREATE PROCEDURE proc_Add
— Add the parameters for the stored procedure here
@last nvarchar(50),
@first nvarchar(50)

AS
BEGIN
— SET NOCOUNT ON added to prevent extra result sets from
— interfering with SELECT statements.
SET NOCOUNT ON;

— Insert statements for procedure here
INSERT INTO Table_3 ([LastName], [FirstName]) VALUES (@last, @first)
END
GO

 

F5 – один раз, зберегти
CREATE -> ALTER (створити -> модифікувати)

 

---

C#

SqlConnection cn_connection = new SqlConnection(Properties.Settings.Default.cn);
SqlCommand scCommand = new SqlCommand("proc_Add", cn_connection);
scCommand.CommandType = CommandType.StoredProcedure;
scCommand.Parameters.Add("@last", SqlDbType.NVarChar, 50).Value = tb1.Text;
scCommand.Parameters.Add("@first", SqlDbType.NVarChar, 50).Value = tb2.Text;
try
{
     if (scCommand.Connection.State == ConnectionState.Closed)
     {
         scCommand.Connection.Open();
     }
     scCommand.ExecuteNonQuery();
}
catch (Exception ex)
{
     MessageBox.Show(ex.ToString());
}
finally
{
     scCommand.Connection.Close();
}