Tutorial by Examples

Using the Authors table in the Library Database CREATE PROCEDURE GetName ( @input_id INT = NULL, --Input parameter, id of the person, NULL default @name VARCHAR(128) = NULL --Input parameter, name of the person, NULL default ) AS BEGIN SELECT Name + ' is from ' + Country...

Stored procedures can return values using the OUTPUT keyword in its parameter list. Creating a stored procedure with a single out parameter CREATE PROCEDURE SprocWithOutParams ( @InParam VARCHAR(30), @OutParam VARCHAR(30) OUTPUT ) AS BEGIN SELECT @OutParam = @InParam + ' must co...

Create example table Employee: CREATE TABLE Employee ( Id INT, EmpName VARCHAR(25), EmpGender VARCHAR(6), EmpDeptId INT ) Creates stored procedure that checks whether the values passed in stored procedure are not null or non empty and perform insert operation in Employee ta...

Dynamic SQL enables us to generate and run SQL statements at run time. Dynamic SQL is needed when our SQL statements contains identifier that may change at different compile times. Simple Example of dynamic SQL: CREATE PROC sp_dynamicSQL @table_name NVARCHAR(20), @col_name NVARCHAR(2...

First lets get some data into a temp table named #systables and ad a incrementing row number so we can query one record at a time select o.name, row_number() over (order by o.name) as rn into #systables from sys.objects as o where o.type = 'S' Next we declare some var...

CREATE PROCEDURE SprocWithSimpleLoop ( @SayThis VARCHAR(30), @ThisManyTimes INT ) AS BEGIN WHILE @ThisManyTimes > 0 BEGIN PRINT @SayThis; SET @ThisManyTimes = @ThisManyTimes - 1; END RETURN; END GO