TransactSQL DECLARE varname vartype SET varname varvalue DECLARE

Transact-SQL

Переменные DECLARE @var_name var_type, … SET @var_name = var_value

Курсоры DECLARE cursor_name CURSOR FOR select_statement OPEN cursor_name FETCH [NEXT] cursor_name [INTO variable_list] CLOSE cursor_name DEALLOCATE cursor_name

Курсоры: пример DECLARE employee_cursor CURSOR FOR SELECT id, name FROM employee DECLARE @emp_id INT, @emp_name VARCHAR(32) OPEN employee_cursor FETCH employee_cursor INTO @emp_id, @emp_name WHILE (@@FETCH_STATUS = 0) BEGIN <do something> FETCH employee_cursor INTO @emp_id, @emp_name END CLOSE employee_cursor DEALLOCATE employee_cursor

Обработка исключений BEGIN TRY -- RAISERROR with severity 11 -19 will cause execution to -- jump to the CATCH block. RAISERROR ( { msg_id | msg_str | @local_variable } { , severity , state } [ , argument [ , . . . n ] ] ) [ WITH option [ , . . . n ] ] END TRY BEGIN CATCH DECLARE @Error. Message NVARCHAR(4000); DECLARE @Error. Severity INT; DECLARE @Error. State INT; SELECT @Error. Message = ERROR_MESSAGE(), @Error. Severity = ERROR_SEVERITY(), @Error. State = ERROR_STATE(); … END CATCH

Обработка исключений (пример) RAISERROR ('Error raised because of wrong data. ', -- Message text. 16, -- Severity. 1 -- State. );

Транзакции BEGIN TRANSACTION … IF (@@ERROR = 0) COMMIT [ TRANSACTION ] ELSE ROLLBACK [ TRANSACTION ]

Процедуры CREATE PROC [ EDURE ] procedure_name [ { @parameter data_type } [ = default ] [ OUTPUT ] ] [ , . . . n ] AS sql_statement

Процедуры: пример CREATE PROCEDURE Example. Proc ( @id INT = 0, @name VARCHAR(32) = ‘’, @salary INT OUTPUT ) AS BEGIN DECLARE @default_salary INT SET @salary = (SELECT …) END

Функции, возвращающие значение CREATE FUNCTION function_name ( [ { @parameter_name [AS] scalar_parameter_data_type [ = default ] } [ , . . . n ] ] ) RETURNS scalar_return_data_type [ AS ] BEGIN END function_body RETURN scalar_expression

Функции: пример CREATE FUNCTION Example. Func ( @id INT = 0, @name VARCHAR(32) = ‘’ ) RETURNS INT AS BEGIN DECLARE @salary INT SET @salary = (SELECT …) RETURN @salary END

Функции, возвращающие таблицу CREATE FUNCTION function_name ( [ { @parameter_name [AS] scalar_parameter_data_type [ = default ] } [ , . . . n ] ] ) RETURNS @return_variable TABLE <table_type_definition> [ AS ] BEGIN function_body END RETURN

Функции: пример CREATE FUNCTION Example. Func ( @id INT = 0, @name VARCHAR(32) = ‘’ ) RETURNS @table TABLE (salary INT) AS BEGIN INSERT INTO @table SELECT … RETURN END

Вызов процедур и функций n n n Процедуры: EXECUTE Example. Proc @id, @name, @salary Функции, возвращающие значение: SET @salary = Example. Func(@id, @name) SELECT Example. Func(@id, @name) Функции, возвращающие таблицу: SELECT * FROM Example. Func(@id, @name)