Date As Variable In SP Call

[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?

 

[fluteplr]

post the procedure

 

[FontanaS]

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

 

[FontanaS]

When I Have Deleted The BeginDate Variable Altogether And Ran It With Just The Filename And EndDate Variable, It Still Hangs.

I Think It Might Have Something To Do With The Dates?

 

[tlbroadbent]

Are other users accessing the same procedure? Have you monitored the Server for blocking conditions? Which version of SQL Server are you using? Prior to SQL 2000, there were problems associated with blocking and SELECT INTO statements that create temp tables. Tempdb could be locked.

Where is the global temporary table used? When and where is it deleted? Do you have indexes on tables? How big are the tables? How many rows are usually inserted by this process?

The SP seems unusual to me. How excatly is this SP used? Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.