Temp table in a stored proc - 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)

Temp table in a stored proc

Author

Topic

ryanoc333
Starting Member

4 Posts

Posted - 2006-12-27 : 12:28:32

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. Thanks for any help, 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

JoeNak
Constraint Violating Yak Guru

292 Posts

Posted - 2006-12-27 : 12:31:20

You're aliasing twice. Try this...

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
WHERE NoteDateUpdate BETWEEN DATEADD(d,-@hiDay,GETDATE()) AND DATEADD(d,-@loDay,GETDATE())
order by NoteDateUpdate ASC

ryanoc333
Starting Member

4 Posts

Posted - 2006-12-27 : 12:44:34

No, still doesnt work. Thanks anyway

ryanoc333
Starting Member

4 Posts

Posted - 2006-12-27 : 12:52:42

ouch! I needed to add single quotes around my empid input parm. Now it works. I should have taken this week off!

Ryan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources