Stored Proc returns no data

[tonykblen]

Hi,

I have the following stored proc:

CREATE PROCEDURE dbo.sp_Stock_Report_WH1
@DayOfStockTake1 datetime,
@DayOfStockTake2 datetime
AS

select * from stocktran
where stx_warehouse = 'WH1'
and
stx_document >
(select max(stx_document) from stocktran
where stx_trandate = @DayOfStockTake1)
and
stx_document <=
(select max(stx_document) from stocktran
where stx_trandate = @DayOfStockTake2)

When I call it with the following, I get no data:

sp_Stock_Report_WH1 '2003-04-04 00:00:00.000','2003-04-11 00:00:00.000'

I know there is data in the table that should appear. The syntax checks out okay in query analyser. Does anyone know if the above should be okay?

Many thanks for your help,
Tony.

 

[SQLSister]

Run each of these statements separately and see what result you get (fill in the variables by hand temporarily)

Select max(stx_document) from stocktran
where stx_trandate = '2003-04-04 00:00:00.000'

select max(stx_document) from stocktran
where stx_trandate = '2003-04-11 00:00:00.000'

if the result is that the first one is greater than the last one, then your statment won't work.

If those two results are OK, plug the results you get into the main statement instead of the subqueries and see what result you get.

By the way it is a bad practice to select using *. Specifiy your columns, your queries will run more efficiently if you only get the columns you need instead of every column.

 

[ClaireHCM]

Are you sure the max of stx_document which match '2003-04-04
is smaller than the max of stx_document which match '2003-04-11

why dont you provide little sample ?

 

[tonykblen]

Hi,

Many thanks for your help.

Results of

Select max(stx_document) from stocktran
where stx_trandate = '2003-04-04 00:00:00.000'

94796

select max(stx_document) from stocktran
where stx_trandate = '2003-04-11 00:00:00.000'

96241

Plugged it into the query analyser and got 668 rows back.

I did add another condition so the stored proc now looks as follows:

CREATE PROCEDURE dbo.sp_Stock_Report_WH1
@DayOfStockTake1 datetime,
@DayOfStockTake2 datetime
AS

select * from stocktran
where stx_warehouse = 'WH1'
and
stx_document >
(select max(stx_document) from stocktran
where stx_trandate = @DayOfStockTake1
and stx_user = 'BLENDERS\Administrator')
and
stx_document <=
(select max(stx_document) from stocktran
where stx_trandate = @DayOfStockTake2
and stx_user = 'BLENDERS\Administrator')

The numbers seem to be okay.

 

[ClaireHCM]

I dont see anything wrong in your query.I can only guess the problem maybe in table instead of query.The query you ran may not contain any value.It's hard to say the real reason,because we dont know how the table look like

Maybe check the table again.

did you run only this part in query analyzer?

select * from stocktran
where stx_warehouse = 'WH1'
and
stx_document >
(select max(stx_document) from stocktran
where stx_trandate = @DayOfStockTake1
and stx_user = 'BLENDERS\Administrator')
and
stx_document <=
(select max(stx_document) from stocktran
where stx_trandate = @DayOfStockTake2
and stx_user = 'BLENDERS\Administrator')

 

[cathyw]

Hi Tony,

Try inserting this line in your stored procedure right before your select statement.
SET NOCOUNT ON
That will suppress the returning of the number of rows, which might be interfering with the returning of the result set.

Hope this helps,
Cathy

 

[tonykblen]

Hi,

Again, many thanks for all your help.

Cathy, that statement did not help matters. Still getting an empty dataset.

This one really baffles me!

Is there any way I can extract what the SQL that is actually ran against the database is and try running that directly in query analyser?

Tony.

 

[tonykblen]

Hi again,

Changed the parameters to take a date only and it works fine now.

Many thanks for all of your help.

Tony.