Output for this query - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

Output for this query

Author

Topic

Vinith
Starting Member

1 Post

Posted - 2011-03-23 : 13:52:10

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 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)

can you please explain me what does this store proc do??

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-03-24 : 11:19:26

[code]
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 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)
[/code]

If you don't have the passion to help people, you have no passion

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-03-24 : 11:19:56

it is doing dynamic query based on the parameters sent in

If you don't have the passion to help people, you have no passion

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-03-25 : 16:16:41

Do a print @strSql instead of EXEC (@strSql)
and you will see the generated statement.

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources