Exec dbo что это

7

SQL Server — Stored Procedures

In SQL Server, a stored procedure is a set of T-SQL statements which is compiled and stored in the database. The stored procedure accepts input and output parameters, executes the SQL statements, and returns a result set if any.

By default, a stored procedure compiles when it gets executed for the first time. It also creates an execution plan that is reused for subsequent executions for faster performance.

Stored procedures are of two types:

User-defined procedures: A User-defined stored procedure is created by a database user in a user-defined database or any System database except the resource database.

System procedures: System procedures are included with SQL Server and are physically stored in the internal, hidden Resource database and logically appear in the sys schema of all the databases. The system stored procedures start with the sp_ prefix.

Create Stored Procedure

Use the CREATE statement to create a stored procedure.

A stored procedure can contain one or more select, insert, update, or delete statements. The following is an example of a simple stored procedure that returns the records from the Employee table using the SELECT query.

Execute the above T-SQL script in the query editor to compile and create it in the database, as shown below.

The above stored procedure can be executed using the EXEC keyword, as shown below.

The following stored procedure inserts values in the Employee table.

The above stored procedure can be used to insert values to the Employee table instead of the INSERT statement. Values are passed as parameters to the stored procedure. The @ symbol is used as a prefix for parameter variables.

You can execute the uspInsertEmployee stored procedure using the EXEC keyword, as shown below.

Specify each parameter separated by a command while executing a stored procedure.

View Stored Procedure

Use sp_help or sp_helptext to see the text of an existing stored procedure, as shown below.

All the stored procedures are listed under Programmability > Stored Procedures folder under the database.

Modify Stored Procedure

Use the ALTER PROCEDURE statement to modify a stored procedure.

Renaming Stored Procedure

Use the system stored procedure sp_rename to rename an existing stored procedure. The following renames uspGetEmployeeList to uspGetEmployees .

Delete Stored Procedure

Use the DROP PROCEDURE statement to delete a stored procedure.

Handling Exceptions in Stored Procedures

In SQL Server, the TRY..CATCH block is used to handle exceptions gracefully. A group of T-SQL statements can be enclosed in a TRY block. If an error is encountered in the TRY block, the control is then passed to the CATCH block which will have another set of SQL statements to handle the error.

In the CATCH block, system functions like ERROR_NUMBER() , ERROR_STATE() , ERROR_SEVERITY() can be used to get information about an error.

The following example handles the error in the uspEmpUpdate stored procedure.

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

В SQL Server процедура представляет собой хранимую программу, в которую вы можете передавать параметры. Он не возвращает значение, как функция. Тем не менее, он может вернуть статус успеха / отказа в процедуру, вызвавшую его.

Синтаксис

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Хранимые процедуры в microsoft sql server

Хранимые процедуры (Stored Procedure) представляют собой набор команд, состоящий из одного или нескольких операторов SQL или функций и сохраняемый в базе данных в откомпилированном виде.

По отношению к БД — это объекты, которые создаются и хранятся в БД. Они могут быть вызваны из клиентских приложений. При этом одна процедура может быть использована в любом количестве клиентских приложений, что позволяет существенно сэкономить трудозатраты на создание прикладного программного обеспечения и эффективно применять стратегию повторного использования кода. Так же как и любые процедуры в стандартных языках программирования, хранимые процедуры могут иметь входные и выходные параметры или не иметь их вовсе.

Каждая хранимая процедура компилируется при первом выполнении, в процессе компиляции строится оптимальный план выполнения процедуры. Описание процедуры совместно с планом ее выполнения хранится в системных таблицах БД.

С точки зрения приложений, работающих с БД, хранимые процедуры— это подпрограммы, которые выполняются на сервере.

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

По умолчанию выполнить хранимую процедуру может только ее владелец, которым является владелец БД, и создатель хранимой процедуры. Однако владелец хранимой процедуры может делегировать права на ее запуск другим пользователям.

Типы хранимых процедур

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

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

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

Пользовательские хранимые процедуры реализуют те или иные действия. Хранимые процедуры – полноценный объект базы данных. Вследствие этого каждая хранимая процедура располагается в конкретной базе данных, где и выполняется.

Временные хранимые процедуры существуют лишь некоторое время, после чего автоматически уничтожаются сервером. Они делятся на локальные и глобальные.

Локальные временные хранимые процедуры могут быть вызваны только из того соединения, в котором созданы. При создании такой процедуры ей необходимо дать имя, начинающееся с одного символа #.

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

Глобальные временные хранимые процедуры доступны для любых соединений сервера, на котором имеется такая же процедура. Для ее определения достаточно дать ей имя, начинающееся с символов ##.

Удаляются эти процедуры при перезапуске или остановке сервера, а также при закрытии соединения, в контексте которого они были созданы.

Создание хранимой процедуры предполагает решение следующих задач:

планирование прав доступа. При создании хранимой процедуры следует учитывать, что она будет иметь те же права доступа к объектам базы данных, что и создавший ее пользователь;

определение параметров хранимой процедуры, хранимые процедуры могут обладать входными и выходными параметрами;

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

Синтаксис оператора создания новой или изменения имеющейся хранимой процедуры в обозначениях MS SQL Server:

PROC[EDURE] имя_процедуры

[=default][OUTPUT] ][. n]

sql_оператор [. n]

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

Используя префиксы sp_, #, ## в имени, создаваемую процедуру можно определить в качестве системной или временной.

Как видно из синтаксиса команды, не допускается указывать имя владельца, которому будет принадлежать создаваемая процедура, а также имя базы данных, где она должна быть размещена. Таким образом, чтобы разместить создаваемую хранимую процедуру в конкретной базе данных, необходимо выполнить команду CREATE PROCEDURE в контексте этой базы данных.

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

Номер в имени – это идентификационный номер хранимой процедуры, однозначно определяющий ее в группе процедур. Для удобства управления процедурами логически однотипные хранимые процедуры можно группировать, присваивая им одинаковые имена, но разные идентификационные номера.

Для передачи входных и выходных данных в создаваемой хранимой процедуре могут использоваться параметры, имена которых, как и имена локальных переменных, должны начинаться с символа @. В одной хранимой процедуре можно задать множество параметров, разделенных запятыми.

В теле процедуры не должны применяться локальные переменные, чьи имена совпадают с именами параметров этой процедуры.

Для определения типа данных, который будет иметь соответствующий параметр хранимой процедуры, используются все типы данных SQL, включая определенные пользователем, за исключением table. Однако тип данных CURSOR может быть использован только как выходной параметр хранимой процедуры, т.е. с указанием ключевого слова OUTPUT.

При указании типа данных cursor нужно также указать ключевые слова VARYING и OUTPUT. Выходных параметров типа cursor может быть несколько.

Наличие ключевого слова OUTPUT означает, что соответствующий параметр предназначен для возвращения данных из хранимой процедуры. Однако это вовсе не означает, что параметр не подходит для передачи значений в хранимую процедуру.

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

Не разрешается использование любых выражений или констант, допустимое для обычных параметров. Аргументы типов text, ntext и image не могут быть выходными, если процедура не является процедурой CLR.

При указании ключевого слова OUTPUT значение соответствующего параметра при вызове процедуры может быть задано только с помощью локальной переменной.

Ключевое слово VARYING применяется совместно с параметром OUTPUT, имеющим тип CURSOR. Оно определяет, что выходным параметром будет результирующее множество.

Ключевое слово DEFAULT представляет собой значение, которое будет принимать соответствующий параметр по умолчанию. Таким образом, при вызове процедуры можно не указывать явно значение соответствующего параметра.

Так как сервер кэширует план исполнения запроса и компилированный код, при последующем вызове процедуры будут использоваться уже готовые значения. Однако в некоторых случаях все же требуется выполнять перекомпиляцию кода процедуры. Указание ключевого слова RECOMPILE предписывает системе создавать план выполнения хранимой процедуры при каждом ее вызове.

Параметр FOR REPLICATION востребован при репликации данных.

Ключевое слово ENCRYPTION предписывает серверу выполнить шифрование кода хранимой процедуры, что может обеспечить защиту от использования авторских алгоритмов, реализующих работу хранимой процедуры.

Ключевое слово AS размещается в начале собственно тела хранимой процедуры, т.е. набора команд SQL, с помощью которых и будет реализовываться то или иное действие. В теле процедуры могут применяться практически все команды SQL, объявляться транзакции, устанавливаться блокировки и вызываться другие хранимые процедуры.

Инструкцию CREATE PROCEDURE нельзя объединять с другими инструкциями Transact-SQL в одном пакете.

Использование RETURN в хранимой процедуре. Выход из хранимой процедуры можно осуществить посредством команды RETURN.

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

Синтаксис:

RETURN [ integer_expression ]

Аргументы:

integer_expression — возвращаемое целочисленное значение. Хранимые процедуры могут возвращать целочисленное значение вызывающей их процедуре или приложению. Тип int. Пример приведен ниже.

Удаление хранимой процедуры осуществляется командой:

Каждая хранимая процедура является объектом БД. Она имеет уникальное имя и уникальный внутренний номер в системном каталоге. При изменении текста хранимой процедуры мы должны сначала уничтожить данную процедуру как объект, хранимый в БД, и только после этого записать на ее место новую. Следует отметить, что при удалении хранимой процедуры удаляются одновременно все ее версии, нельзя удалить только одну версию хранимой процедуры.

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

[ @return_status = ]

имя_процедуры [;номер]

Если вызов хранимой процедуры не является единственной командой в пакете, то присутствие команды EXECUTE обязательно. Более того, эта команда требуется для вызова процедуры из тела другой процедуры или триггера.

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

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

Из синтаксиса команды EXECUTE видно, что имена параметров могут быть опущены при вызове процедуры. Однако в этом случае пользователь должен указывать значения для параметров в том же порядке, в каком они перечислялись при создании процедуры.

Параметры, для которых задано значение по умолчанию, в списке вызова можно не указывать.

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

Пример:

CREATE TABLE dbo.Table11

(c1 int PRIMARY KEY NOT NULL, c2 int NOT NULL);

INSERT INTO dbo.Table11 VALUES (1, 10);

INSERT INTO dbo.Table11 VALUES (2, 20);

INSERT INTO dbo.Table11 VALUES (3, 30);

Процедура:

CREATE PROC my_proc11 @par1 int output, @par2 int output

SELECT @par1= c1, @par2 = c2 FROM dbo.Table11;

Обращение:

Declare @p1 int, @p2 int

EXEC my_proc11 @p1 output, @p2 output

При указании ключевого слова OUTPUT значение соответствующего параметра при вызове процедуры может быть задано только с помощью локальной переменной.

Выводится только одна строка (последняя).

Пример задания значения входного параметра по умолчанию:

CREATE PROC my_proc55 @par1 int output, @par2 int output, @par3 As int =10

SELECT @par1= c1, @par2 = c2 FROM dbo.Table5 where c2=@par3;

Обращение к процедуре:

Declare @p1 int, @p2 int

EXEC my_proc55 @p1 output, @p2 output —.

Declare @p1 int, @p2 int

EXEC my_proc55 @p1 output, @p2 output, DEFAULT

Примеры создания и использования хранимых процедур для базы данных DB_Books:

Процедура без параметров:

CREATE PROCEDURE Count_Books AS

Select count(Code_book) from Books

Обращение к процедуре:

Процедура c входным параметром @Count_pages:

CREATE PROCEDURE Count_Books_Pages @Count_pages as Int

AS Select count(Code_book) from Books

Обращение к процедуре:

EXEC Count_Books_Pages 100

Процедура c входными параметрами @Count_pages и @Title:

CREATE PROCEDURE Count_Books_Title @Count_pages as Int, @Title AS Char(10) AS

Select count(Code_book) from Books

WHERE Pages>=@Count_pages AND Title_book LIKE @Title

Обращение к процедуре:

EXEC Count_Books_Title 100, ‘П%’

Процедура c входными параметрами @Count_pages и @Title и выходным параметром @Itogo:

CREATE PROCEDURE Count_Books_Itogo @Count_pages Int, @Title Char(10), @Itogo Int OUTPUT

Select @Itogo = count(Code_book) from Books

WHERE Pages>=@Count_pages AND Title_book LIKE @Title

Обращение к процедуре:

Declare @q As int

EXEC Count_Books_Itogo 100, ‘П%’, @q output

Процедура c входным параметром @param и RETURN:

CREATE PROCEDURE checkname @param int AS

IF (SELECT Name_author FROM authors WHERE Code_author = @param) = ‘Пушкин А.С.’

Обращение к процедуре:

DECLARE @return_status As int

EXEC @return_status = checkname 3

SELECT ‘Return Status’ = @return_status

Процедура c входным параметром @k для получения всей информации о конкретном авторе:

CREATE PROC select_author @k CHAR(30)

AS SELECT * FROM Authors WHERE name_author=@k

Обращение к процедуре:

EXEC select_author ‘Пушкин А.С.’

Declare @p1 As CHAR(30)

set @p1=’Пушкин А.С.’

EXEC select_author @p1

Использование типа данных cursor в параметре OUTPUT

Хранимые процедуры языка Transact-SQL могут использовать тип данных cursor только для параметров OUTPUT. Если тип данных cursor указан для параметра, должны быть также указаны оба параметра VARYING и OUTPUT. Если для параметра указано ключевое слово VARYING, тип данных должен быть cursor и должно быть указано ключевое слово OUTPUT.

Примечание: Тип данных cursor не может быть связан с переменными приложения через интерфейсы API баз данных, таких как OLE DB, ODBC, ADO и DB-Library. Поскольку параметры OUTPUT должны быть связаны прежде, чем приложение может выполнить хранимую процедуру, хранимые процедуры с параметрами OUTPUT типа cursor не могут быть вызваны из функций API базы данных. Эти процедуры могут быть вызваны из пакетов языка Transact-SQL, хранимых процедур или триггеров, только когда переменная OUTPUT типа cursor присвоена локальной переменной языка Transact-SQL типа cursor.

Следующие правила относятся к выходным параметрам типа cursor при выполнении процедуры:

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

Непрокручиваемый курсор открыт в процедуре на результирующем наборе по имени RS из 100 строк.

Процедура выбирает первые 5 строк результирующего набора RS.

Процедура возвращает результат участнику.

Результирующий набор RS, возвращенный участнику, состоит из строк с 6 по 100 из набора RS, и курсор в участнике позиционирован перед первой строкой RS.

Для однонаправленного курсора, если курсор позиционирован перед первой строкой после завершения хранимой процедуры, весь результирующий набор будет возвращен к вызывающему пакету, хранимой процедуре или триггеру. После возврата позиция курсора будет установлена перед первой строкой.

Для однонаправленного курсора, если курсор позиционирован за концом последней строки после завершения хранимой процедуры, вызывающему пакету, хранимой процедуре или триггеру будет возвращен пустой результирующий набор.

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

Для любого типа курсора, если курсор закрыт, то вызывающему пакету, хранимой процедуре или триггеру будет возвращено значение NULL. Это же произойдет в случае, если курсор присвоен параметру, но этот курсор никогда не открывался.

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

В следующем примере создается хранимая процедура, которая указывает выходной параметр @currency_cursor, используя тип данных cursor. Хранимая процедура затем будет вызвана из пакета.

Создание и заполнение таблицы для примера:

IF OBJECT_ID (‘Table1′, N’U’) IS NOT NULL

DROP TABLE Table1;

CREATE TABLE Table1

(c1 int PRIMARY KEY NOT NULL, c2 int NOT NULL);

INSERT INTO Table1 VALUES (1, 10);

INSERT INTO Table1 VALUES (2, 20);

INSERT INTO Table1 VALUES (3, 30);

select * from Table1

Создание процедуры:

IF OBJECT_ID ( ‘PrimerCursor’, ‘P’ ) IS NOT NULL

DROP PROCEDURE PrimerCursor;

CREATE PROCEDURE PrimerCursor

@CurrencyCursor CURSOR VARYING OUTPUT

SET @CurrencyCursor = CURSOR

STATIC FORWARD_ONLY FOR

select c1, c2 from Table1

Обращение к процедуре:

DECLARE @MyCursor CURSOR;

EXEC PrimerCursor @CurrencyCursor = @MyCursor OUTPUT;

—обратите внимание на синтаксис, @CurrencyCursor = @MyCursor

WHILE (@@FETCH_STATUS = 0)

FETCH NEXT FROM @MyCursor

См. выше. Для однонаправленного курсора, если курсор позиционирован перед первой строкой после завершения хранимой процедуры, весь результирующий набор будет возвращен к вызывающему пакету, хранимой процедуре или триггеру. После возврата позиция курсора будет установлена перед первой строкой.

Измененный пример:

IF OBJECT_ID ( ‘PrimerCursor’, ‘P’ ) IS NOT NULL

DROP PROCEDURE PrimerCursor;

CREATE PROCEDURE PrimerCursor

@CurrencyCursor CURSOR VARYING OUTPUT

SET @CurrencyCursor = CURSOR

STATIC SCROLL FOR

select c1, c2 from Table1

FETCH ABSOLUTE 2 FROM @CurrencyCursor

FETCH prior FROM @CurrencyCursor

—Обращение к процедуре:

DECLARE @MyCursor CURSOR;

EXEC PrimerCursor @CurrencyCursor = @MyCursor OUTPUT;

FETCH NEXT FROM @MyCursor

См. выше. Для прокручиваемого курсора, все строки в результирующем наборе будут возвращены к вызывающему пакету, хранимой процедуре или триггеру после выполнения хранимой процедуры. При возврате позиция курсора остается в позиции последней выборки, выполненной в процедуре.

—Создание процедуры с выходным параметром курсором

IF OBJECT_ID ( ‘dbo.uspCurrencyCursor’, ‘P’ ) IS NOT NULL

DROP PROCEDURE dbo.uspCurrencyCursor;

CREATE PROCEDURE dbo.uspCurrencyCursor

@CurrencyCursor CURSOR VARYING OUTPUT

SET @CurrencyCursor = CURSOR

FORWARD_ONLY DYNAMIC FOR

SELECT CurrencyCode, Name

FROM Sales.Currency where Name Like ‘S%’;

DECLARE @MyCursor CURSOR;

EXEC dbo.uspCurrencyCursor @CurrencyCursor = @MyCursor OUTPUT;

WHILE (@@FETCH_STATUS = 0)

FETCH NEXT FROM @MyCursor;

Функции, определенные пользователем

Функции всегда имеют один возвращаемый параметр.

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

Скалярная функция возвращает атомарное (скалярное) значение.

Функции являются табличными, если предложение returns возвращает набор строк.

Функции создаются при помощи оператора create function, который имеет cледующий синтаксис:

create function [schema_name.]function_name

[WITH (ENCRYPTION | SCHEMABINDING)]

| RETURN (select_statement) >

schema_name — имя схемы, которой назначено владение созданной функцией;

function_name — имя новой функции;

@ parameter_name — имя входного параметра;

parameter_data_type — задает тип данных параметра;

default — задает необязательное значение по умолчанию для соответствующего параметра. Значением по умолчанию может быть также пустое значение null.

RETURNS — задает тип данных значения, возвращаемого функцией. Это может быть любой стандартный тип данных, поддерживаемый системой базы данных, включая тип данных table (SQL 2008). (Только один стандартный тип данных, который нельзя использовать, является тип данных timestamp);

TABLE —указывает, что возвращаемым значением функции является таблица. Функциям, возвращающим табличное значение, могут передаваться только константы и @local_variables.

block — является блоком begin/end, который содержит реализацию функции. Последним оператором блока должен быть оператор return с аргументом. Значение аргумента — это значение, возвращаемое данной функцией.

В теле блока begin/end допустимы только следующие операторы:

операторы присваивания, такие как set;

операторы управления потоком выполнения, такие как while и if;

операторы declare, определяющие локальные переменные данных;

операторы select, содержащие списки выбора с выражениями, которые присваиваются в качестве значений переменным, являющимся локальными в этой функции;

операторы insert, update и delete, изменяющие переменные типа table, которые являются локальными в этой функции.

По умолчанию только участники фиксированной серверной роли sysadmin, а также участники фиксированных ролей базы данных db_owner и db_ddladmin могут использовать оператор create function.

Скалярная функция возвращает атомарное (скалярное) значение. Это означает, что в предложении returns скалярной функции можно задать один из стандартных типов данных.

RETURN scalar_expression —возвращаемое атомарное (скалярное) значение.

[ WITH <function_option> [ ,. n ] ]

function_body

RETURN scalar_expression

Функции являются табличными, если предложение returns возвращает набор строк.

RETURN (select_statement) – определяет выходные табличные данные функции. Инструкция RETURN не может иметь аргумента.

with encrYption — кодирует информацию в системном каталоге, который содержит текст оператора create function.

with schemabinding связывает функцию с объектами базы данных.

Пример: функция вычисляет среднее 3 чисел.

CREATE FUNCTION SRED

(@X1 Int, @X2 Int, @X3 Int)

declare @RES As real

set @RES =(@X1+@X2+@X3)/3

Вызов:

select dbo.SRED (3, 4, 5)

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

CREATE FUNCTION compute_costs (@percent INT =10) — значение по умолчанию

DECLARE @additional_costs DEC (14,2), @sum_budget dec(16,2)

SELECT @sum_budget = SUM (budget) FROM project

SET @additional_costs = @sum_budget * @percent/100

Входная переменная @percent задает процент увеличения бюджетов. Блок begin/end объявляет две локальные переменные: @additionai_cost и @sum_budget. Затем функция присваивает переменной @sum_budget сумму всех бюджетов, используя специальную форму оператора select. После этого функция вычисляет общие дополнительные затраты и возвращает это значение с помощью оператора return.

Пример: функция вычисляет возраст сотрудника на сегодняшний день.

IF OBJECT_ID(‘dbo.fn_age’) IS NOT NULL DROP FUNCTION dbo.fn_age;

CREATE FUNCTION dbo.fn_age

@birthdate AS DATETIME,

@eventdate AS DATETIME

DATEDIFF(year, @birthdate, @eventdate)

— CASE WHEN MONTH(@eventdate) + DAY(@eventdate)

Функции может быть вызвана в операторах Transact-SQL, таких как select, insert, update или delete.

Для вызова функции задается ее имя, за которым следуют круглые скобки. В этих скобках можно задать один или более аргументов. Аргументы являются значениями или выражениями, передаваемыми входным параметрам, которые заданы сразу после имени функции.

При вызове функции, когда все входные параметры не имеют значения по умолчанию, нужно задать значения для каждого параметра в том же порядке, в котором эти параметры определены в операторе create function.

Пример: использование функции compute_cost

SELECT project_no, project_name

WHERE budget < dbo.compute_costs (25)

Выдает имена и номера всех проектов, где бюджет меньше, чем общая дополнительная стоимость всех проектов при заданном проценте.

Пример: использование функции compute_cost

SELECT dbo.compute_costs (25)

Пример: использование функции dbo.fn_age

empid, firstname, lastname, birthdate,

dbo.fn_age(birthdate, GETDATE ()) AS age

Функции являются табличными, если предложение returns возвращает набор строк.

Ни одна из инструкций Transact-SQL в возвращающей табличное значение функции не может возвращать результирующий набор непосредственно пользователю. Единственные данные, которые функция может вернуть пользователю, это таблица table, возвращаемая этой функцией.

В зависимости от того, как определено тело функции, табличные функции могут быть классифицированы как линейные или как многооператорные функции.

Если предложение returns задает table без указания списка столбцов, то эта функция является линейной. Линейные функции возвращают результат выполнения оператора select в виде переменной типа данных table

Синтаксис:

[ WITH <function_option> [ ,. n ] ]

RETURN [ ( ] select_stmt [ ) ]

Пример:

CREATE FUNCTION employees_in_project (@pr_number CHAR(4))

RETURNS TABLE

AS RETURN (SELECT emp_fname, emp_lname

FROM works_on, employee

WHERE employee.emp_no = works_on.emp_no

AND project_no = @pr_number)

Функция employees_in_project отображает имена всех служащих, которые связаны с конкретным проектом. Входной параметр @pr_number задает номер проекта. Поскольку функция в общем случае вращает набор строк, предложение returns содержит тип данных TABLE. Блока begin/end в примере нет, потому что предложение return содержит оператор select.

Обращение к функции имеет вид:

SELECT * FROM employees_in_project(‘p3’)

Пример использования функции, возвращающей таблицу:

CREATE FUNCTION fn_getcustomerorders1(@CustomerID int, @TopRecords bigint)

SELECT TOP (@TopRecords) *

WHERE CustomerID = @CustomerID

ORDER BY OrderDate DESC

Обращение к функции:

SELECT * FROM fn_getcustomerorders1(5,3 );

Результат:

Многооператорная табличная функция включает имя, определяющее table. Имя задает внутреннюю переменную типа table. Можно использовать эту переменную для добавления в нее строк, а затем вернуть эту переменную в качестве возвращаемого значения функции.

Синтаксис:

RETURNS @return_variable TABLE < table_type_definition >

[ WITH <function_option> [ ,. n ] ]

function_body

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

Пример: В следующем примере создается функция dbo.ufnGetContactInformation и демонстрируются компоненты возвращающей табличное значение функции. В этой функции именем локальной возвращаемой переменной является @retContactInformation. Инструкции в теле функции вставляют строки в эту переменную для создания табличных результатов, возвращаемых этой функцией.

IF OBJECT_ID(N’dbo.ufnGetContactInformation’, N’TF’) IS NOT NULL

DROP FUNCTION dbo.ufnGetContactInformation;

CREATE FUNCTION dbo.ufnGetContactInformation(@ContactID int)

—@ContactID входной параметр – индекс контакта

RETURNS @retContactInformation TABLE

— Поля, возвращаемые функцией

ContactID int PRIMARY KEY NOT NULL,

FirstName nvarchar(50) NULL,

LastName nvarchar(50) NULL,

JobTitle nvarchar(50) NULL,

ContactType nvarchar(50) NULL

— Присвоение общей информации

WHERE ContactID = @ContactID;

WHEN EXISTS(SELECT * FROM HumanResources.Employee AS e

WHERE e.ContactID = @ContactID)

THEN (SELECT Title

WHERE ContactID = @ContactID)

WHEN EXISTS(SELECT * FROM Purchasing.VendorContact AS vc

INNER JOIN Person.ContactType AS ct

ON vc.ContactTypeID = ct.ContactTypeID

WHERE vc.ContactID = @ContactID)

THEN (SELECT ct.Name

FROM Purchasing.VendorContact AS vc

INNER JOIN Person.ContactType AS ct

ON vc.ContactTypeID = ct.ContactTypeID

WHERE vc.ContactID = @ContactID)

WHEN EXISTS(SELECT * FROM Sales.StoreContact AS sc

INNER JOIN Person.ContactType AS ct

ON sc.ContactTypeID = ct.ContactTypeID

WHERE sc.ContactID = @ContactID)

THEN (SELECT ct.Name

FROM Sales.StoreContact AS sc

INNER JOIN Person.ContactType AS ct

ON sc.ContactTypeID = ct.ContactTypeID

WHERE ContactID = @ContactID)

WHEN EXISTS(SELECT * FROM HumanResources.Employee AS e

WHERE e.ContactID = @ContactID)

WHEN EXISTS(SELECT * FROM Purchasing.VendorContact AS vc

INNER JOIN Person.ContactType AS ct

ON vc.ContactTypeID = ct.ContactTypeID

WHERE vc.ContactID = @ContactID)

THEN ‘Vendor Contact’

WHEN EXISTS(SELECT * FROM Sales.StoreContact AS sc

INNER JOIN Person.ContactType AS ct

ON sc.ContactTypeID = ct.ContactTypeID

WHERE sc.ContactID = @ContactID)

THEN ‘Store Contact’

WHEN EXISTS(SELECT * FROM Sales.Individual AS i

WHERE i.ContactID = @ContactID)

— формирование вызываемой информации

IF @ContactID IS NOT NULL

INSERT INTO @retContactInformation

SELECT @ContactID, @FirstName, @LastName, @JobTitle, @ContactType;

Обращение к функции:

SELECT ContactID, FirstName, LastName, JobTitle, ContactType

FROM dbo.ufnGetContactInformation(2200);

Пользовательские функции, возвращающие тип данных table, могут быть полноценной альтернативой представлениям. Ссылка на эти функции происходит как на возвращающие табличное значение функции. Возвращающая табличное значение пользовательская функция может быть использована там, где в запросах Transact-SQL разрешены табличные выражения или выражения представлений. В то время как представления ограничены одной инструкцией SELECT, пользовательские функции могут содержать дополнительные инструкции, обеспечивающие более эффективную логику, чем та, которая возможна в представлениях.

Возвращающая табличное значение пользовательская функция также может заменять хранимые процедуры, возвращающие один результирующий набор. На таблицу, возвращаемую пользовательской функцией, можно ссылаться в предложении FROM инструкции Transact-SQL, в котором нельзя ссылаться на хранимые процедуры, возвращающие результирующие наборы.

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

Вложенностью называют ситуацию, когда хранимая процедура вызывает другую процедуру или выполняет управляемый код, ссылаясь на подпрограмму, тип или статистическую функцию среды Common Language Runtime (CLR) Integration. Любая ссылка на управляемый код внутри хранимой процедуры Transact-SQL считается одним уровнем вложенности.

Вложенность хранимых процедур и ссылок на управляемый код ограничена 32 уровнями. Уровень вложенности увеличивается на единицу, когда вызванная хранимая процедура или управляемый код начинает выполняться, и уменьшается на единицу, когда заканчивает. Если уровень вложенности превышает максимальное значение, вся цепочка вызовов заканчивается ошибкой. Текущий уровень вложенности хранимых процедур можно получить при помощи функции @@NESTLEVEL.

Хранимые процедуры могут вызывать сами себя, этот способ называется рекурсией.

Пример. Создать процедуру для определения общего количества товаров, приобретенных фирмой, в которой работает заданный сотрудник.

Сначала разрабатывается процедура для определения фирмы, где работает сотрудник (вложенная процедура).

CREATE PROC my_proc7 @n VARCHAR(20), @f VARCHAR(20) OUTPUT

Затем создается процедура (вызывающая процедура), подсчитывающая общее количество товара, который закуплен интересующей нас фирмой.

Name already in use

sql-docs / docs / t-sql / language-elements / execute-transact-sql.md

• Go to file T
• Go to line L
• Copy path
• Copy permalink

• Open with Desktop
• View raw
• Copy raw contents Copy raw contents

Copy raw contents

Copy raw contents

Executes a command string or character string within a [!INCLUDEtsql] batch, or one of the following modules: system stored procedure, user-defined stored procedure, CLR stored procedure, scalar-valued user-defined function, or extended stored procedure. The EXECUTE statement can be used to send pass-through commands to linked servers. Additionally, the context in which a string or command is executed can be explicitly set. Metadata for the result set can be defined by using the WITH RESULT SETS options.

[!IMPORTANT]
Before you call EXECUTE with a character string, validate the character string. Never execute a command constructed from user input that has not been validated.

. image type=»icon» source=»../../includes/media/topic-link-icon.svg» border=»false». Transact-SQL syntax conventions

. moniker range=»>=sql-server-ver15″ The following code block shows the syntax in SQL Server 2019. Alternatively, see syntax in SQL Server 2017 and earlier instead.

The following code block shows the syntax in SQL Server 2017 and earlier. Alternatively, see syntax in SQL Server 2019 instead.

@return_status
Is an optional integer variable that stores the return status of a module. This variable must be declared in the batch, stored procedure, or function before it is used in an EXECUTE statement.

When used to invoke a scalar-valued user-defined function, the @return_status variable can be of any scalar data type.

module_name
Is the fully qualified or nonfully qualified name of the stored procedure or scalar-valued user-defined function to call. Module names must comply with the rules for identifiers. The names of extended stored procedures are always case-sensitive, regardless of the collation of the server.

A module that has been created in another database can be executed if the user running the module owns the module or has the appropriate permission to execute it in that database. A module can be executed on another server running [!INCLUDEssNoVersion] if the user running the module has the appropriate permission to use that server (remote access) and to execute the module in that database. If a server name is specified but no database name is specified, the [!INCLUDEssDEnoversion] looks for the module in the default database of the user.

;number
Applies to: [!INCLUDEsql2008-md] and later

Is an optional integer that is used to group procedures of the same name. This parameter is not used for extended stored procedures.

For more information about procedure groups, see CREATE PROCEDURE (Transact-SQL).

@module_name_var
Is the name of a locally defined variable that represents a module name.

This can be a variable that holds the name of a natively compiled, scalar user-defined function.

@parameter
Is the parameter for module_name, as defined in the module. Parameter names must be preceded by the at sign (@). When used with the @parameter_name=value form, parameter names and constants do not have to be supplied in the order in which they are defined in the module. However, if the @parameter_name=value form is used for any parameter, it must be used for all subsequent parameters.

By default, parameters are nullable.

value
Is the value of the parameter to pass to the module or pass-through command. If parameter names are not specified, parameter values must be supplied in the order defined in the module.

When executing pass-through commands against linked servers, the order of the parameter values depends on the OLE DB provider of the linked server. Most OLE DB providers bind values to parameters from left to right.

If the value of a parameter is an object name, character string, or qualified by a database name or schema name, the whole name must be enclosed in single quotation marks. If the value of a parameter is a keyword, the keyword must be enclosed in double quotation marks.

If you pass a single word that does not begin with @ and that’s not enclosed in quotation marks — for example, if you forget @ on a parameter name — the word is treated as an nvarchar string, in spite of the missing quotation marks.

If a default is defined in the module, a user can execute the module without specifying a parameter.

The default can also be NULL. Generally, the module definition specifies the action that should be taken if a parameter value is NULL.

@variable
Is the variable that stores a parameter or a return parameter.

OUTPUT
Specifies that the module or command string returns a parameter. The matching parameter in the module or command string must also have been created by using the keyword OUTPUT. Use this keyword when you use cursor variables as parameters.

If value is defined as OUTPUT of a module executed against a linked server, any changes to the corresponding @parameter performed by the OLE DB provider will be copied back to the variable at the end of the execution of module.

If OUTPUT parameters are being used and the intent is to use the return values in other statements within the calling batch or module, the value of the parameter must be passed as a variable, such as @parameter = @variable. You cannot execute a module by specifying OUTPUT for a parameter that is not defined as an OUTPUT parameter in the module. Constants cannot be passed to module by using OUTPUT; the return parameter requires a variable name. The data type of the variable must be declared and a value assigned before executing the procedure.

When EXECUTE is used against a remote stored procedure, or to execute a pass-through command against a linked server, OUTPUT parameters cannot be any one of the large object (LOB) data types.

Return parameters can be of any data type except the LOB data types.

DEFAULT
Supplies the default value of the parameter as defined in the module. When the module expects a value for a parameter that does not have a defined default and either a parameter is missing or the DEFAULT keyword is specified, an error occurs.

@string_variable
Is the name of a local variable. @string_variable can be any char, varchar, nchar, or nvarchar data type. These include the (max) data types.

[N] ‘tsql_string‘
Is a constant string. tsql_string can be any nvarchar or varchar data type. If the N is included, the string is interpreted as nvarchar data type.

AS <context_specification>
Specifies the context in which the statement is executed.

LOGIN
Applies to: [!INCLUDEsql2008-md] and later

Specifies the context to be impersonated is a login. The scope of impersonation is the server.

USER
Specifies the context to be impersonated is a user in the current database. The scope of impersonation is restricted to the current database. A context switch to a database user does not inherit the server-level permissions of that user.

[!IMPORTANT]
While the context switch to the database user is active, any attempt to access resources outside the database will cause the statement to fail. This includes USE database statements, distributed queries, and queries that reference another database by using three- or four-part identifiers.

‘name‘
Is a valid user or login name. name must be a member of the sysadmin fixed server role or exist as a principal in sys.database_principals or sys.server_principals, respectively.

name cannot be a built-in account, such as NT AUTHORITY\LocalService, NT AUTHORITY\NetworkService, or NT AUTHORITY\LocalSystem.

For more information, see Specifying a User or Login Name later in this topic.

[N] ‘command_string‘
Is a constant string that contains the command to be passed through to the linked server. If the N is included, the string is interpreted as nvarchar data type.

[?]
Indicates parameters for which values are supplied in the <arg-list> of pass-through commands that are used in an EXEC(‘. ‘, <arg-list>) AT <linkedsrv> statement.

AT linked_server_name
Applies to: [!INCLUDEsql2008-md] and later

Specifies that command_string is executed against linked_server_name and results, if any, are returned to the client. linked_server_name must refer to an existing linked server definition in the local server. Linked servers are defined by using sp_addlinkedserver.

WITH <execute_option>
Possible execute options. The RESULT SETS options cannot be specified in an INSERT. EXEC statement.

AT DATA_SOURCE data_source_name Applies to: [!INCLUDEsssql19] and later

Specifies that command_string is executed against data_source_name and results, if any, are returned to the client. data_source_name must refer to an existing EXTERNAL DATA SOURCE definition in the database. Only data sources that point to SQL Server are supported. Additionally, for SQL Server big data cluster data sources that point to compute pool, data pool or storage pool are supported. Data sources are defined by using CREATE EXTERNAL DATA SOURCE.

WITH <execute_option>
Possible execute options. The RESULT SETS options cannot be specified in an INSERT. EXEC statement.

Use this option if the parameter you are supplying is atypical or if the data has significantly changed. This option is not used for extended stored procedures. We recommend that you use this option sparingly because it is expensive.

Note: You can not use WITH RECOMPILE when calling a stored procedure that uses OPENDATASOURCE syntax. The WITH RECOMPILE option is ignored when a four-part object name is specified.

This option provides no guarantee of what results, if any, will be returned, and no definition is provided. The statement executes without error if any results are returned or no results are returned. RESULT SETS UNDEFINED is the default behavior if a result_sets_option is not provided.

Guarantees that the execute statement will not return any results. If any results are returned the batch is aborted.

Provides a guarantee that the result will come back as specified in the result_sets_definition. For statements that return multiple result sets, provide multiple result_sets_definition sections. Enclose each result_sets_definition in parentheses, separated by commas. For more information, see <result_sets_definition> later in this topic.

<result_sets_definition> Applies to: [!INCLUDEssSQL11] and later, [!INCLUDEssSDSfull]

Describes the result sets returned by the executed statements. The clauses of the result_sets_definition have the following meaning

The actual result set being returned during execution can differ from the result defined using the WITH RESULT SETS clause in one of the following ways: number of result sets, number of columns, column name, nullability, and data type. If the number of result sets differs, an error occurs and the batch is aborted.

Parameters can be supplied either by using value or by using @parameter_name=value. A parameter is not part of a transaction; therefore, if a parameter is changed in a transaction that is later rolled back, the value of the parameter does not revert to its previous value. The value returned to the caller is always the value at the time the module returns.

Nesting occurs when one module calls another or executes managed code by referencing a common language runtime (CLR) module, user-defined type, or aggregate. The nesting level is incremented when the called module or managed code reference starts execution, and it is decremented when the called module or managed code reference has finished. Exceeding the maximum of 32 nesting levels causes the complete calling chain to fail. The current nesting level is stored in the @@NESTLEVEL system function.

Because remote stored procedures and extended stored procedures are not within the scope of a transaction (unless issued within a BEGIN DISTRIBUTED TRANSACTION statement or when used with various configuration options), commands executed through calls to them cannot be rolled back. For more information, see System Stored Procedures (Transact-SQL) and BEGIN DISTRIBUTED TRANSACTION (Transact-SQL).

When you use cursor variables, if you execute a procedure that passes in a cursor variable with a cursor allocated to it an error occurs.

You do not have to specify the EXECUTE keyword when executing modules if the statement is the first one in a batch.

For additional information specific to CLR stored procedures, see CLR Stored Procedures.

Using EXECUTE with Stored Procedures

You do not have to specify the EXECUTE keyword when you execute stored procedures when the statement is the first one in a batch.

[!INCLUDEssNoVersion] system stored procedures start with the characters sp_. They are physically stored in the Resource database, but logically appear in the sys schema of every system and user-defined database. When you execute a system stored procedure, either in a batch or inside a module such as a user-defined stored procedure or function, we recommend that you qualify the stored procedure name with the sys schema name.

[!INCLUDEssNoVersion] system extended stored procedures start with the characters xp_, and these are contained in the dbo schema of the master database. When you execute a system extended stored procedure, either in a batch or inside a module such as a user-defined stored procedure or function, we recommend that you qualify the stored procedure name with master.dbo.

When you execute a user-defined stored procedure, either in a batch or inside a module such as a user-defined stored procedure or function, we recommend that you qualify the stored procedure name with a schema name. We do not recommend that you name a user-defined stored procedure with the same name as a system stored procedure. For more information about executing stored procedures, see Execute a Stored Procedure.

Using EXECUTE with a Character String

In earlier versions of [!INCLUDEssNoVersion], character strings are limited to 8,000 bytes. This requires concatenating large strings for dynamic execution. In [!INCLUDEssNoVersion], the varchar(max) and nvarchar(max) data types can be specified that allow for character strings to be up to 2 gigabytes of data.

Changes in database context last only until the end of the EXECUTE statement. For example, after the EXEC in this following statement is run, the database context is master.

You can use the AS < LOGIN | USER >= ‘ name ‘ clause to switch the execution context of a dynamic statement. When the context switch is specified as EXECUTE (‘string’) AS <context_specification> , the duration of the context switch is limited to the scope of the query being executed.

Specifying a User or Login Name

The user or login name specified in AS < LOGIN | USER >= ‘ name ‘ must exist as a principal in sys.database_principals or sys.server_principals, respectively, or the statement will fail. Additionally, IMPERSONATE permissions must be granted on the principal. Unless the caller is the database owner or is a member of the sysadmin fixed server role, the principal must exist even when the user is accessing the database or instance of [!INCLUDEssNoVersion] through a Windows group membership. For example, assume the following conditions:

CompanyDomain\SQLUsers group has access to the Sales database.

CompanyDomain\SqlUser1 is a member of SQLUsers and, therefore, has implicit access to the Sales database.

Although CompanyDomain\SqlUser1 has access to the database through membership in the SQLUsers group, the statement EXECUTE @string_variable AS USER = ‘CompanyDomain\SqlUser1’ will fail because CompanyDomain\SqlUser1 does not exist as a principal in the database.

Specify a login or user that has the least privileges required to perform the operations that are defined in the statement or module. For example, do not specify a login name, which has server-level permissions, if only database-level permissions are required; or do not specify a database owner account unless those permissions are required.

Permissions are not required to run the EXECUTE statement. However, permissions are required on the securables that are referenced within the EXECUTE string. For example, if the string contains an INSERT statement, the caller of the EXECUTE statement must have INSERT permission on the target table. Permissions are checked at the time EXECUTE statement is encountered, even if the EXECUTE statement is included within a module.

EXECUTE permissions for a module default to the owner of the module, who can transfer them to other users. When a module is run that executes a string, permissions are checked in the context of the user who executes the module, not in the context of the user who created the module. However, if the same user owns the calling module and the module being called, EXECUTE permission checking is not performed for the second module.

If the module accesses other database objects, execution succeeds when you have EXECUTE permission on the module and one of the following is true:

The module is marked EXECUTE AS USER or SELF, and the module owner has the corresponding permissions on the referenced object. For more information about impersonation within a module, see EXECUTE AS Clause (Transact-SQL).

The module is marked EXECUTE AS CALLER, and you have the corresponding permissions on the object.

The module is marked EXECUTE AS user_name, and user_name has the corresponding permissions on the object.

Context Switching Permissions

To specify EXECUTE AS on a login, the caller must have IMPERSONATE permissions on the specified login name. To specify EXECUTE AS on a database user, the caller must have IMPERSONATE permissions on the specified user name. When no execution context is specified, or EXECUTE AS CALLER is specified, IMPERSONATE permissions are not required.

Examples: SQL Server

A. Using EXECUTE to pass a single parameter

The uspGetEmployeeManagers stored procedure in the [!INCLUDEssSampleDBnormal] database expects one parameter ( @EmployeeID ). The following examples execute the uspGetEmployeeManagers stored procedure with Employee ID 6 as its parameter value.

The variable can be explicitly named in the execution:

If the following is the first statement in a batch or an osql or sqlcmd script, EXEC is not required.

B. Using multiple parameters

The following example executes the spGetWhereUsedProductID stored procedure in the [!INCLUDEssSampleDBnormal] database. It passes two parameters: the first parameter is a product ID ( 819 ) and the second parameter, @CheckDate, is a datetime value.

C. Using EXECUTE ‘tsql_string’ with a variable

The following example shows how EXECUTE handles dynamically built strings that contain variables. This example creates the tables_cursor cursor to hold a list of all user-defined tables in the [!INCLUDEssSampleDBobject] database, and then uses that list to rebuild all indexes on the tables.

D. Using EXECUTE with a remote stored procedure

The following example executes the uspGetEmployeeManagers stored procedure on the remote server SQLSERVER1 and stores the return status that indicates success or failure in @retstat .

Applies to: [!INCLUDEsql2008-md] and later

E. Using EXECUTE with a stored procedure variable

The following example creates a variable that represents a stored procedure name.

F. Using EXECUTE with DEFAULT

The following example creates a stored procedure with default values for the first and third parameters. When the procedure is run, these defaults are inserted for the first and third parameters when no value is passed in the call or when the default is specified. Note the various ways the DEFAULT keyword can be used.

The Proc_Test_Defaults stored procedure can be executed in many combinations.

G. Using EXECUTE with AT linked_server_name

The following example passes a command string to a remote server. It creates a linked server SeattleSales that points to another instance of [!INCLUDEssNoVersion] and executes a DDL statement ( CREATE TABLE ) against that linked server.

Applies to: [!INCLUDEsql2008-md] and later

H. Using EXECUTE WITH RECOMPILE

The following example executes the Proc_Test_Defaults stored procedure and forces a new query plan to be compiled, used, and discarded after the module is executed.

I. Using EXECUTE with a user-defined function

The following example executes the ufnGetSalesOrderStatusText scalar user-defined function in the [!INCLUDEssSampleDBnormal] database. It uses the variable @returnstatus to store the value returned by the function. The function expects one input parameter, @Status . This is defined as a tinyint data type.

J. Using EXECUTE to query an Oracle database on a linked server

The following example executes several SELECT statements at the remote Oracle server. The example begins by adding the Oracle server as a linked server and creating linked server login.

Applies to: [!INCLUDEsql2008-md] and later

K. Using EXECUTE AS USER to switch context to another user

The following example executes a [!INCLUDEtsql] string that creates a table and specifies the AS USER clause to switch the execution context of the statement from the caller to User1 . The [!INCLUDEssDE] will check the permissions of User1 when the statement is run. User1 must exist as a user in the database and must have permission to create tables in the Sales schema, or the statement fails.

L. Using a parameter with EXECUTE and AT linked_server_name

The following example passes a command string to a remote server by using a question mark ( ? ) placeholder for a parameter. The example creates a linked server SeattleSales that points to another instance of [!INCLUDEssNoVersion] and executes a SELECT statement against that linked server. The SELECT statement uses the question mark as a place holder for the ProductID parameter ( 952 ), which is provided after the statement.

Applies to: [!INCLUDEsql2008-md] and later

M. Using EXECUTE to redefine a single result set

Some of the previous examples executed EXEC dbo.uspGetEmployeeManagers 6; which returned 7 columns. The following example demonstrates using the WITH RESULT SET syntax to change the names and data types of the returning result set.

Applies to: [!INCLUDEssSQL11] and later, [!INCLUDEssSDSfull]

N. Using EXECUTE to redefine a two result sets

When executing a statement that returns more than one result set, define each expected result set. The following example in [!INCLUDEssSampleDBobject] creates a procedure that returns two result sets. Then the procedure is executed using the WITH RESULT SETS clause, and specifying two result set definitions.

Applies to: [!INCLUDEssSQL11] and later, [!INCLUDEssSDSfull]

O. Using EXECUTE with AT DATA_SOURCE data_source_name to query a remote SQL Server

The following example passes a command string to an external data source pointing to a SQL Server instance.

Applies to: [!INCLUDEsssql19] and later

P. Using EXECUTE with AT DATA_SOURCE data_source_name to query compute pool in SQL Server Big Data Cluster

The following example passes a command string to an external data source pointing to a compute pool in SQL Server Big Data Cluster. The example creates a data source SqlComputePool against a compute pool in SQL Server Big Data Cluster and executes a SELECT statement against the data source.

Applies to: [!INCLUDEsssql19] and later

Q. Using EXECUTE with AT DATA_SOURCE data_source_name to query data pool in SQL Server Big Data Cluster

The following example passes a command string to an external data source pointing to compute pool in SQL Server big data cluster. The example creates a data source SqlDataPool against a data pool in SQL Server big data cluster and executes a SELECT statement against the data source.

Applies to: [!INCLUDEsssql19] and later

R. Using EXECUTE with AT DATA_SOURCE data_source_name to query storage pool in SQL Server Big Data Cluster

The following example passes a command string to an external data source pointing to compute pool in SQL Server Big Data Cluster. The example creates a data source SqlStoragePool against a data pool in SQL Server Big Data Cluster and executes a SELECT statement against the data source.

Applies to: [!INCLUDEsssql19] and later

Examples: Azure Synapse Analytics

A: Basic Procedure Execution

Executing a stored procedure:

Calling a stored procedure with name determined at runtime:

Calling a stored procedure from within a stored procedure:

B: Executing Strings

Executing a SQL string:

Executing a nested string:

Executing a string variable:

C: Procedures with Parameters

The following example creates a procedure with parameters and demonstrates 3 ways to execute the procedure:

Footer

© 2023 GitHub, Inc.

You can’t perform that action at this time.

You signed in with another tab or window. Reload to refresh your session. You signed out in another tab or window. Reload to refresh your session.