Temp Tables and Table Variables
-
Hi,
I've come across a bizarre problem that I can't figure out. The key points are:
a) In the prGetList SP I use a table variable to retrieve data from the DB
b) I need to identify the number of unique occurrences of a specific field in the returned data
c) I populate a temp table with the table variable in order to be able to access it from prCappedQuery SP to which I pass the temp table name
Points a) and c) are legacy code and working fine. Point b) is the new requirement which I cannot seem to get to work.
Any suggestions would be greatly appreciated.
Thanks
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[prGetList]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[prGetList]
GO
CREATE PROCEDURE dbo.prGetList
AS
Declare @CourseCount int
Declare @EventOpsList TABLE (
Event_ID int,
Course_ID int)
/* Retrieve data for collection - SIMPLIFIED FOR THIS EXAMPLE */
INSERT INTO @EventOpsList(Event_ID, Course_ID)
SELECT A.Event_ID, A.Course_ID
FROM dbo.Event A --***** THIS IS THE LINE THE ERROR MSG REFERS TO ??? *****
INNER JOIN dbo.Course B ON A.Course_ID = B.Course_ID
SELECT *
INTO #EventOpsList2
FROM @EventOpsList
/* Retrieve distinct Course count */
SELECT @CourseCount = COUNT(DISTINCT(Course_ID) FROM #EventOpsList2
/* Then call SP that returns datasets for ObjectModel */
--EXEC prCappedQuery '[#EventOpsList2] T'
DROP TABLE #EventOpsList2
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
-
What error message do you get?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
-
I get the following error message when I try and create the SP:
Server: Msg 156, Level 15, State 1, Procedure prGetList, Line 21
Incorrect syntax near the keyword 'FROM'.
-
OK,
below I am attaching a full sample script that also creates and populates the tables. It continues to give me the same error. Is no one else seeing this error?
As far as your comments regarding "select into #temp from @a", it is used for separate functionality from the count logic I want to implement. The existing code retrieves data into the table variable @a, but it then needs to call another SP to access that data. It therefore populates the temp table #temp and passes the name of the temp table to the other SP.
WRT to my reqmt, I have also tried to select from the @a table variable, but that also returns the same error message.
Thanks for your help
******************************************************************************USE TESTDB
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Course]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Course]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Event]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Event]
GO
CREATE TABLE [dbo].[Course] (
[Course_ID] [int] IDENTITY (1, 1) NOT NULL ,
[CourseCode] [varchar] (10) COLLATE SQL_Latin1_General_CP850_CI_AS NOT NULL ,
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Event] (
[Event_ID] [int] IDENTITY (1, 1) NOT NULL ,
[Course_ID] [int] NOT NULL ,
) ON [PRIMARY]
GO
SET IDENTITY_INSERT Course ON
INSERT INTO [Course] (Course_ID, CourseCode) VALUES (1, 'TugIMVstd')
INSERT INTO [Course] (Course_ID, CourseCode) VALUES (2, 'RTG16Wheel')
INSERT INTO [Course] (Course_ID, CourseCode) VALUES (3, 'InductionO')
INSERT INTO [Course] (Course_ID, CourseCode) VALUES (4, 'InductionA')
INSERT INTO [Course] (Course_ID, CourseCode) VALUES (5, 'Presentatn')
INSERT INTO [Course] (Course_ID, CourseCode) VALUES (6, 'TugRORO')
INSERT INTO [Course] (Course_ID, CourseCode) VALUES (7, 'Van Carrie')
SET IDENTITY_INSERT Course OFF
GO
SET IDENTITY_INSERT Event ON
INSERT INTO [Event] (Event_ID, Course_ID) VALUES (1, 1)
INSERT INTO [Event] (Event_ID, Course_ID) VALUES (2, 2)
INSERT INTO [Event] (Event_ID, Course_ID) VALUES (3, 3)
INSERT INTO [Event] (Event_ID, Course_ID) VALUES (4, 3)
INSERT INTO [Event] (Event_ID, Course_ID) VALUES (5, 4)
INSERT INTO [Event] (Event_ID, Course_ID) VALUES (6, 6)
INSERT INTO [Event] (Event_ID, Course_ID) VALUES (7, 5)
SET IDENTITY_INSERT Event OFF
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[prGetList]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[prGetList]
GO
CREATE PROCEDURE dbo.prGetList
AS
Declare @CourseCount int
Declare @EventOpsList TABLE (
Event_ID int,
Course_ID int)
/* Retrieve data for collection */
INSERT INTO @EventOpsList(Event_ID, Course_ID)
SELECT A.Event_ID, A.Course_ID
FROM dbo.Event A --***** THIS IS THE LINE THE ERROR MSG REFERS TO ??? *****
INNER JOIN dbo.Course B ON A.Course_ID = B.Course_ID
SELECT *
INTO #EventOpsList2
FROM @EventOpsList
/* Retrieve distinct Course count - BOTH STMTS BELOW GIVE THE SAME ERROR MSG*/
--SELECT @CourseCount = COUNT(DISTINCT(Course_ID) FROM @EventOpsList
--SELECT @CourseCount = COUNT(DISTINCT(Course_ID) FROM #EventOpsList2
/* Then call SP that accesses data in Temp Table */
--EXEC prCappedQuery '[#EventOpsList2] T'
DROP TABLE #EventOpsList2
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
-
Works on my pc. Have you tried dropping the proc and recreating it?.
What version of sql server do you have, what's the compatibility level?
-
Truly bizarre. I am running SQL 2000 and have set the compatibility level 80.
Just to clarify, the script I posted was run on a new TestDB that I've just created. As a result before I ran the script there was nothing in the DB. I have also just tried changing the collation used in Course table to the database default, but still get the same error.
Help
-
Is this still the simplified version or the actual code?
/* Retrieve data for collection - SIMPLIFIED FOR THIS EXAMPLE */
INSERT INTO @EventOpsList(Event_ID, Course_ID)
SELECT A.Event_ID, A.Course_ID
FROM dbo.Event A --***** THIS IS THE LINE THE ERROR MSG REFERS TO ??? *****
INNER JOIN dbo.Course B ON A.Course_ID = B.Course_ID
-
I first noticed the error in a SP which contains a complex INSERT with the SELECT having many JOINS. So I then tried a cut down version on the two tables I sent in the sample script, but where I keep getting the problem.
It's important to underline that the sample script I posted has been run on a NEW TestDB where I have run nothing but the sample scripts.....
-
Can you paste the original failing query here? Maybe I'll see something else.
-
Just want to point out that there are extra Parenthesis here and should be removed
:--SELECT @CourseCount = COUNT(DISTINCT(Course_ID) FROM @EventOpsList
--SELECT @CourseCount = COUNT(DISTINCT(Course_ID) FROM #EventOpsList2
* Noel -
Haaaaaaaaaa, there's your error.
May I suggest that you post the FAILING code next time so that we don't search for nothing.
Thanx Noeld for spotting that one.
-
Here it is. I'm kind of hoping you won't also ask for all the related table and data scripts....
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[prGetEventDevtList]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[prGetEventDevtList]
GO
------------------------------------------------------------------------------------------------------------------------------------------------------------
-- Purpose : Retrieve dataset of Development Events to display in
-- Training - Event Devt filtered grid
--
-- Assumptions : Reqmt is that date range is always passed in, but SP
-- will be able to handle no date range
--
-- Inputs : @CourseType_ID
-- @StartDate
-- @EndDate
-- @CourseCount OUTPUT,
-- @Filter (OPTIONAL)
-- @OrderBy (OPTIONAL)
-- @Cap (OPTIONAL)
--
-- Outputs : @CourseCount
------------------------------------------------------------------------------------------------------------------------------------------------------------
/**********************************************************************
* Change history
* Date Author Ref Details
***********************************************************************/
CREATE PROCEDURE dbo.prGetEventDevtList
(
@CourseType_ID int, --This avoids havign to hard-code it in SP
@CourseCount int OUT,
@StartDate smalldatetime = '1/1/1900',
@EndDate smalldatetime = '1/1/2078',
@Filter Text = '',
@OrderBy Text = '',
@Cap int=0
)
AS
Declare @EventDevtList TABLE (
Event_ID int,
Course_ID int,
CourseName varchar (100),
Provider varchar (255),
PrimaryTrainerClockNo int,
PrimaryTrainerName varchar(255),
MinimumAttendees int,
ActualAttendees int,
Spaces int,
StartDate smalldatetime,
EndDate smalldatetime,
Duration int,
Venue varchar(100),
AvgRating decimal(4,2),
TrainingAuthForm bit,
JoiningInst bit,
AttendanceConfirm bit,
Cost decimal(10,2),
Recharged varchar(255),
RechargeValue decimal(10,2),
Invoiced bit,
InvoiceNumber varchar(255),
CourseContent varchar (500),
EventStatus varchar(100))
/* Initialise NULL dates */
SET @StartDate = IsNull(@StartDate,CAST('1/1/1900' as smalldatetime))
SET @EndDate = IsNull(@EndDate,CAST('1/1/3000' as smalldatetime))
/* Retrieve data for collection */
INSERT INTO @EventDevtList(Event_ID, Course_ID, CourseName, Provider, PrimaryTrainerClockNo, PrimaryTrainerName, MinimumAttendees, ActualAttendees, Spaces,
StartDate, EndDate, Duration, Venue, AvgRating, TrainingAuthForm, JoiningInst, AttendanceConfirm, Cost,
Recharged, RechargeValue, Invoiced, InvoiceNumber, CourseContent, EventStatus)
SELECT A.Event_ID, A.Course_ID, B.[Name], C.[Name], U.Employee_ID, [dbo].[fnGetEmployeeNameByID](U.Employee_ID, T.Trainer_ID),
A.MinimumAttendees, 0, 0, A.StartDate, A.EndDate, A.Duration, D.[Name], 0, 0, 0, 0, A.Cost,
A.Recharged, A.RechargeValue, A.Invoiced, A.InvoiceNumber, dbo.fnListCourseKeywords(A.Course_ID), E.[Name]
FROM dbo.Event A
INNER JOIN dbo.Course B ON A.Course_ID = B.Course_ID
INNER JOIN dbo.CourseProvider C ON A.CourseProvider_ID = C.CourseProvider_ID
INNER JOIN dbo.Venue D ON A.Venue_ID = D.Venue_ID
INNER JOIN dbo.EventStatus E ON A.EventStatus_ID = E.EventStatus_ID
LEFT JOIN dbo.Trainer_Event T ON A.Event_ID = T.Event_ID AND T.PrimaryTrainer = 1
LEFT JOIN dbo.Trainer U ON T.Trainer_ID = U.Trainer_ID
LEFT JOIN dbo.Employee V ON U.Employee_ID = V.Employee_ID
WHERE B.CourseType_ID = @CourseType_ID
AND (A.StartDate <= @EndDate)
--AND (A.StartDate >= @StartDate AND A.EndDate <= @EndDate)
--OR (A.StartDate < @StartDate)
--OR (A.StartDate > @StartDate)
ORDER BY A.StartDate
SELECT *
INTO #EventDevtList2
FROM @EventDevtList
/* Retrieve distinct Course count */
SELECT @CourseCount = COUNT(DISTINCT(Course_ID) FROM #EventOpsList2
/* Then call SP that returns datasets for ObjectModel */
EXEC prCappedQuery '[#EventDevtList2] T', @Filter, @OrderBy, @Cap
DROP TABLE #EventDevtList2
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
GRANT EXECUTE ON [dbo].[prGetEventDevtList] TO [LmsUsers]
GO
-
Did you read my post ?
* Noel -
As Noeld said and I confirmed, this should read like this :
/* Retrieve distinct Course count */
SELECT @CourseCount = COUNT(DISTINCT Course_ID) FROM #EventOpsList2
Anyone here would have spotted the error in 2 seconds if you had given the problem code first, not the code you think is failing. I'm not trying to rant on you but that's just it. If we don't have the good/all information we can't help you right and we have to guess which can take very long.
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply