Solved

Adding filter data in a stored procedure

Posted on 2008-10-24
11
442 Views
Last Modified: 2010-05-18
Hi

My SQL Server 2000 Stored Procedure below works fine.
But how do I add the @filterString into the SELECT statement?

I tried

if @directionData = 0
SELECT r.*, rs.*, c.* FROM requests AS r, requests_status AS rs, categories AS c WHERE r.status = rs.status_id AND r.category = c.category_id @filterString order by r.reuqest_id ASC

but that gave me a syntax error.

Thanks
AM
CREATE PROCEDURE usp_Request_GetRequests
(
	@directionData	int,
	@statusFilter int
) AS
 
declare @filterString varchar(500)
set @filterString = ''
if @statusFilter = 0
set @filterString = ' AND r.status = 0'
 
if @directionData = 0
SELECT r.*, rs.*, c.* FROM requests AS r, requests_status AS rs, categories AS c WHERE r.status = rs.status_id AND r.category = c.category_id order by r.reuqest_id ASC
if @directionData = 1
SELECT r.*, rs.*, c.* FROM requests AS r, requests_status AS rs, categories AS c WHERE r.status = rs.status_id AND r.category = c.category_id order by r.reuqest_id DESC

Open in new window

0
Comment
Question by:amoran
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
11 Comments
 
LVL 5

Expert Comment

by:Cvijo123
ID: 22797605
you can try it this way, i removed some if statments and use 1 query insted of 2 for order by with case
CREATE PROCEDURE usp_Request_GetRequests
(
        @directionData  int,
        @statusFilter int
) AS
 
 
SELECT 
	r.*, 
	rs.*, 
	c.* 
FROM requests AS r, requests_status AS rs, categories AS c 
WHERE 
	r.status = rs.status_id 
	AND r.category = c.category_id 
	AND (@statusFilter = 0 or r.status = 0 )
order by 
	case when @directionData = 0 then r.reuqest_id end, 
	case when @directionData = 1 then r.reuqest_id end DESC

Open in new window

0
 
LVL 5

Expert Comment

by:Cvijo123
ID: 22797644
sorry line 16 should be like this

AND (@statusFilter <> 0 or r.status = 0 )
0
 
LVL 1

Expert Comment

by:GeniusSteals
ID: 22797663
Try

DECLARE @sql AS varchar(MAX)

SELECT @sql='
SELECT r.*, rs.*, c.* AS FilterString FROM requests AS r, requests_status AS rs, categories AS c WHERE ' + @FilterString + '
r.status = rs.status_id AND r.category = c.category_id order by r.reuqest_id ASC'

EXEC (@sql)
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 

Author Comment

by:amoran
ID: 22797723
Hi Cvijo
I think you might misunderstand what I'm getting at.
Ill have data like this ...

if @statusFilter = 0
set @filterString = ' AND r.status = 0'
if @statusFilter = 1
set @filterString = ' AND r.status = 4'
if @statusFilter = 2
set @filterString = ' AND r.status = 5'

but it's how to fit that into the select statement.
0
 
LVL 1

Expert Comment

by:GeniusSteals
ID: 22797838
My code would work as long as there is always an entry for @FilterString - I use dynamic queries like this a lot when the WHERE clause can change at run time
0
 
LVL 5

Accepted Solution

by:
Cvijo123 earned 20 total points
ID: 22797840
mybe something like this then:


CREATE PROCEDURE usp_Request_GetRequests
(
        @directionData  int,
        @statusFilter int
) AS
 
 
Declare @status int
 
if @statusFilter = -1 then 
	set @status = -1 -- we will ignore status filter is you send -1 as @statusFiter
 
if @statusFilter = 0 then 
	set @status =  0 
 
if @statusFilter = 1 then 
	set @status =  4 
 
if @statusFilter = 2 then 
	set @status =  5 
 
 
 
SELECT 
        r.*, 
        rs.*, 
        c.* 
FROM requests AS r, requests_status AS rs, categories AS c 
WHERE 
        r.status = rs.status_id 
        AND r.category = c.category_id 
        AND (@status = -1 or r.status = @status )
order by 
        case when @directionData = 0 then r.reuqest_id end, 
        case when @directionData = 1 then r.reuqest_id end DESC

Open in new window

0
 

Author Comment

by:amoran
ID: 22820275
Sorry for the delay guys ...


When I try

CREATE PROCEDURE usp_Request_GetRequests
(
      @directionData      int,
      @statusFilter int
) AS

DECLARE @sql AS varchar(MAX)

SELECT @sql='SELECT r.*, rs.*, c.* AS FilterString FROM requests AS r, requests_status AS rs, categories AS c WHERE ' + @statusFilter + '
r.status = rs.status_id AND r.category = c.category_id order by r.reuqest_id ASC'

EXEC (@sql)

I get an error

Must declare the vaiable @sql

Any ideas?
Thanks
0
 

Author Comment

by:amoran
ID: 22820348
And If I try

CREATE PROCEDURE usp_Request_GetRequests
(
      @directionData      int,
      @statusFilter int
) AS

DECLARE @sql AS varchar(500)
DECLARE @directionString AS varchar(500)
DECLARE @filterString As varchar(200)

--if @statusFilter = 1 set @filterString =  'r.status = 1 AND '


SELECT @sql='SELECT r.*, rs.*, c.* AS FilterString FROM requests AS r, requests_status AS rs, categories AS c WHERE ' + @statusFilter + 'r.status = rs.status_id AND r.category = c.category_id order by r.reuqest_id ASC'

EXEC (@sql)

GO


then I get

Syntax error converting the varchar value 'SELECT r.*, rs.*, c.* AS FilterString FROM requests AS r, requests_status AS rs, categories AS c WHERE ' to a column of data type int.
0
 
LVL 5

Expert Comment

by:Cvijo123
ID: 22820755
amoran what about my example ?
in my case you are not using dynamic SQL and everything you asked should work imo.
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question