Temp table in a stored proc

Question

Post reply

Temp table in a stored proc

Ryan-350179

Old Hand

Points: 311
More actions
December 27, 2006 at 9:46 am

#63026

This one is realy bugging me. I created a stored proc from a sql query that I used for testing. The testing qurey works by returning a result, but if I make a stored proc out of it, I get no results. Heres what I have. The testing sql is first...
DECLARE @DefaultDate datetime
DECLARE @ListType varchar(1)
DECLARE @EmpId varchar(8)
DECLARE @CusId varchar(8)
DECLARE @hiDay int
DECLARE @loDay int
SET @DefaultDate = '1900-01-01 00:00:00.000'
SET @EmpId = '004'
SET @CusId = '57198'
SET @hiDay = '200'--range old
SET @loDay = '0'--range new
SELECT
CusId,
EmpId,
convert(varchar,NoteDateUpdate,101) as 'NoteDateUpdate'
FROM
(
SELECT c.CusId
, n.EmpId
, MAX(CASE WHEN n.DateUpdate IS NULL THEN @DefaultDate ELSE n.DateUpdate END) as 'NoteDateUpdate'
FROM COCUS c
LEFT JOIN CONOTITM n ON c.NotId = n.NotId
WHERE n.EmpId = @EmpId
AND c.CusId = @CusId
GROUP BY c.CusId, n.EmpId
)
as temp
WHERE NoteDateUpdate BETWEEN DATEADD(d,-@hiDay,GETDATE()) AND DATEADD(d,-@loDay,GETDATE())
order by NoteDateUpdate ASC
--this wont return anything even though it should
exec PR_CRM_LastCusNote @empid=004, @cusid=57198, @hiday=200
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[PR_CRM_LastCusNote]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[PR_CRM_LastCusNote]
GO
/*****************************************************************************
* Name: PR_CRM_LastCusNote
* Purpose:
* Created: 12/27/2006 ROC
*****************************************************************************/
CREATE PROCEDURE [dbo].[PR_CRM_LastCusNote]
(
@DefaultDate datetime = '1900-01-01 00:00:00.000',
@EmpId varchar(8), -- = '004',
@CusId varchar(8), -- = '57198',
@hiDay int, -- = '200', --range old
@loDay int = '0' --range new
)
AS
SELECT
CusId,
EmpId,
convert(varchar,NoteDateUpdate,101) as 'NoteDateUpdate'
FROM
(
SELECT c.CusId
, n.EmpId
, MAX(CASE WHEN n.DateUpdate IS NULL THEN @DefaultDate ELSE n.DateUpdate END) as 'NoteDateUpdate'
FROM COCUS c
LEFT JOIN CONOTITM n ON c.NotId = n.NotId
WHERE n.EmpId = @EmpId
AND c.CusId = @CusId
GROUP BY c.CusId, n.EmpId
)
as #temp t
WHERE NoteDateUpdate BETWEEN DATEADD(d,-@hiDay,GETDATE()) AND DATEADD(d,-@loDay,GETDATE())
order by NoteDateUpdate ASC

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

David Webb-CDS SSCoach Points: 17398 More actions · Answer 1

I'd try this first:

Select the input parameters

SELECT @DefaultDate, @EmpId,@CusId,@hiDay, @loDay

so you can ensure they're being passed correctly.

Take the single quotes off the 0 in the @loDay definition

Take the # off the ' as #temp t'

And then again, I might be wrong ...
David Webb

Tom Goltl SSCertifiable Points: 5755 More actions · Answer 2

You'll also have to take the single tick off of hiday. You've got the hiday default value commented out in the procedure definition. I would also pass values to the stored procedure, even if you've got defaults declared.

Tom

Ryan-350179 Old Hand Points: 311 More actions · Answer 3

I overlooked something. Your sugestion for testing the parms helped me find out that I needed single quotes around the empid when executing the proc. Thanks very much for the help!

Ryan