T-SQL Dynamic WHERE and ORDER BY


Answer to this thread: http://forums.asp.net/t/1298204.aspx

Declare @FilterBy nvarchar(50)

Declare @Filter nvarchar(50)

Declare @SortBy nvarchar(50)

Declare @Order nvarchar(4)

 

SET

@FilterBy=‘Country’

SET

@Filter=‘s’

SET

@SortBy=‘Rating’

SET

@Order=‘ASC’

 

SELECT

Category, Country, State, City, DateUploaded, Rating, NumRatings, NumViews, NumComments FROM [Photos]

WHERE

(ISNULL(Category,) = CASE @FilterBy WHEN ‘Category’ THEN @Filter ELSE ISNULL(Category,) END ) AND

(ISNULL(Country,) = CASE @FilterBy WHEN ‘Country’ THEN @Filter ELSE ISNULL(Country,) END ) AND

(ISNULL(State,) = CASE @FilterBy WHEN ‘State’ THEN @Filter ELSE ISNULL(State,) END ) AND

(ISNULL(City,) = CASE @FilterBy WHEN ‘City’ THEN @Filter ELSE ISNULL(City,) END )

ORDER

BY

CASE

@Order

WHEN

‘ASC’ THEN

CASE

@SortBy

WHEN

‘DateUploaded’ THEN DateUploaded

WHEN ‘Rating’ THEN Rating

WHEN ‘NumRatings’ THEN NumRatings

WHEN ‘NumViews’ THEN NumViews

WHEN ‘NumComments’ THEN NumComments END

END

ASC,

CASE

@Order

WHEN

‘DESC’ THEN

CASE

@SortBy

WHEN

‘DateUploaded’ THEN DateUploaded

WHEN ‘Rating’ THEN Rating

WHEN ‘NumRatings’ THEN NumRatings

WHEN ‘NumViews’ THEN NumViews

WHEN ‘NumComments’ THEN NumComments END

END

DESC

 

–table script:

CREATE

TABLE [dbo].[Photos](

[Category] [nvarchar]

(255) NULL,

[Country] [nvarchar]

(255) NULL,

[State] [nvarchar]

(255) NULL,

[City] [nvarchar]

(255) NULL,

[DateUploaded] [nvarchar]

(255) NULL,

[Rating] [nvarchar]

(255) NULL,

[NumRatings] [nvarchar]

(255) NULL,

[NumViews] [nvarchar]

(255) NULL,

[NumComments] [nvarchar]

(255) NULL,

[id] [int] Not

NULL

)

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s