SQL Store Procedure Column Name List

We can get all column list of store procedure using sys.dm_exec_describe_first_result_set_for_object function as below example First create Store procedure. for example CREATE PROCEDURE SP_Get_All_Employee_List AS BEGIN     SET NOCOUNT ON;     SELECT * FROM dbo.EMPLOYEE_MASTER WHERE 1=2 END This SP return Employee_Master table columns. Now use sys.dm_exec_describe_first_result_set_for_objec fun for SP_Get_All_Employee_List as: SELECT name FROM sys.dm_exec_describe_first_result_set_for_object … Continue reading SQL Store Procedure Column Name List

Advertisements

SQL Month Between Two Dates

Using below query you can get months between two dates. create temporary table @tbl_Date_Range and provide from date and to date to get months between two dates.for example from jan-2016 to dec-2016. DECLARE @From_Date DATETIME,@To_Date DATETIME; DECLARE @tbl_Date_Range TABLE(RowID int NOT NULL primary key identity(1,1), MONTH DATETIME); DECLARE @Initial_Date DATETIME; SET @From_Date ='2016-01-01' SET @To_Date … Continue reading SQL Month Between Two Dates

Create Split Function In SQL

Create table valued function as below code SET QUOTED_IDENTIFIER ON SET ANSI_NULLS ON GO CREATE FUNCTION [dbo].[Split] (     @RowData nvarchar(MAX),     @SplitOn nvarchar(5) )   RETURNS @RtnValue table (     Id int identity(1,1),     Data nvarchar(100) ) AS   BEGIN     Declare @Cnt int     Set @Cnt = 1     While (Charindex(@SplitOn,@RowData)>0)     Begin … Continue reading Create Split Function In SQL

Get Comma Separated Value From SQL Select Statement

By using XML PATH we can get comma separated list from SQL select statement. For example here i am creating one demo table as below DECLARE @tbl TABLE (str NVARCHAR(MAX)) Now insert value in @tbl table as INSERT INTO @tbl( str ) VALUES ('test1') INSERT INTO @tbl( str ) VALUES ('test2') INSERT INTO @tbl( str … Continue reading Get Comma Separated Value From SQL Select Statement