Stored procedure problem. - 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 2000 Forums

SQL Server Development (2000)

Stored procedure problem.

Author

Topic

macca
Posting Yak Master

146 Posts

Posted - 2008-04-17 : 07:48:41

I am running this stored procedure but it is not returning anything:

CREATE PROCEDURE sproc_SearchDisplay

(
@DateFrom Datetime,
@DateTo Datetime,
@Name varchar(150),
@BehalfOf varchar(150),
@RefNo varchar(10),
@DeptId int,
@Sdid char(20)
)

AS

BEGIN
SELECT cheq_id, cheq_daterec, cheq_pername, cheq_behalf, cheq_behalf1, cheq_behalf2, cheq_amt
FROM RecCheqs
WHERE cheq_DateRec >= coalesce(nullif(@DateFrom, ' '),cheq_DateRec) AND cheq_DateRec <= coalesce(nullif(@DateTo, ' '),cheq_DateRec)
AND cheq_pername = coalesce(nullif(@Name , ' '),cheq_pername)
AND cheq_behalf = coalesce(nullif(@BehalfOf , ' '),cheq_behalf)
AND cheq_refno = coalesce(nullif(@RefNo , ' '),cheq_refno)
AND cheq_dept = coalesce(nullif(@DeptId , 0),cheq_dept)
AND cheq_dept IN (SELECT DeptID FROM Service
WHERE OfficeName = @Sdid)
END
GO

If I run the below, it returns exactly what I want:

select * from reccheqs where cheq_dept IN (SELECT DeptID FROM Service
WHERE OfficeName = 'Dungloe')

Or If I run the below, it returns exactly what I want:

CREATE PROCEDURE sproc_SearchDisplay

(
@DateFrom Datetime,
@DateTo Datetime,
@Name varchar(150),
@BehalfOf varchar(150),
@RefNo varchar(10),
@DeptId int,
@Sdid char(20)
)

AS

BEGIN
SELECT cheq_id, cheq_daterec, cheq_pername, cheq_behalf, cheq_behalf1, cheq_behalf2, cheq_amt
FROM RecCheqs
WHERE cheq_DateRec >= coalesce(nullif(@DateFrom, ' '),cheq_DateRec) AND cheq_DateRec <= coalesce(nullif(@DateTo, ' '),cheq_DateRec)
AND cheq_pername = coalesce(nullif(@Name , ' '),cheq_pername)
AND cheq_behalf = coalesce(nullif(@BehalfOf , ' '),cheq_behalf)
AND cheq_refno = coalesce(nullif(@RefNo , ' '),cheq_refno)
AND cheq_dept = coalesce(nullif(@DeptId , 0),cheq_dept)
AND cheq_dept = @Sdid
END
GO

Problem is when I combine the two It doesn't work.

Any ideas?
macca

nr
SQLTeam MVY

12543 Posts

Posted - 2008-04-17 : 08:32:58

is cheq_dept numeric?

AND cheq_dept = coalesce(nullif(@DeptId , 0),cheq_dept)
seems to imply that cheq_dept is numeric

AND cheq_dept = @Sdid
seems to imply it's character.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

macca
Posting Yak Master

146 Posts

Posted - 2008-04-17 : 08:49:10

Yes, cheq_dept is numeric, that was a typo error.

Basically what the problem is, is that the search page lets you select a number of things to search on which includes cheq_dept. But the we must use cheq_dept so that we only return the records with the cheq_dept's which have the corresponding OfficeName = @Sdid in the table service.

Is the problem maybe because I am trying to do the search referring to cheq_dept twice ?
macca

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-17 : 11:26:00

[code]CREATE PROCEDURE sproc_SearchDisplay

(
@DateFrom Datetime,
@DateTo Datetime,
@Name varchar(150),
@BehalfOf varchar(150),
@RefNo varchar(10),
@DeptId int,
@Sdid char(20)
)

AS

BEGIN
SELECT cheq_id, cheq_daterec, cheq_pername, cheq_behalf, cheq_behalf1, cheq_behalf2, cheq_amt
FROM RecCheqs r
INNER JOIN (SELECT DeptID FROM Service
WHERE OfficeName = @Sdid) t
ON t.DeptID=r.cheq_dept
WHERE cheq_DateRec >= coalesce(nullif(@DateFrom, ' '),cheq_DateRec) AND cheq_DateRec <= coalesce(nullif(@DateTo, ' '),cheq_DateRec)
AND cheq_pername = coalesce(nullif(@Name , ' '),cheq_pername)
AND cheq_behalf = coalesce(nullif(@BehalfOf , ' '),cheq_behalf)
AND cheq_refno = coalesce(nullif(@RefNo , ' '),cheq_refno)
AND cheq_dept = coalesce(nullif(@DeptId , 0),cheq_dept)
END
GO[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources