Set Column Visibility in SSRS Report Based On Parameters Values.

In this article We  will see one real time scenario, the usage of Column Visibility property in SSRS. STEP 1: Create Sample Data set in our report, You can use the Below Query. SELECT ‘Steven Paul’ AS NAME ,’American’ AS LOCATION ,’American entrepreneur’ AS BUSINESS , ‘NeXT’ AS FOUNDER, ‘Q1 2016′ AS YEAR,’$10.00 billion’ AS TURNOVER,’Steve … More Set Column Visibility in SSRS Report Based On Parameters Values.

Create dynamic PIVOT queries in SQL Server.

In this Script we will look how we can dynamically create the pivot Script to handle the unknown values. USE AdventureWorks2012; GO CREATE TABLE dbo.Players ( FormatID INT, PlayerID INT , Name NVARCHAR(255) NOT NULL, Formats Varchar(50), Average Int ); INSERT dbo.Players VALUES (1,1, N’MS Dhoni’,’ODI’,55), (1,2, N’Rohit Sharma’,’ODI’,40), (1,3, N’Virat kholi’,’ODI’,60), (1,4, N’ABD’,’ODI’,60), (2,1, … More Create dynamic PIVOT queries in SQL Server.

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.

Things to know before going to join new project.

1. Get List of Stored Procedure In a Database  select ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_DEFINITION from INFORMATION_SCHEMA.ROUTINES where ROUTINE_TYPE=’PROCEDURE’ 2. Find All Number of  Functions  In a Database SELECT ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = ‘FUNCTION’. 3. Number of Triggers In a Database. SELECT sysobjects.name AS trigger_name ,USER_NAME(sysobjects.uid) AS trigger_owner ,s.name AS table_schema ,OBJECT_NAME(parent_obj) AS table_name … More Things to know before going to join new project.

Delete repeated Ids from Table – Performance Improvement.

CREATE TABLE dbo.Data( FriendlyFunctionCode INT, MemberFirmId VARCHAR(100),FunctionLevel3Desc VARCHAR(100)) GO INSERT INTO dbo.Data VALUES(1,’Value1′,’Value2′) ,(1,’Value2′,’Value3′) ,(2,’Value4′,’Value5′) SELECT * FROM dbo.Data ;WITH CTE AS ( SELECT FriendlyFunctionCode,MemberFirmId, FunctionLevel3Desc, Row_number()OVER (PARTITION BY FriendlyFunctionCode ORDER BY (SELECT 1) ) AS Rn FROM dbo.Data) SELECT * FROM CTE WHERE Rn = 1 DROP TABLE Data