Output

Question

Output

vinithreddy86

Newbie

Points: 3
More actions
March 23, 2011 at 12:10 pm

#389119

CREATE PROCEDURE TestSproc2
@size int = 20,
@current int ,
@columns varchar(1000) = '*',
@tableName varchar(100),
@condition varchar(1000) = '',
@ascColumn varchar(100) = '',
@bitOrderType bit = 0,
@pkColumn varchar(50) = ''
AS
DECLARE @strTemp varchar(300)
DECLARE @strSql varchar(5000)
DECLARE @strOrderType varchar(1000)
BEGIN
IF @bitOrderType = 1
BEGIN
SET @strOrderType = ' ORDER BY '+@ascColumn+' DESC'
SET @strTemp = '<(SELECT min'
END
ELSE
BEGIN
SET @strOrderType = ' ORDER BY '+@ascColumn+' ASC'
SET @strTemp = '>(SELECT max'
END
IF @current = 1
BEGIN
IF @condition != ''
SET @strSql = 'SELECT TOP '+STR(@size)+' '+@columns+' FROM '+@tableName+
' WHERE '+@condition+@strOrderType
ELSE
SET @strSql = 'SELECT TOP '+STR(@size)+' '+@columns+' FROM '+@tableName+@strOrderType
END
ELSE
BEGIN
IF @condition !=''
SET @strSql = 'SELECT TOP '+STR(@size)+' '+@columns+' FROM '+@tableName+
' WHERE '+@condition+' AND '+@pkColumn+@strTemp+'('+@pkColumn+')'+' FROM (SELECT TOP '+STR((@current-1)*@size)+
' '+@pkColumn+' FROM '+@tableName+ 'where'+@condition+@strOrderType+') AS TabTemp)'+@strOrderType
ELSE
SET @strSql = 'SELECT TOP '+STR(@size)+' '+@columns+' FROM '+@tableName+
' WHERE '+@pkColumn+@strTemp+'('+@pkColumn+')'+' FROM (SELECT TOP '+STR((@current-1)*@size)+' '+@pkColumn+
' FROM '+@tableName+@strOrderType+') AS TabTemp)'+@strOrderType
END
END
EXEC (@strSql)

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jim Murphy SSCertifiable Points: 6263 More actions · Answer 1

Pretty. But I didn't see a question or a statement in there.

Please use the forum to ask questions only. There are other places to properly post pretty code for others to benefit from.

Jim

Jim Murphy
http://www.sqlwatchmen.com
@SQLMurph

Alicia Rose Ten Centuries Points: 1044 More actions · Answer 2

Vinit,

Did you get an output for the stored proc?

If so can you please explain.

Alicia Rose

Alicia Rose Ten Centuries Points: 1044 More actions · Answer 3

Jim

Can you please help by explaining what is the purpose of the stored proc and what are the possible testcases and test scripts for it.

Thanks,

Alicia Rose

Alicia Rose Ten Centuries Points: 1044 More actions · Answer 4

Jim

Can you please help by explaining what is the purpose of the stored proc and what are the possible test cases and test scripts for it.

Thanks,

Alicia Rose

Alicia Rose Ten Centuries Points: 1044 More actions · Answer 5

Jim

Can you please help by explaining what is the purpose of the stored proc and what are the possible test cases and test scripts for it.

Thanks,

Alicia Rose

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 6

Alicia Rose (3/27/2011)
Jim
Can you please help by explaining what is the purpose of the stored proc and what are the possible test cases and test scripts for it.
Thanks,

Why not just ask the original person who wrote it? Also ask them why they didn't document their code.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)