如何将存储过程转换为querry [英] How to convert stored procedure into 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

推荐答案

为什么？

这是一个合理大小的SP - 执行相同工作的任何查询都将具有相同的大小或更大，因此执行速度更慢，使用更多带宽，并且通常效率较低。还存在（相当大）错误蔓延的风险，因为它很容易作为查询进行修改，并且更难以作为SP。

将其保留为SP，并按原样运行！

Why?
That is a reasonably sized SP - any query that does the same job is going to be the same size or larger, so it will execute slower, use more bandwidth, and generally be less efficient. There is also the (considerable) risk of an error creeping in, since it is easy to modify as a query, and harder as an SP.
Leave it as an SP, and run it as such!

您不能使用 Command.Text 来存储过程。



您可以将选择查询集移动到您的代码中，并使用文本选项单独执行它们。

You cannot use Command.Text for a stored procedure.

You can move the set of select queries to your code and execure them individually with the Text option.

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