Trouble after Alter procedure - 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

Transact-SQL (2000)

Trouble after Alter procedure

Author

Topic

hornet
Yak Posting Veteran

96 Posts

Posted - 2006-04-20 : 04:57:32

Hi! I have next trouble. I had had the procedure. I 've made ALTER it.
After that its'nt working. It isn't returning recordset. I've watched procedure and didn't find any errors. What is this may be? Thanks a lot. I had changed only WHERE. Here is code:
ALTER PROCEDURE prReportGetDataForDiningRoom (
@ReportDate SMALLDATETIME,
@Building NVARCHAR(8))
AS

--SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SET NOCOUNT ON

--BEGIN TRAN
DECLARE @sqlSelect NVARCHAR(4000)

IF LEN(LTRIM(RTRIM(@Building))) = 0
SET @Building = ' '

SET @sqlSelect = N'SELECT DateIn, DateOut, RealDateIn, RealDateOut, NumberRoom, Building, FullName, ShortName,
HotelArrivalTimeID, HotelLeaveTimeID
FROM vReportAllPermits
WHERE DATEDIFF(day, DateIn, @ReportDate) >= 0 AND DATEDIFF(day, DateOut, @ReportDate) <= 0 AND State IN(1, 2) '

IF LTRIM(RTRIM(UPPER(@Building))) NOT LIKE 'ÂÑÅ'
SET @sqlSelect = @sqlSelect + ' AND Building = @Building '

SET @sqlSelect = @sqlSelect + ' ORDER BY HotelArrivalTimeID, FullName '

DECLARE @strParams NVARCHAR(2048)
SET @strParams = N' @ReportDate SMALLDATETIME,
@Building NVARCHAR(8) '

EXEC sp_executesql @sqlSelect, @strParams, @ReportDate, @Building

--COMMIT TRAN

RETURN 0
GO

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-04-20 : 05:00:34

no records matching your where criteria perhaps

try running those dsql in QA first and see if they return anything...

divide and conquer

--------------------
keeping it simple...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-20 : 05:17:17

>>DATEDIFF(day, DateIn, @ReportDate) >= 0 AND DATEDIFF(day, DateOut, @ReportDate) <= 0

What do you want to do with that condition?
Post some sample data and the result you want

Madhivanan

Failing to plan is Planning to fail

hornet
Yak Posting Veteran

96 Posts

Posted - 2006-04-20 : 05:38:27

Here is 'TextData' from Profiler:
exec prReportGetDataForDiningRoom 'Apr 20 2006 12:00AM', N'Âñå'

Kristen
Test

22859 Posts

Posted - 2006-04-20 : 08:54:46

Not sure why you are using dynamic SQL for this, as it seems the only reason is to conditionally add

IF LTRIM(RTRIM(UPPER(@Building))) NOT LIKE 'ÂÑÅ'
SET @sqlSelect = @sqlSelect + ' AND Building = @Building '

which you could just add to your WHERE clause - i.e. using something like:

SELECT DateIn, DateOut, RealDateIn, RealDateOut, NumberRoom, Building, FullName, ShortName,
HotelArrivalTimeID, HotelLeaveTimeID
FROM vReportAllPermits
WHERE DATEDIFF(day, DateIn, @ReportDate) >= 0 AND DATEDIFF(day, DateOut, @ReportDate) <= 0 AND State IN(1, 2)
AND (LTRIM(RTRIM(UPPER(@Building))) LIKE 'ÂÑÅ' OR Building = @Building)
ORDER BY HotelArrivalTimeID, FullName

Kristen

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-04-20 : 09:00:12

After the LIKE isn't the wild card (% or _ or [] or [ ^ ]) needed ?
eg

NOT LIKE '%ÂÑÅ%'

Srinika

Kristen
Test

22859 Posts

Posted - 2006-04-20 : 09:03:04

I agree; I didn't mention it 'coz I didn't know what those accented characters actually originated as before they were cut&pasted in here!

Kristen

hornet
Yak Posting Veteran

96 Posts

Posted - 2006-04-21 : 07:55:37

Thanks! I troubleshooted. I've remove GO at end and its' working!
I don't know why it is?