如何将存储过程转换为querry

问题描述

Hai all，



你能不能帮助我将以下存储过程转换为querry。

Hai all,

Could you anyone please help me for converting the following stored procedure into a querry.

ALTER PROCEDURE [dbo].[GetTableColumnsBaseOnConfiguration]
(
	@TableName VARCHAR(50)='',
	@Role_Id INT=0,
	@PageSize INT=0,
	@SkipRec INT=0,
	@SortColumn VARCHAR(100)='',
	@SortOrder VARCHAR(20)='ASC',
	@ConnectionId INT =0,
	@ReturnPaging VARCHAR(20)='' OUTPUT
)
AS
BEGIN

	SET NOCOUNT ON;

	DECLARE @COLUMNSLIST VARCHAR(MAX)
	DECLARE @ALLCOLUMNLIST VARCHAR(MAX)
	DECLARE @SELCTQUERY VARCHAR(MAX)
	DECLARE @KEYCOLUMNSWHEREQUERY VARCHAR(MAX)
	SET @KEYCOLUMNSWHEREQUERY=''



	SELECT @KEYCOLUMNSWHEREQUERY=COALESCE(@KEYCOLUMNSWHEREQUERY+'  ['+ORIGINAL_COLUMN_NAME+']  NOT IN (SELECT TOP '+CAST(@SkipRec  AS VARCHAR(20))+'  ['+ORIGINAL_COLUMN_NAME+']  FROM '+@TableName+') AND','')
	FROM REP_EXT_TABLE_INFO
	WHERE PRIMARYKEY_FLAG IS NOT NULL
	AND TABLE_NAME=@TableName
	AND CONNECTION_ID=@ConnectionId

	IF LEN(@KEYCOLUMNSWHEREQUERY)>0
		SET @KEYCOLUMNSWHEREQUERY=SUBSTRING(@KEYCOLUMNSWHEREQUERY,0,LEN(@KEYCOLUMNSWHEREQUERY)-3)



	SELECT
	@ALLCOLUMNLIST=COALESCE(@ALLCOLUMNLIST + ', ', '') + ISNULL('['+ORIGINAL_COLUMN_NAME+']','')
	FROM REP_EXT_TABLE_INFO
	WHERE TABLE_NAME=@TableName
	AND CONNECTION_ID=@ConnectionId



	SELECT
	@COLUMNSLIST=COALESCE(@COLUMNSLIST + ', ', '') + ISNULL('['+C.ORIGINAL_COLUMN_NAME+']','')
	FROM REP_EXT_TABLE_INFO AS C
	INNER JOIN iDSS_ColumnConfiguration AS CC ON CC.ColumnName=C.ORIGINAL_COLUMN_NAME
	WHERE TABLE_NAME=@TableName
	AND CC.TableName=@TableName
	AND CC.Role_Id=@Role_Id
	AND CC.ReadAccess=1
	AND CONNECTION_ID=@ConnectionId




	IF @SortColumn <>''
		BEGIN
			SET @KEYCOLUMNSWHEREQUERY=REPLACE(@KEYCOLUMNSWHEREQUERY,@TableName,@TableName+' ORDER BY ['+@SortColumn+'] '+@SortOrder)
		END
	SET @SELCTQUERY='SELECT TOP '+ CAST(@PageSize  AS VARCHAR(20)) +' '+@COLUMNSLIST+' FROM '+@TableName

	IF @KEYCOLUMNSWHEREQUERY<>''
		BEGIN
			SET @SELCTQUERY=@SELCTQUERY+' WHERE '+@KEYCOLUMNSWHEREQUERY
			SET @ReturnPaging='true'
		END
	ELSE
		BEGIN
			SET @ReturnPaging='false'
			SET @SELCTQUERY='SELECT '+@COLUMNSLIST+' FROM '+@TableName
		END

	IF @SortColumn <>''
		BEGIN
			SET @SELCTQUERY=@SELCTQUERY+' ORDER BY ['+@SortColumn+'] '+@SortOrder
		END
	PRINT @SELCTQUERY
	--EXEC(@SELCTQUERY)

	SELECT
		C.ORIGINAL_COLUMN_NAME AS COLUMN_NAME,
		@ALLCOLUMNLIST AS ALLCOLUMNS,
		C.DATA_TYPE AS DATATYPE,
		C.DATA_LENGTH AS [LENGTH],
		CC.CreateAccess,
		CC.ReadAccess,
		CC.UpdateAccess,
		CC.DeleteAccess,
		CASE WHEN C.PRIMARYKEY_FLAG IS NOT NULL
		THEN 'KEYCOLUMN'
		ELSE 'COLUMN'
		END AS COLUMN_TYPE,
		@SELCTQUERY AS SELECTQUERY
	FROM REP_EXT_TABLE_INFO AS C
	INNER JOIN iDSS_ColumnConfiguration AS CC ON CC.ColumnName=C.ORIGINAL_COLUMN_NAME
	WHERE C.Table_Name=@TableName
	AND CC.TableName=@TableName
	AND CC.Role_Id=@Role_Id
	AND CC.ReadAccess=1
	AND CONNECTION_ID=@ConnectionId
END

提前致谢

Thanks in advance

推荐答案

这篇关于如何将存储过程转换为querry的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！