Microsoft SQL Server
Хранимые процедуры

Вступление

Синтаксис

• СОЗДАТЬ {ПРОЦЕДУРА | PROC} [имя_схемы.] Имя_процесса
• [@parameter [type_schema_name.] тип данных
• [VARYING] [= по умолчанию] [OUT | ВЫХОД | READONLY]
• , @parameter [type_schema_name.] datatype
• [VARYING] [= по умолчанию] [OUT | ВЫХОД | READONLY]]
• [WITH {ENCRYPTION | РЕКОМЕНДУЕМ | ВЫПОЛНИТЬ КАК пункт}]
• [ДЛЯ РЕПЛИКАЦИИ]
• КАК
• НАЧАТЬ
• [Declaration_section]
• executable_section
• КОНЕЦ;

Создание и выполнение базовой хранимой процедуры

Использование таблицы « Authors в базе данных библиотеки

CREATE PROCEDURE GetName
(
    @input_id INT = NULL,      --Input parameter,  id of the person, NULL default
    @name VARCHAR(128) = NULL  --Input parameter, name of the person, NULL default
) 
AS
BEGIN 
    SELECT Name + ' is from ' + Country 
    FROM Authors 
    WHERE Id = @input_id OR Name = @name
END 
GO

Вы можете выполнить процедуру с несколькими различными синтаксисами. Во-первых, вы можете использовать EXECUTE или EXEC

EXECUTE GetName @id = 1
EXEC Getname @name = 'Ernest Hemingway'

Кроме того, вы можете опустить команду EXEC. Кроме того, вам не нужно указывать, какой параметр вы передаете, когда вы передаете все параметры.

GetName NULL, 'Ernest Hemingway'

Если вы хотите указать входные параметры в другом порядке, чем то, как они объявлены в процедуре, вы можете указать имя параметра и присвоить значения. Например

 CREATE PROCEDURE dbo.sProcTemp 
 (
    @Param1 INT,
    @Param2 INT
)
AS
BEGIN

    SELECT
        Param1 = @Param1,
        Param2 = @Param2

END

нормальный порядок выполнения этой процедуры - сначала указать значение для параметра @ Param1, а затем @ Param2 second. Так что это будет выглядеть примерно так

  EXEC dbo.sProcTemp @Param1 = 0,@Param2=1

Но также возможно, что вы можете использовать следующие

  EXEC dbo.sProcTemp @Param2 = 0,@Param1=1

в этом случае вы указываете значение для параметра @ param2 first и @ Param1 second. Это означает, что вам не нужно сохранять тот же порядок, что и в процедуре, но вы можете заказать любой заказ по своему усмотрению. но вам нужно указать, к какому параметру вы устанавливаете значение

Доступ к хранимой процедуре из любой базы данных

А также вы можете создать процедуру с префиксом sp_ эти procuedres, как и все системные хранимые процедуры, могут быть выполнены без указания базы данных из-за поведения SQL Server по умолчанию. Когда вы выполняете хранимую процедуру, которая начинается с «sp_», SQL Server сначала ищет процедуру в основной базе данных. Если процедура не найдена в master, она просматривается в активной базе данных. Если у вас есть хранимая процедура, к которой вы хотите получить доступ из всех ваших баз данных, создайте ее в главном и используйте имя, которое включает префикс «sp_».

Use Master

CREATE PROCEDURE sp_GetName
(
    @input_id INT = NULL,      --Input parameter,  id of the person, NULL default
    @name VARCHAR(128) = NULL  --Input parameter, name of the person, NULL default
) 
AS
BEGIN 
    SELECT Name + ' is from ' + Country 
    FROM Authors 
    WHERE Id = @input_id OR Name = @name
END 
GO

ЗАПОМНЕННАЯ ПРОЦЕДУРА с параметрами OUT

Хранимые процедуры могут возвращать значения, используя ключевое слово OUTPUT в списке параметров.

Создание хранимой процедуры с одним параметром out

CREATE PROCEDURE SprocWithOutParams
(
    @InParam VARCHAR(30),
    @OutParam VARCHAR(30) OUTPUT
)
AS
BEGIN
    SELECT @OutParam = @InParam + ' must come out'   
    RETURN
END   
GO

Выполнение хранимой процедуры

DECLARE @OutParam VARCHAR(30)    
EXECUTE SprocWithOutParams 'what goes in', @OutParam OUTPUT   
PRINT @OutParam 

Создание хранимой процедуры с несколькими параметрами

CREATE PROCEDURE SprocWithOutParams2
(
    @InParam VARCHAR(30),
    @OutParam VARCHAR(30) OUTPUT,
    @OutParam2 VARCHAR(30) OUTPUT
)
AS
BEGIN
    SELECT @OutParam = @InParam +' must come out'   
    SELECT @OutParam2 = @InParam +' must come out'
    RETURN
END    
GO

Выполнение хранимой процедуры

DECLARE @OutParam VARCHAR(30)    
DECLARE @OutParam2 VARCHAR(30)  
EXECUTE SprocWithOutParams2 'what goes in', @OutParam OUTPUT, @OutParam2 OUTPUT   
PRINT @OutParam 
PRINT @OutParam2

Сохраненная процедура с If ... Else и Insert Into operation

Создать таблицу примеров Employee :

CREATE TABLE Employee
(
    Id INT,
    EmpName VARCHAR(25),
    EmpGender VARCHAR(6),
    EmpDeptId INT
)

Создает хранимую процедуру, которая проверяет, не являются ли значения, переданные в хранимой процедуре, нулевыми или не пустыми и выполняют операцию вставки в таблице Employee.

CREATE PROCEDURE spSetEmployeeDetails
(
    @ID int,
    @Name VARCHAR(25),
    @Gender VARCHAR(6),
    @DeptId INT
)
AS
BEGIN
    IF (
        (@ID IS NOT NULL AND LEN(@ID) !=0) 
        AND (@Name IS NOT NULL AND LEN(@Name) !=0)
        AND (@Gender IS NOT NULL AND LEN(@Gender) !=0)
        AND (@DeptId IS NOT NULL AND LEN(@DeptId) !=0)
    )
    BEGIN
        INSERT INTO Employee
        (
            Id,
            EmpName,
            EmpGender,
            EmpDeptId
        )
        VALUES
        (
            @ID,
            @Name,
            @Gender,
            @DeptId
        )
    END
ELSE
    PRINT 'Incorrect Parameters'
END
GO

Выполнение хранимой процедуры

DECLARE @ID INT,
    @Name VARCHAR(25),
    @Gender VARCHAR(6),
    @DeptId INT

EXECUTE spSetEmployeeDetails
    @ID = 1,
    @Name = 'Subin Nepal',
    @Gender = 'Male',
    @DeptId = 182666 

Динамический SQL в хранимой процедуре

Dynamic SQL позволяет создавать и запускать SQL-запросы во время выполнения. Динамический SQL необходим, когда наши операторы SQL содержат идентификатор, который может меняться в разное время компиляции.

Простой пример динамического SQL:

CREATE PROC sp_dynamicSQL
@table_name      NVARCHAR(20),
@col_name        NVARCHAR(20), 
@col_value       NVARCHAR(20) 
AS
BEGIN
DECLARE  @Query  NVARCHAR(max)
SET      @Query = 'SELECT * FROM ' + @table_name
SET      @Query = @Query + ' WHERE ' + @col_name + ' = ' + ''''+@col_value+''''
EXEC     (@Query)
END

В приведенном выше sql-запросе мы можем видеть, что мы можем использовать вышеуказанный запрос, определяя значения в @table_name, @col_name, and @col_value во время выполнения. Запрос генерируется во время выполнения и выполняется. Это метод, в котором мы можем создавать целые скрипты в виде строки в переменной и выполнять ее. Мы можем создавать более сложные запросы с использованием динамической концепции SQL и конкатенации. Эта концепция очень эффективна, если вы хотите создать сценарий, который можно использовать в нескольких условиях.

Выполнение хранимой процедуры

DECLARE @table_name     NVARCHAR(20) = 'ITCompanyInNepal',
        @col_name       NVARCHAR(20) = 'Headquarter',
        @col_value      NVARCHAR(20) = 'USA'
    
EXEC    sp_dynamicSQL   @table_name,    
                        @col_name,
                        @col_value

Таблица, которую я использовал

Выход

Простая петля

Сначала можно получить некоторые данные в таблице temp с именем #systables и #systables число увеличивающихся строк, чтобы мы могли запросить одну запись за раз

select
    o.name,
    row_number() over (order by o.name) as rn
into
    #systables
from
    sys.objects as o
where
    o.type = 'S'

Далее мы объявляем некоторые переменные для управления циклом и хранения имени таблицы в этом примере

declare
    @rn int = 1,
    @maxRn int = (
                    select
                        max(rn)
                    from
                        #systables as s
                    )
declare    @tablename sys name

Теперь мы можем циклично использовать простую. Мы увеличиваем @rn в выражении select но это также может быть отдельный оператор ex set @rn = @rn + 1 который будет зависеть от ваших требований. Мы также используем значение @rn до того, как оно будет увеличено, чтобы выбрать одну запись из #systables . Наконец, мы печатаем имя таблицы.

while @rn <= @maxRn
    begin

        select
            @tablename = name,
            @rn = @rn + 1
        from
            #systables as s
        where
            s.rn = @rn

        print @tablename
    end

Простая петля

CREATE PROCEDURE SprocWithSimpleLoop
(
    @SayThis VARCHAR(30),
    @ThisManyTimes INT
)
AS
BEGIN
    WHILE @ThisManyTimes > 0
    BEGIN
        PRINT @SayThis;
        SET @ThisManyTimes = @ThisManyTimes - 1;
    END
    
    RETURN;
END    
GO