Click here to Skip to main content
15,886,199 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Hai all,

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

SQL
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
Posted
Updated 9-Jun-16 1:08am
v3
Comments
walterhevedeich 8-Mar-12 1:48am    
A stored procedure itself is a query. What do you really want? Elaborate your question.
kutz 2 8-Mar-12 1:54am    
Without executing as stored procedure I need to execute as querry format in my C# code.
That is Command.Text not Command.StoredProcedure

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!
 
Share this answer
 
Comments
kutz 2 8-Mar-12 2:40am    
Can I execute this SP in any database source type?Because I checked this SP with Sql server only.I didn't check with other database source type.Thats why I tried to convert this to querry
OriginalGriff 8-Mar-12 2:59am    
Pretty much - though you may have to make some small changes to work with Oracle or MySql. I haven't checked your code in the detail required to find out. But if you do need to change the SP for them, then you would have to change the query as well to cope with those systems.

I do know that it won't work with SqlCE, as it doesn't support stored procedures at all. But then, it probably won't work as a query with SqlCE either, as it is probably going to need multiple statements in a single command.
kutz 2 8-Mar-12 4:14am    
Ok thank you for you valuable information.Any way I need to convert stored procedure into querry because for depending upon the db source I can write the querry in my C# code itself by giving condition.But for stored procedure it is not possible.For every DB source I need to write different stored procedure.Because at runtime only we know db source.
kutz 2 8-Mar-12 4:42am    
Could anyone please help me.I am having trouble with this task
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.
 
Share this answer
 
Comments
kutz 2 8-Mar-12 2:13am    
You are now confused.I know I can't use Command.Text for stored procedure.Please describe how to convert this stored procedure into a querry(combination of more than one querry) so that I can execute as querry

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900