Инструкция output в transact-sql

SQL Server stored procedure output parameter returns null

An output parameter of a stored procedure will always return NULL when we do not assign or modify the output parameter and still try to fetch the value of it.

Let’s understand this situation with the help of the example given below.

In the above example, we are creating a store procedure that will insert a new record in the employee table. And for this, we have declared 3 input parameters and one output parameter, and we are using the 3 input parameters to assign values to be inserted. But we didn’t use or assign any value in the output parameter.

Now, while executing the stored procedure, if we try to fetch the value of the output parameter, it will return NULL. As we didn’t assign any value to the output parameter in the procedure.

NULL result

You may like the following SQL Server tutorials:

• SQL Server Create Temp Table
• Delete Duplicate Rows in SQL Server
• SQL Server Row_Number
• SQL Server select from stored procedure
• SQL Server stored procedure vs function
• Stored procedure in SQL Server for insert and update

So in this tutorial, we have learned what is Output Parameters in SQL Server Stored Procedure, How to use Output parameters in a stored procedure, we have also covered the below topics.

• SQL Server stored procedure output parameter
• SQL Server stored procedure output parameter data types
• SQL Server stored procedure assign value to output parameter
• SQL Server stored procedure output parameter default value
• SQL Server stored procedure output parameter table variable
• SQL Server stored procedure output parameter returns null
• SQL Server stored procedure output parameter varchar
• SQL Server stored procedure output parameter varchar(max)
• SQL Server stored procedure output parameter cursor
• SQL Server stored procedure output parameter @@identity

Изменение функций

Вы можете изменять функцию с помощью оператора ALTER FUNCTION. Общий вид для каждого варианта функции отличается. Давайте рассмотрим каждый из них.

1. Общий вид команды изменения скалярной функции:

ALTER FUNCTION  function_name 
    (  }  ] ) 

RETURNS scalar_return_data_type

 ] 



BEGIN 
    function_body 
    RETURN scalar_expression
END

2. Общий вид изменения функции, возвращающей таблицу:

ALTER FUNCTION  function_name 
    (  }  ] ) 

RETURNS TABLE 

 ] 

 

RETURN  select-stmt  

3. Общий вид команды изменения функции с множеством операторов, возвращающей таблицу.

ALTER FUNCTION  function_name 
    (  }  ] ) 

RETURNS @return_variable TABLE  

 ] 

 

BEGIN 
    function_body 
    RETURN
END

 ::= 
    { ENCRYPTION | SCHEMABINDING }

 :: = 
    ( { column_definition | table_constraint }  ) 

Следующий пример показывает упрощенный вариант команды, изменяющей функцию:

ALTER FUNCTION dbo.tbPeoples
AS
 -- эовое тело функции

Getting Started

In order to follow the below examples, you will need to create a table Department_SRC by executing the below T-SQL code in a SQL Server Database. 

IF OBJECT_ID ('Department_SRC', 'U') IS NOT NULL
DROP TABLE dbo.Department_SRC;
CREATE TABLE .(
         IDENTITY(1,1) NOT NULL,
        varchar(50)  NOT NULL,
        varchar(50) NOT NULL,
         NOT NULL
) ON 
Insert into .(,,)
 Values('Engineering','Research and Development',getdate());

OUTPUT gives access to two virtual tables (Magic Tables). These are:

1. “INSERTED” contains the new rows (INSERT or UPDATE‘s SET)
2. “DELETED” contains the old copy of the rows(empty for INSERT)

Implement the OUTPUT Clause in Insert Statements

Using the OUTPUT clause, we can display the rows inserted into a table in the output window by selecting the column names with the INSERTED prefix or using INSERTED.* to display all the columns. This will also help in printing out some of the output messages on the management studio window for confirmations.

You can see the result of the OUTPUT clause in below image.

Inserting OUTPUT results into a Table Variable

In order to showcase this example I declared table variable called @Inserted to store the results of the OUTPUT clause. We use the OUTPUT INTO syntax to store the results in a table variable. With this approach, we have access to the set of rows that were inserted during the execution of the query that can be used later for the next steps with in the same execution.

You can see that the results has inserted into the @Inserted table as shown in below image.

Use of OUTPUT Clause for Tables with an Identity Column

The OUTPUT clause will have access to all the columns in the table, even those not part of your Insert statement. For example, in the above table, Department_SRC, there is a column, DepartmentID, which is an identity column. While inserting any new records into the Department_SRC table, DepartmentID is not part of the INSERT list. However, we have access to the DepartmentID column in the OUTPUT clause and can view or log the value of DepartmentID that was inserted in that statement.

You can see that the value of identity column DepartmentID along with the other columns was returned as part of OUTPUT clause as shown in below image.

With the above shown example, the OUTPUT clause is a very good alternative to SCOPE_IDENTITY(), and maybe even better. Referring to Books Online and real time scenarios we can make an assumption with this statement: There is almost no reason to use anything besides an OUTPUT clause when trying to get the identity of the row(s) just inserted. The OUTPUT clause is scope and table safe.

Примеры использования инструкции OUTPUT в T-SQL

Сейчас мы разберем примеры использования инструкции OUTPUT в сочетании с INSERT, UPDATE и DELETE, пример с MERGE мы подробно рассматривали в материале – Операция MERGE в языке Transact-SQL – описание и примеры, поэтому здесь я не буду повторяться.

Исходные данные для примеров

Сначала давайте создадим тестовые данные, а именно таблицу, с названием TestTable, именно данные в ней мы и будем изменять.

В качестве сервера у меня выступает Microsoft SQL Server 2016 Express.

   
   --Создание таблицы
   CREATE TABLE TestTable(
           IDENTITY(1,1) NOT NULL,
          NOT NULL,
        (100) NOT NULL,
               NULL
   )
   GO

Пример использования OUTPUT в сочетании с INSERT

В этом примере мы добавим три строки и сразу вернем результат (добавленные строки) инструкцией OUTPUT, для этого мы обратимся к таблице Inserted (т.е. укажем префикс).

Инструкция OUTPUT указывается после инструкции INSERT и определения целевой таблицы.

  
   INSERT INTO TestTable
    OUTPUT Inserted.ProductId,
           Inserted.CategoryId,
           Inserted.ProductName,
           Inserted.Price
    VALUES (1, 'Клавиатура', 150),
           (1, 'Мышь', 50),
           (2, 'Телефон', 300)

Пример использования OUTPUT в сочетании с UPDATE

В случае с обновлением данных (UPDATE) мы уже можем обращаться и к Inserted и к Deleted, для того чтобы получить как новые, так и старые значения. OUTPUT также указывается после модифицирующей инструкции, в этом случае после UPDATE, стоит отметить, что условие WHERE мы пишем после инструкции OUTPUT.

   
   UPDATE TestTable SET Price = Price + 10
     OUTPUT Inserted.ProductId AS ,
            Deleted.Price AS ,
            Inserted.Price AS 
     WHERE Price < 200

Пример использования OUTPUT в сочетании с UPDATE и конструкцией INTO

В данном примере я покажу, как можно сохранить результирующий набор, который нам возвращает инструкция OUTPUT, например, для того чтобы в дальнейшем проанализировать произведенные изменения. Это можно сделать с помощью указания конструкции INTO, принцип ее работы примерно такой же, как и у конструкции SELECT INTO, т.е. мы после указания списка выборки пишем INTO и название целевой таблицы (или табличной переменной).

В примере ниже результат сохраняется в табличной переменной, которая предварительно объявляется.

   
   --Объявление табличной переменной
   DECLARE @TmpTable TABLE (ProductId INT, PriceOld Money, PriceNew Money);

   --Выполнение UPDATE с инструкцией OUTPUT
   UPDATE TestTable SET Price = Price + 10
        OUTPUT Inserted.ProductId AS ,
               Deleted.Price AS ,
               Inserted.Price AS 
        INTO @TmpTable (ProductId, PriceOld, PriceNew) --Сохраняем результат в табличной переменной
   WHERE Price < 200

   --Можем анализировать сохраненные данные
   SELECT * FROM @TmpTable

Пример использования OUTPUT в сочетании с DELETE

Если в инструкции OUTPUT обратиться к таблицам Inserted или Deleted указав *, то, как и в случае с SELECT, выведутся все столбцы таблицы. В следующем примере, для того чтобы посмотреть значения всех столбцов удаленных строк, указан символ *.

   
   DELETE TestTable
       OUTPUT Deleted.*
   WHERE ProductId < 3

Инструкцию OUTPUT языка T-SQL мы рассмотрели, надеюсь, материал был Вам полезен. Всем тем, кто только начинает свое знакомство с языком SQL, рекомендую прочитать книгу SQL код» – это самоучитель по языку SQL, которую написал я, и в которой я подробно, и в то же время простым языком, рассказываю о языке SQL, удачи!

Нравится21Не нравится1

Другие инструкции и предложения Transact-SQL для модификации таблиц

Сервер SQL Server поддерживает следующие дополнительные инструкции и предложения для модификации таблиц:

• инструкцию TRUNCATE TABLE;

• инструкцию MERGE;

• предложение OUTPUT.

Эти инструкции и предложение рассматриваются в последующих подразделах.

Инструкция TRUNCATE TABLE

Инструкция TRUNCATE TABLE является более быстрой версией инструкции DELETE без предложения WHERE. Эта инструкция удаляет все строки таблицы более быстро, чем инструкция DELETE, поскольку она удаляет содержимое постранично, тогда как инструкция DELETE делает это построчно. Инструкция TRUNCATE TABLE является расширением Transact-SQL стандарта SQL. Еще одним важным отличием этой инструкции является то, что она сбрасывает индекс столбца, для которого указано свойство автоинкремента IDENTITY.

Инструкция TRUNCATE TABLE имеет следующий синтаксис:

Инструкция MERGE

Инструкция MERGE объединяет последовательность инструкций INSERT, UPDATE и DELETE в одну элементарную инструкцию, в зависимости от существования записи (строки). Иными словами, можно синхронизировать две разные таблицы, чтобы модифицировать содержимое таблицы назначения в зависимости от различий, обнаруженных в таблице-источнике.

Основной областью применения для инструкции MERGE является среда хранилищ данных, где таблицы необходимо периодически обновлять, чтобы отражать новые данные, прибывающие с систем оперативной обработки транзакций OLTP (On-Line Transaction Processing). Эти данные могут содержать изменения существующих строк таблиц и/или новый строки, которые нужно вставить в таблицы. Если строка в новых данных соответствует записи, которая уже имеется в таблице, выполняется инструкция UPDATE или DELETE. В противном случае выполняется инструкция INSERT.

Альтернативно, вместо инструкции MERGE можно использовать последовательность инструкций INSERT, UPDATE и DELETE, в которых для каждой строки решается, какую операцию выполнять: вставку, удаление или обновление. Но этот подход имеет значительный недостаток, связанный с производительностью: в нем требуется выполнять несколько проходов по данным, а данные обрабатываются по принципу «запись за записью».

Предложение OUTPUT

По умолчанию единым видимым результатом выполнения инструкции INSERT, UPDATE или DELETE является только сообщение о количестве модифицированных строк, например «3 rows DELETED» (удалены 3 строки) и система не сохраняет информацию о модифицированных данных. Если такой видимый результат не удовлетворяет вашим требованиям, то можно использовать предложение OUTPUT, которое выводит модифицированные, вставленные или удаленные строки.

Предложение OUTPUT также применимо с инструкцией MERGE, для которой оно выводит все модифицированные строки в виде таблицы.

Результаты выполненных операций соответствующих инструкций предложение OUTPUT выводит в таблицах inserted и deleted. Кроме этого, чтобы
заполнить таблицы, в предложении OUTPUT требуется использовать выражение INTO. Поэтому для сохранения результата используется табличная переменная.

В примере ниже показано использование инструкции OUTPUT с инструкцией DELETE:

При условии, что содержимое таблицы находится в исходном состоянии, выполнение запроса в примере дает следующий результат:

В этом примере сначала объявляется табличная переменная @deleteTable с двумя столбцами: Id и LastName. В этой таблице будут сохранены удаленные строки. Синтаксис инструкции DELETE расширен предложением OUTPUT: «OUTPUT deleted.Id, deleted.LastName INTO @deleteTable». Посредством этого предложения система сохраняет удаленные строки в таблице deleted, содержимое которой потом копируется в переменную @deleteTable.

В примере ниже показано использование предложения OUTPUT в инструкции UPDATE:

Результат выполнения этого запроса:

Различия между функциями Transact-SQL и среды CLR с табличным значением Differences Between Transact-SQL and CLR Table-Valued Functions

Возвращающие табличное значение функции Transact-SQL Transact-SQL материализуют результаты вызова в промежуточной таблице. Transact-SQL Transact-SQL table-valued functions materialize the results of calling the function into an intermediate table. По этой причине они поддерживают в результатах ограничения и уникальные индексы. Since they use an intermediate table, they can support constraints and unique indexes over the results. Это исключительно полезно при возвращении результатов большого объема. These features can be extremely useful when large results are returned.

Функции CLR с табличным значением, напротив, представляют альтернативу в виде потока. In contrast, CLR table-valued functions represent a streaming alternative. Материализация всего результирующего набора в одной таблице не требуется. There is no requirement that the entire set of results be materialized in a single table. IEnumerable объект, возвращаемый управляемой функции напрямую вызывается планом выполнения запроса, который вызывает функцию с табличным, и эти результаты используются в инкрементном режиме. The IEnumerable object returned by the managed function is directly called by the execution plan of the query that calls the table-valued function, and the results are consumed in an incremental manner. Такая потоковая модель обеспечивает немедленную обработку результатов сразу после получения первой строки, вместо того чтобы ожидать заполнения всей таблицы. This streaming model ensures that results can be consumed immediately after the first row is available, instead of waiting for the entire table to be populated. Она также полезна, если возвращается очень большое число строк, поскольку таблица не материализуется в памяти как единое целое. It is also a better alternative if you have very large numbers of rows returned, because they do not have to be materialized in memory as a whole. Например, функция с табличным значением может использоваться для синтаксического анализа текстового файла и возвращения каждой строки текста в виде строки таблицы. For example, a managed table-valued function could be used to parse a text file and return each line as a row.

Queues

You can use OUTPUT in applications that use tables as queues, or to hold intermediate result sets. That is, the application is constantly adding or removing rows from the table. The following example uses the OUTPUT clause in a DELETE statement to return the deleted row to the calling application.

USE AdventureWorks2012;
GO

DELETE TOP(1) dbo.DatabaseLog WITH (READPAST)  
OUTPUT deleted.*  
WHERE DatabaseLogID = 7;  
GO

This example removes a row from a table used as a queue and returns the deleted values to the processing application in a single action. Other semantics may also be implemented, such as using a table to implement a stack. However, does not guarantee the order in which rows are processed and returned by DML statements using the OUTPUT clause. It is up to the application to include an appropriate WHERE clause that can guarantee the desired semantics, or understand that when multiple rows may qualify for the DML operation, there is no guaranteed order. The following example uses a subquery and assumes uniqueness is a characteristic of the column in order to implement the desired ordering semantics.

USE tempdb;
GO

CREATE TABLE dbo.table1  
(  
    id INT,  
    employee VARCHAR(32)  
);  
GO  
  
INSERT INTO dbo.table1 VALUES   
      (1, 'Fred')  
     ,(2, 'Tom')  
     ,(3, 'Sally')  
     ,(4, 'Alice');  
GO  
  
DECLARE @MyTableVar TABLE  
(  
    id INT,  
    employee VARCHAR(32)  
);  
  
PRINT 'table1, before delete';  
SELECT * FROM dbo.table1;  
  
DELETE FROM dbo.table1  
OUTPUT DELETED.* INTO @MyTableVar  
WHERE id = 4 OR id = 2;  
  
PRINT 'table1, after delete';  
SELECT * FROM dbo.table1;  
  
PRINT '@MyTableVar, after delete';  
SELECT * FROM @MyTableVar;  
  
DROP TABLE dbo.table1;  

When to Use Transactions

 
You should use transactions when several operations must succeed or fail as a unit. The following are some frequent scenarios where use of transactions is recommended:

• In batch processing, where multiple rows must be inserted, updated, or deleted as a single unit
• Whenever a change to one table requires that other tables be kept consistent
• When modifying data in two or more databases concurrently
• In distributed transactions, where data is manipulated in databases on various servers

When you use transactions, you put locks on data that is pending for permanent change to the database. No other operations can take place on locked data until the acquired lock is released. You could lock anything from a single row up to the entire database. This is called concurrency, which means how the database handles multiple updates at one time.
 
In the bank example above, locks will ensure that two separate transactions don’t access the same accounts at the same time. If they do then either deposits or withdrawals could be lost.
 

Note: it’s important to keep transactions pending for the shortest period of time. A lock stops others from accessing the locked database resource. Too many locks, or locks on frequently accessed resources, can seriously degrade performance.
 

SQL Server stored procedure output parameter table variable

Unfortunately, we cannot use the table-value data type as an OUTPUT parameter of a stored procedure. The OUTPUT parameters are used to return the value to the caller of the procedure. But the table-value data type can only be accomplished by using the READONLY keyword in the SQL Server procedure.

The READONLY indicates that the parameter cannot be updated or modified within the body of the procedure. So we can only use the table-value data type as an INPUT parameter in a stored procedure.

The detailed guidelines related to this topic are available in the official documentation provided by Microsoft.

Как удалить строку в sql

Для удаления применяется команда DELETE :

Например, удалим строки, у которых id равен 9:

Или удалим все товары, производителем которых является Xiaomi и которые имеют цену меньше 15000:

Более сложный пример — удалим первые два товара, у которых производитель — Apple:

После первого оператора FROM идет выборка двух строк из таблицы Products. Этой выборке назначается псевдоним Selected с помощью оператора AS. Далее устанавливаем условие, что если Id в таблице Products имеет то же значение, что и Id в выборке Selected, то строка удаляется.

Если необходимо вовсе удалить все строки вне зависимости от условия, то условие можно не указывать:

Implement the OUTPUT Clause in Delete Statements

The OUTPUT clause in a delete statement will have access to the DELETED table. This method is heavily used in recent days for implementing auditing processes and building historic tables. I will be using the same table Department_SRC in order to showcase some examples to use the OUTPUT clause and capture the output results into a table variable for a delete statement.

Viewing Deleted rows in the Results Window

Using the OUTPUT clause to display the fields with the DELETED prefix in the columns or using DELETED.* to display all the fields from the table. The OUTPUT clause should be used in between the DELETE and WHERE Statements. This will also help in printing out some of the custom output values in the Management Studio window for confirmation.

You can see this example as shown in below image.

Inserting DELETED OUTPUT INTO table variable

In order to showcase this example, I declared a table variable called @Deleted to store the OUTPUT clause results. We use the OUTPUT INTO syntax to store the results into a table variable as we did for the INSERT statement. In addition, it is possible to use two OUTPUT statements: one to view the values and another to insert the values into a table variable at the same time.

 You can see this example as shown in below image.

SQL Server stored procedure output parameter

A stored procedure in SQL Server is a piece of SQL code that we can save and reuse over and over. Stored procedures logically group one or more Transact-SQL statements and store them as a named object in the database.

Now if we call the stored procedure for the first time, then SQL Server generates an execution plan and saves it in the cache. After this, SQL Server reuses the plan in successive executions of the stored procedure to execute the stored procedure quickly and reliably.

So, if we want to use a query multiple times in SQL Server. For this, we can create and save a store procedure and execute it whenever required.

Now to create a stored procedure in SQL Server, we can use the following syntax.

And to execute the created stored procedure, we have to use the following syntax.

We can also pass parameters to a stored procedure so that it can execute based on the value(s) of the parameter(s) passed. And the stored procedure can also return one or more output parameters.

So, in this section, we will learn how we can use a stored procedure with output parameters in SQL Server. And to create an output parameter in a stored procedure, we can use the following syntax given below.

To define an output parameter in a stored procedure, we have to use the OUTPUT clause with the parameter name in the stored procedure.

Example

Now, let’s understand this with the help of an example. And for example, consider the following employee table given below.

Employee Table

Now, we will use the stored procedure output parameter to get the count of the employees whose salary is greater than a certain amount. And for this implementation, we are going to create a stored procedure as shown below.

• In the above example, we are creating a stored procedure with 2 parameters, out of which emp_count is an output parameter.
• In the procedure, we are using the SELECT statement with WHERE clause to filter out the employees whose salary is greater than 60,0000.
• In the end, we are using the @@ROWCOUNT to get the count of rows returned by the previous statement.
• And we are storing the count value in the output parameter.

Next, we will execute this stored procedure by using the following syntax.

In the query first, we are declaring a variable that will store the value returned by the output parameter. Next, we need to use the variable in the stored procedure call. In the end, we will get the following result.

Declare output parameter in sql server stored procedure

This is how to declare output parameter in sql server stored procedure.

Read SQL Server INSERT INTO SELECT

Table of contents

SSIS OLE DB Source: SQL Command vs Table or View

SSIS Expression Tasks vs Evaluating variables as expressions

SSIS OLE DB Destination vs SQL Server Destination

Execute SQL Task in SSIS: SqlStatementSource Expressions vs Variable Source Types

Execute SQL Task in SSIS: Output Parameters vs Result Sets

SSIS Derived Columns with Multiple Expressions vs Multiple Transformations

SSIS Data types: Change from the Advanced Editor vs Data Conversion Transformations

SSIS Connection Managers: OLE DB vs ODBC vs ADO.NET

SSIS Flat Files vs Raw Files

SSIS Foreach Loop vs For Loop Container

SSIS: Execute T-SQL Statement Task vs Execute SQL Task

SSIS Lookup transformation vs. Fuzzy Lookup transformation

SSIS Pivot transformations vs. Unpivot transformations

SSIS Merge Join vs. Merge Transformation

Data Access Modes in SSIS OLE DB Destination: SQL Command vs. Table or View

SSIS XML Source vs XML task

SSIS Script task vs. Script Component

SSIS term extraction vs. term lookup