Can this query be written in a sproc, dynamic only

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

Transact-SQL (2005)

Can this query be written in a sproc, dynamic only

Author

Topic

sql777
Constraint Violating Yak Guru

314 Posts

Posted - 2008-02-21 : 22:09:28

Hi,
I have this query which I *think* has to be done dynamically, if it can be done inside a stored procedure that would be ideal.

SELECT articleID
FROM tblArticles
WHERE
isLive = @isLive
AND userID = @userID (could be ommitted)
AND dtCreated >= @dateCreated (could be ommitted)

ORDER By dtCreated ASC OR DESC( or userID, title)

So basically there WHERE clause has some filters that may or may not be present, and the order by cluase can have various fields to order by, and the sort order can be asc or desc.

Possible in a sproc?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-21 : 22:40:09

I think you can do this without D-SQL

Create Procedure GerArticles
@isLive bit=NULL,
@userID int=NULL,
@DateCreated datetime=NULL,
@OrderBy varchar(100)='datecreated',
@SortOrder varchar(50)='Ascending'
AS

SELECT articleID
FROM tblArticles
WHERE
(isLive = @isLive OR @isLive IS NULL)
AND (userID = @userID OR @userID IS NULL)
AND (dtCreated >= @dateCreated OR @dateCreated IS NULL)
ORDER By
CASE WHEN @OrderBy='datecreated' AND @SortOrder='Ascending'
     THEN dtCreated ASC
     WHEN @OrderBy='datecreated' AND @SortOrder='Descending' 
     THEN dtCreated DESC
     WHEN @OrderBy='userID' AND @SortOrder='Ascending'
     THEN userID ASC
     WHEN @OrderBy='userID' AND @SortOrder='Descending' 
     THEN userID DESC
     WHEN @OrderBy='title' AND @SortOrder='Ascending'
     THEN title ASC
     WHEN @OrderBy='title' AND @SortOrder='Descending' 
     THEN title DESC
END
GO

I've assumed the default sort order to be based on ascending order date created. If not change defualt values for last two params.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-02-21 : 22:40:50

Possible with dynamic sql.

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2008-02-22 : 00:04:16

Hi Visakh,

the case u used in Order by clause ll give an error message,
all the columns returned in a case should have same datatype.

we can do that in a little different way

first we move all data into #table and then we write dynamic sql for that #table

Create Procedure GerArticles
@isLive bit=NULL,
@userID int=NULL,
@DateCreated datetime=NULL,
@OrderBy varchar(100)='datecreated',
@SortOrder varchar(50)='Ascending'
AS

SELECT articleID
FROM tblArticles
INTO #TBL
WHERE
(isLive = @isLive OR @isLive IS NULL)
AND (userID = @userID OR @userID IS NULL)
AND (dtCreated >= @dateCreated OR @dateCreated IS NULL)

DECLARE @STR VARCHAR(MAX)
SELECT @STR = ''
SELECT @STR = ' SELECT * FROM #TBL ORDER BY ' + @OrderBy + ' ' + @SortOrder

EXEC (@STR)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-22 : 00:58:18

quote:
Originally posted by PeterNeo

Hi Visakh,

the case u used in Order by clause ll give an error message,
all the columns returned in a case should have same datatype.

we can do that in a little different way

first we move all data into #table and then we write dynamic sql for that #table

Create Procedure GerArticles
@isLive bit=NULL,
@userID int=NULL,
@DateCreated datetime=NULL,
@OrderBy varchar(100)='datecreated',
@SortOrder varchar(50)='Ascending'
AS

SELECT articleID
FROM tblArticles
INTO #TBL
WHERE
(isLive = @isLive OR @isLive IS NULL)
AND (userID = @userID OR @userID IS NULL)
AND (dtCreated >= @dateCreated OR @dateCreated IS NULL)

DECLARE @STR VARCHAR(MAX)
SELECT @STR = ''
SELECT @STR = ' SELECT * FROM #TBL ORDER BY ' + @OrderBy + ' ' + @SortOrder

EXEC (@STR)

Yeah thats true. How about Casting all the return fields to same datatype?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-22 : 01:33:34

quote:

Originally posted by visakh16

I think you can do this without D-SQL

Create Procedure GerArticles
@isLive bit=NULL,
@userID int=NULL,
@DateCreated datetime=NULL,
@OrderBy varchar(100)='datecreated',
@SortOrder varchar(50)='Ascending'
AS

SELECT articleID
FROM tblArticles
WHERE
(isLive = @isLive OR @isLive IS NULL)
AND (userID = @userID OR @userID IS NULL)
AND (dtCreated >= @dateCreated OR @dateCreated IS NULL)
ORDER By
CASE WHEN @OrderBy='datecreated' AND @SortOrder='Ascending'
     THEN dtCreated ASC
     WHEN @OrderBy='datecreated' AND @SortOrder='Descending' 
     THEN dtCreated DESC
     WHEN @OrderBy='userID' AND @SortOrder='Ascending'
     THEN userID ASC
     WHEN @OrderBy='userID' AND @SortOrder='Descending' 
     THEN userID DESC
     WHEN @OrderBy='title' AND @SortOrder='Ascending'
     THEN title ASC
     WHEN @OrderBy='title' AND @SortOrder='Descending' 
     THEN title DESC
END
GO

I've assumed the default sort order to be based on ascending order date created. If not change defualt values for last two params.

It is not possible to include DESC/ASC inside CASE expression in an Order by caluse. However you can use it at the end of END

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-22 : 01:38:23

quote:
Originally posted by madhivanan

quote:
Originally posted by visakh16

I think you can do this without D-SQL

Create Procedure GerArticles
@isLive bit=NULL,
@userID int=NULL,
@DateCreated datetime=NULL,
@OrderBy varchar(100)='datecreated',
@SortOrder varchar(50)='Ascending'
AS

SELECT articleID
FROM tblArticles
WHERE
(isLive = @isLive OR @isLive IS NULL)
AND (userID = @userID OR @userID IS NULL)
AND (dtCreated >= @dateCreated OR @dateCreated IS NULL)
ORDER By
CASE WHEN @OrderBy='datecreated' AND @SortOrder='Ascending'
THEN dtCreated ASC
WHEN @OrderBy='datecreated' AND @SortOrder='Descending'
THEN dtCreated DESC
WHEN @OrderBy='userID' AND @SortOrder='Ascending'
THEN userID ASC
WHEN @OrderBy='userID' AND @SortOrder='Descending'
THEN userID DESC
WHEN @OrderBy='title' AND @SortOrder='Ascending'
THEN title ASC
WHEN @OrderBy='title' AND @SortOrder='Descending'
THEN title DESC
END
GO

I've assumed the default sort order to be based on ascending order date created. If not change defualt values for last two params.

It is not possible to include DESC/ASC inside CASE expression in an Order by caluse. However you can use it at the end of END

Madhivanan

Failing to plan is Planning to fail

is it. a good learning point for me. Thanks madhi

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-02-22 : 12:34:41

Maybe this would help?
http://www.sqlteam.com/article/dynamic-order-by

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources