Calling Stored Procedure Using Date Variables

[FontanaS]

I Have A Stored Procedure That Looks For Records Based On Three Inputed Variables - BeginDate, EndDate, Filename.

When Calling That Stored Procedure In The Query,
Exec Sp-Obtain_TravelDates '08/01/02','08/01/02','gel0801a'
It Hangs.

I Know That The Dates And Filename Are Good.

When I Substitute In The Stored Procedure The BeginDate Or EndDate Variable For '08/01/02' The Query Works.

But For Some Reason, When I Have Both Dates As Variables, The Query Hangs And I Locks Up And I Have To Use Ctrl, Alt, Del To Close SQL Server.

Any Ideas Why?

 

[jhonty]

How are you using BeginDate, EndDate variable in the query?

try out with

<Date_field> between @BeginDate and @EndDate

else

<Date_field> >= @BeginDate
and <Date_field> <= @EndDate

 

[FontanaS]

I Have A Stored Procedure That Looks For Records Based On
Three Inputed Variables - BeginDate, EndDate, Filename.

When Calling That Stored Procedure In The Query,
Exec Sp-Obtain_TravelDates '08/01/02','08/01/02','gel0801a'
It Hangs.

I Know That The Dates And Filename Are Good.

When I Substitute In The Stored Procedure The BeginDate Or
EndDate Variable For '08/01/02' The Query Works.

But For Some Reason, When I Have Both Dates As Variables,
The Query Hangs And I Locks Up And I Have To Use Ctrl,
Alt, Del To Close SQL Server.

Any Ideas Why?

SP -
CREATE PROCEDURE sp_obtain_traveldates ( @begindate VARCHAR
(8) , @enddate VARCHAR(8) , @filename VARCHAR(12) )

AS
BEGIN TRANSACTION
SELECT TCTRLNBR.CTRLNBR, MIN(TVTRIP.DEPARTUREDATE)
AS BEGINTRAVEL, MAX(TVTRIP.RETURNDATE) AS ENDTRAVEL
INTO ##Voucher_tmp
FROM TCTRLNBR FULL OUTER JOIN TVTRIP ON TRVORDER =
AUTHNUMBER
WHERE TCTRLNBR.CTRLNBR IN
(SELECT CTRLNBR FROM TVOUCHER WHERE FROMDATE =
@begindate AND TODATE = @enddate AND TMEXTFILENAME
LIKE '%'+@filename +'%')
GROUP BY TCTRLNBR.CTRLNBR,TVTRIP.AUTHNUMBER
ORDER BY TVTRIP.AUTHNUMBER
RAISERROR (' %d Row(s) Inserted Into Temporary
Table ##Voucher_Tmp', -1, -1, @@rowcount)
COMMIT TRANSACTION
GO

OUTCOME - When The Query Finishes It Will Le Me Know How
Many Records Were Selected Into The Temp Table. Then When
I Select * From The Temp Table I Should See A List Of
Records Between The Dates Specified, And From The Filename
Specified.

 

[JMCraig]

In looking at your stored procedure, I'm puzzled by a number of things:

Stylistically, I'd suggest that you put reserved words in lower case if you use upper case for indentifiers; it's a whole lot easier to read, IMHO. Personally, I prefer lower case identifiers--not that it makes much difference.

##Voucher_Tmp wouldn't appear to be a valid name for a temp table--did you mean #Voucher_Tmp?

When you say &quot;it hangs&quot; do you know what it's actually doing? Can you make another connection to the server (via a different session in a SQL tool) and run sp_who to see what's going on with the process? How big are the tables involved? Some of the things in your query can cause the optimizer to choose an inappropriate query plan (e.g. the use of MIN and MAX in one select statement's list, the use of the subquery when it appears it could be added as part of the join--although without a clear understanding of the data, that may not work given your use of FULL OUTER JOIN--but a full outer join can sure lead to some table scans).

So, here's what I'd suggest:
1. Use SET SHOWPLAN ON with SET NOEXEC ON and run the query with some test values to see the query plan.

Use DECLARE with the variable names to substitute for the declarations in the parameter list of the proc; use SELECT to assign values to your local variables. Are you scanning some big tables? Perhaps you need some indexes?

Use SET NOEXEC OFF followed by SET SHOWPLAN OFF when you're done.
2. When it's hung, make another connection (ideally log in as a different user so you won't confuse the two sessions) and run sp_who and see if you can see what's happening (a sleeping SELECT command perhaps--that could indicate some heavy table scanning).

To verify that the select into is running and not actually hung, you can examine the locks for the spid that's running the proc. The easiest way to do this, I find is this:

use <your database>
go
select object_name (id), page, class
from master..syslocks
where spid = <the spid running the proc>
and dbid = db_id('<your db name>')
/* this last condition will exclude the temp table which
could be confusing since it's name won't translate
properly via object_name as you're using a different DB */
go

Run the select repeatedly; if the page value changes, then it's definitely running, it just may take a good long while to finish! Also, if you only get one lock on a table, use the values of the class column in the Admin Guide or the Reference Manual to see if the lock in question is a table lock--unlikely in this situation.

BOL,

John J M Craig
Alpha-G Consulting, LLC

http://alphagconsulting.bizhosting.com

nsjmcraig@netscape.net