Skip to main content

Posts

Showing posts with the label NOT EXISTS in SQL Server

What is store procedure? How do they work? When do you use?

A stored procedure is a collection of SQL statements that has been created and stored in the database. It is a set of recompiled SQL statements that are used to perform a special task. Stored procedures create once a time and calls it n number of times and also reduces the network traffic and increase the performance. When do you use store procedure? I used store procedures in 1 of 3 scenarios, ·          Security, ·          Speed and ·          Transactions Types of SQL Procedures, 1.       System Stored Procedures 2.       User Defined Stored Procedures 3.       Extended Stored Procedures Syntax:- CREATE PROCEDURE < Procedure_Name, sysname, ProcedureName > -- Add the parameters for the stored procedure here < @Param1, sysname, @p1...
Read more

SQL Server BETWEEN operator

The BETWEEN operator is used to select the values within a range. Syntax: -- Used of BETWEEN SELECT col1, col1 FROM Table_name WHERE col1 BETWEEN val1 AND val2; -- CREATE TABLE CREATE TABLE [dbo].[Tbl_Demo]( [ID] [ int ] IDENTITY( 1 , 1 ) NOT NULL , [Name] [ varchar ]( 500 ) NULL , [Age] [ int ] NULL , [IsActive] [ bit ] NULL , [IsDeleted] [ bit ] NULL , CONSTRAINT [PK_Tbl_Demo] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO --INSERT TABLE ROWS SET IDENTITY_INSERT [dbo].[Tbl_Demo] ON GO INSERT [dbo].[Tbl_Demo] ([ID], [Name], [Age], [IsActive], [IsDeleted]) VALUES ( 1 , N 'Anil Singh' , 30 , 1 , 0 ) GO INSERT [dbo].[Tbl_Demo] ([ID], [Name], [Age], [IsActive], [IsDeleted]) VALUES ( 2 , N 'Aradhya' , 3 , 1 , 0 ) GO INSERT [dbo].[Tbl_Demo] ([ID], [N...
Read more

Use of EXISTS and NOT EXISTS in SQL Server

SQL EXISTS condition are used for combination of sub queries and its sub query returns at least one row. SQL EXISTS condition can be used in a SELECT , INSERT , UPDATE or DELETE statement. Query Syntax E xists   :     WHERE   EXISTS ( --subquery ); Query Syntax Not Exists :     WHERE   NOT EXISTS ( --subquery ); The Live Query example code as given below -- CREATE TABLE MOBILE CREATE TABLE [dbo] . [Mobile] (             [UID] [varchar] ( 30 ) NOT NULL,             [Account] [varchar] ( 30 ) NOT NULL,             [Mobile] [char] ( 10 ) NOT NULL,             [AlertToMobile] [char] ( 10 ) NULL,             [AlertToEmail] [v...
Read more