DATABASE & SQL/PLSQL

adplus-dvertising
Programming in SQL Server
Previous Home Next

programming in sql provides various functionalities are /p>

  1. Batches
  2. Variables
  3. Printing Messages
  4. Comments
  5. Control-of-flow statements

Batches

  1. Batches are groups of SQL statement submitted together to SQL Server  for execution
  2. A batch is parsed
  3. A batch is optimized
  4. A batch is compiled/li>
  5. A batch is executed
  6. SQL server compiled the statements  of a batch into a single unit called an execution plane
  7. The statement in the execution plane are then executed one at a time
  8. There is any error in a batch, no statement in the batch is executed

Restriction

  1. You cannot bind rules and defaults to columns and use them in the same batch
  2. You can not define and use the CHECK constraints in the same batch
  3. You can not drop object and recreate them in the same batch
  4. you cannot alter a table and reference the new column in the same batch

Variables

Variable used to store a temporary value. We can declare and assign a value to a variable, declared a variable by using DECLARE statement.

Syntax:

DECLARE @variable_name data_type

The @ symbol before the variable name. This symbol is required and is used by the query processor to identify variable.

Example:

DECLARE @empID int
SELECT @empID=max(emp_id)
FROM employee
SELECT new_empID=@empID

In the first line DECLARE keyword declare a variable empID of int type, and in second line the maximum value of emp_id attributes from employee table stored in the empID variable

output

s

Global Variables: Global variable are system-supplied and predefined. These variables are distinguished from local variables by having two @ signs preceding their names

Previous Home Next