Why developers are using SET ANSI_NULLS ON/OFF setting in SQL Stored Procedures.

Letz understand SET ANSI_NULLS ON /OFF with an example. CREATE TABLE dbo.Players(FirstName VARCHAR(50),LastName Varchar(50)) INSERT INTO dbo.Players VALUES(‘RAHUL’,’DRAVID’), (‘DHONI ‘,’MS’), (‘LAXMAN’, NULL) SELECT * FROM Players SET ANSI_NULLS ON GO SELECT * FROM dbo.Players WITH(NOLOCK) WHERE LastName = NULL SELECT * FROM dbo.Players WITH(NOLOCK) WHERE LastName <> NULL RESULT FirstName LastName ————————————————– —————————– (0 row(s) … More Why developers are using SET ANSI_NULLS ON/OFF setting in SQL Stored Procedures.

XML as a Input Parameter in Stored Procedure.

IF EXISTS (SELECT 1 FROM sys.procedures WHERE object_id = object_id(N’XMLDOC’)) DROP PROCEDURE XMLDOC GO Create Procedure XMLDOC @XMLDoc xml as begin DECLARE @XmlDocumentHandle INT EXEC sp_xml_preparedocument @XmlDocumentHandle OUTPUT, @XMLDoc,'<ROOT xmlns:xyz=”urn:MyNamespace”/>’ select * FROM OPENXML (@XmlDocumentHandle,’/root/Resurce’,1)–with student WITH ( ID int, Name varchar(30), Technology varchar(30), EXP numeric(3,1) ) EXEC sp_xml_removedocument @XmlDocumentHandle end exec XMLDOC @XMLDoc = … More XML as a Input Parameter in Stored Procedure.