Programming constructs

Programming constructs:
Programming constructs are used to execute the set of sql statements as a unit. These are also known as Control Structures.
1. If-else
2. Begin-end
3. While

If-else: It is used to execute the statement based on condition.
Syntax: if (condition)
Begin

End
Else
Begin

End
If condition is true then statement1 is executed otherwise statement2 will be executed.

While: If the condition is true then statements under while will be excluded repeatedly until the given condition false
Syntax:
While (condition)
Begin

statement1
statement1
statement1
……………….
End.

Named Batches: Set of T-Sql statements can written and executed as a single unit with a proper name called Named Batch. These includes
a. Stored procedures
b. User defined functions
c. Triggers
Stored procedures: Stored procedures are one of the database objects. There are two types of stored procedures available in Sql Server.
a. System Defined Stored Procedures
b. User Defined Stored Procedures
System Defined Stored Procedures: These are also known as predefined or built- in stored procedures.
Example:
SP_HELP
SP_RENAMEDB
SP_RENAME
SP_HELPCONSTRAINT
SP_HELPTEXT
……
…..
……

User Defined Stored Procedures: Procedures created by the user are called used defined stored procedures.
Syntax: create proc [edure] procedurename [@para 1 datatype (size)[=default_value][output]@ para 2 datatype (size)[=default_value][value],…..]
As
begin
select statement
end

Syntax to execute the user defined stored procedure:
Exec [ute] procedurename [value1,value2,……]

Note: The number of values supplied through EXEC statement must be equal to the number parameters.

Example1: write a Procedure to select the data from EMP table.
CREATE PROCEDURE P1
AS
BEGIN
SELECT * FROM EMP
END
EXEC P1

Example2: Write a procedure to add two numbers
CREATE PROCEDURE P3 @A INT=10,@B INT=20
AS
BEGIN
DECLARE @C INT
SET @C=@A+@B
PRINT @C
END
EXEC P3
Output: 30
EXEC P3 25,45
Output: 70

Note: Server will give highest priority to the user supplied values rather than default values.

Programming constructs