=========================================================================
STUFF FUNCTION
=========================================================================
Select STUFF((SELECT ','+ name from sys.columns WHERE object_id = OBJECT_ID('dbo.customers') FOR XML PATH('')),1,1,'')
select STUFF((select ',' + FirstName From Customers FOR XML PATH('')),1,1,'')
========================================================================
/****** Object: UserDefinedFunction [dbo].[fn_GetColumnNameByTableName] Script Date: 21-12-2020 12:08:57 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--SELECT dbo.fn_GetColumnNameByTableName('Customers')
ALTER FUNCTION [dbo].[fn_GetColumnNameByTableName]
(@TableName nvarchar(MAX))
RETURNS @ReturnTable TABLE (
TableJoinList NVARCHAR(MAX),
ColumnList NVARCHAR(MAX)
)
AS
BEGIN
DECLARE @TableJoinList NVARCHAR(MAX) = '';
DECLARE @ColumnList NVARCHAR(MAX) = '';
IF (@TableName = 'Customers')
BEGIN
SELECT @TableJoinList = 'Customers CU WITH(NOLOCK) LEFT JOIN AspNetUsers AU WITH(NOLOCK) ON AU.Id = CU.UserId',
@ColumnList = 'CustomerId,CustomerTypeId,UserId,FullName,AU.Email'
END
ELSE IF (@TableName = 'AspNetUsers')
BEGIN
SELECT @TableJoinList = '', @ColumnList = 'Id,FirstName,MiddleName,LastName,UserName'
END
ELSE IF (@TableName = 'AspNetRoles')
BEGIN
SELECT @TableJoinList = '',
@ColumnList = 'Id,Name,ColorCode,UserTypeId,PartnerAccountId'
END
INSERT INTO @ReturnTable (TableJoinList,ColumnList) VALUES (@TableJoinList,@ColumnList)
RETURN
End
==================================================================================================================================================
USE RevampV2_Dev
GO
ALTER PROC GetListData
@TableName varchar(1000) = 'AspNetRoles'
,@WhereClause varchar(1000) = '1=1'
,@PageNumber INT = 3
,@NumberOfRecords INT = 10
,@OrderBy varchar(75) = 'ASC'
,@SortOrderColumn varchar(75) = 'NAME'
AS
BEGIN
DECLARE @SqlCommand varchar(1000) = ''
DECLARE @ColumnList varchar(1000) = ''
DECLARE @TableJoinList varchar(1000) = ''
SELECT @TableJoinList = TableJoinList, @ColumnList = ColumnList FROM [dbo].[fn_GetColumnNameByTableName](@TableName)
SET @TableName = CASE WHEN ISNULL(@TableJoinList,'') <> '' THEN @TableJoinList ELSE @TableName END
SET @SqlCommand = 'SELECT ' + @ColumnList + ' FROM '+ @TableName + ' WHERE ' + @WhereClause +
' ORDER BY '+CAST(@SortOrderColumn AS VARCHAR) + ' ' + CAST(@OrderBy AS varchar) +
' OFFSET ' + CAST((@PageNumber - 1) * @NumberOfRecords AS varchar) +' ROWS
FETCH NEXT '+CAST(@NumberOfRecords AS varchar) +' ROWS ONLY'
PRINT @SqlCommand
EXEC (@SqlCommand)
END
