Using Adventureworks DB create a Table-Valued Function to return number of orders and Total Sales for each employee and department. To Call the Function...
UNIQUE constraint is used to enforce uniqueness of a column. It means that all values in the column are different i.e. the column should not allow any duplicate values. Both PRIMARY KEY and UNIQUE KEY are used to enforce uniqueness of a column. What is the difference between Primary Key and Unique Key? A table … Continue reading Unique Key Constraint with Example
Stored Procedure Function In Stored Procedure it is optional, it can return zero, single or multiple values Function Must Return Value Stored Procedures are pre-compile objects and can be executed whenever it is called Function is compiled and executed every time when it is called. Stored Procedure Can Accept Both Input and Output Parameters Function … Continue reading Difference between Stored Procedure and Functions in SQL Server
CREATE PROCEDURE SP_OwnersListing ( @OwnersId int = NULL ,@SearchString nvarchar(max) = NULL ,@SortString nvarchar(max) = ' ' ,@PageNumber int = 1 ) AS BEGIN DECLARE @OLSortString nvarchar(max), @PageSize int SET @OLSortString = @SortString SET @PageSize = 10 SET @SearchString = LTRIM(RTRIM(@SearchString)) SELECT Owners.Id As OwnerID, RL.PropertyId, RL.Id As ListingId, Concat(A.Number,',',A.Street,',',A.Suburb,',',A.City)As Location, P.Bedroom, P.Bathroom, P.ParkingSpace, RL.Title, … Continue reading Sample Solution for Creating Stored Procedure using Search, Sort and Pagination with Parameters
Basic Joins in SQL Generally, tables are related to each other using Foreign Key constraints. Joins in SQL combines records from two related tables to retrieve the data Different types of Joins Inner Join or Join: Returns only the matching rows from both the tables Outer Join: Outer Joins are of 3 types Full Outer … Continue reading Basic SQL Joins