Stored Proedure to retrieve selective records.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Stored Proedure to retrieve selective records.

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-08-19 : 07:48:23

Joydip writes "I have collected a stored procedure in SQL Server to retrieve selected records from a query. But this stored procedure requires the order by clause. i want this to be modifed without the order by clause as parameter. A sample run of the procedure now is as follows:--

SelectPage 'select * from temp','id ASC',1,10

This would display all records from the table temp from 1 to 10. I don't require the parameter for ordering. This would be in the first parameter itself. Pls send me the updated procedure at the following email id: joydip.kanjilal@holool.com

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE SelectPage(@Select varchar(1000), @OrderBy varchar(1000),

@StartRow int, @EndRow int)

AS

BEGIN

declare @ColList varchar(2000);

declare @Where varchar(2000);

declare @i int;

declare @i2 int;

declare @tmp varchar(1000);

declare @dec varchar(1000);

declare @f varchar(100);

declare @d varchar(100);

declare @Symbol char(2);

declare @SQL varchar(5000);

declare @Sort varchar(1000);

set @Sort = @OrderBy + ', '

set @dec = ''

set @Where = ''

set @SQL = ''

set @i = charindex(',' , @Sort)

while @i != 0

begin

set @tmp = left(@Sort,@i-1)

set @i2 = charindex(' ', @tmp)

set @f = ltrim(rtrim(left(@tmp,@i2-1)))

set @d = ltrim(rtrim(substring(@tmp,@i2+1,100)))

set @Sort = rtrim(ltrim(substring(@Sort,@i+1,100)))

set @i = charindex(',', @Sort)

set @symbol = case when @d = 'ASC' then '>' else '<' end +

case when @i=0 then '=' else '' end

set @dec = @dec + 'declare @' + @f + ' sql_variant; '

set @ColList = isnull(replace(replace(@colList,'>','='),'<','=') + ' and ','') +

@f + @Symbol + ' @' + @f

set @Where = @Where + ' OR (' + @ColList + ') '

set @SQL = @SQL + ', @' + @f + '= ' + @f

end

set @SQL = @dec + ' ' +

'SET ROWCOUNT ' + convert(varchar(10), @StartRow) + '; ' +

'SELECT ' + substring(@SQL,3,7000) + ' from (' + @Select + ') a ORDER BY ' +

@OrderBy + '; ' + 'SET ROWCOUNT ' +

convert(varchar(10), 1 + @EndRow - @StartRow) + '; ' +

'select * from (' + @Select + ') a WHERE ' +

substring(@Where,4,7000) + ' ORDER BY ' + @OrderBy + '; SET ROWCOUNT 0;'

exec(@SQL)

END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-19 : 07:51:50

If you want pagination, refer this
http://weblogs.sqlteam.com/jeffs/archive/2003/12/22/672.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources