October 29, 2008 at 8:43 am
(This isn't my actual select query, its just an example, but i still get the same error aobut there alread being a #temp. Is it something to do with an end if?
Would appreciate any help.
error
There is already an object named '#temp' in the database.
BEGIN
IF @CountryID = 1
BEGIN
SELECT ProjectID, projectname, ProjectAddress
into #temp
from project
END
ELSE
BEGIN
SELECT ProjectID, projectname, ProjectAddress
into #temp
from project
END
END
select * from #temp
October 29, 2008 at 8:55 am
debbie.coates (10/29/2008)
(This isn't my actual select query, its just an example, but i still get the same error aobut there alread being a #temp. Is it something to do with an end if?Would appreciate any help.
error
There is already an object named '#temp' in the database.
BEGIN
IF @CountryID = 1
BEGIN
SELECT ProjectID, projectname, ProjectAddress
into #temp
from project
END
ELSE
BEGIN
SELECT ProjectID, projectname, ProjectAddress
into #temp
from project
END
END
select * from #temp
You are using SELECT INTO in both parts of your IF ELSE. Looking at this example, however, makes no sense as both queries are the same. It would help if you'd post your actual code if possible, or something more representative of what you are doing.
October 29, 2008 at 9:06 am
This is my actual stored proceedure
CREATE PROCEDURE dbo.SP_ListProjectSOAs
(
@PROJECTID BIGINT
)
AS
Declare @CountryID int
set @CountryID = (Select Countryid from Project where projectid = @projectid)
BEGIN
IF @CountryID = 1
BEGIN
SELECT
PROJECT.ProjectID
, ProjectSOA.ProjectSOAID
, Ward.WardName
, ProjectSOA.SOA
, CASE
When (ProjectSOA.[SOA] = PROJECT.[PrimarySOA] AND ProjectSOA.[WardID] = PROJECT.[PrimaryWardId])
THEN '***'
When (ProjectSOA.[SOA] <> PROJECT.[PrimarySOA] AND ProjectSOA.[WardID] = PROJECT.[PrimaryWardId])
THEN '*'
else ''
end as 'Primary'
, cast(CRTScore * 100 as decimal (10,2)) AS [IMD%]
, CASE When ([EligibleCoalFieldArea] = 0) THEN 'N' ELSE 'Y' End AS Coalfield
, Case
When([CRTScore]* 100 >= 70 And [EligibleCoalFieldArea] <> 0)
THEN 'Y'
ELSE ''
End AS 'MG_Eliblible'
FROM
ProjectSOA INNER JOIN Ward ON ProjectSOA.WardID = Ward.WardID
INNER JOIN PROJECT ON ProjectSOA.ProjectID = PROJECT.ProjectID
LEFT OUTER JOIN SOA ON ProjectSOA.SOA = SOA.SOA
WHERE
ProjectSOA.ProjectID = @ProjectID
ORDER BY
[Primary] desc
END
ELSE
BEGIN
SELECT
PROJECT.ProjectID
, ProjectSOA.ProjectSOAID
, Ward.WardName
, ProjectSOA.SOA
, CASE
When (ProjectSOA.[SOA] = PROJECT.[PrimarySOA] AND ProjectSOA.[WardID] = PROJECT.[PrimaryWardId])
THEN '***'
When (ProjectSOA.[SOA] <> PROJECT.[PrimarySOA] AND ProjectSOA.[WardID] = PROJECT.[PrimaryWardId])
THEN '*'
else ''
end as 'Primary'
, cast(CRTScore * 100 as decimal (10,2)) AS [IMD%]
, 'Y' AS Coalfield
, 'Y' AS 'MG_Eliblible'
FROM
ProjectSOA INNER JOIN Ward ON ProjectSOA.WardID = Ward.WardID
INNER JOIN PROJECT ON ProjectSOA.ProjectID = PROJECT.ProjectID
LEFT OUTER JOIN SOA ON ProjectSOA.SOA = SOA.SOA
WHERE
ProjectSOA.ProjectID = @ProjectID
ORDER BY
[Primary] desc
END
END
What I want to do is put the records returned into a temp table so that i can then do some further processing of them.
October 29, 2008 at 9:11 am
At the start of your stored procedure, create your temporary table. Then use INSERT to put the records into the temp table. Do your additional processing.
October 29, 2008 at 9:16 am
If I did it this way, would I then have to define all the columns headings/size etc ?
October 29, 2008 at 9:32 am
debbie.coates (10/29/2008)
If I did it this way, would I then have to define all the columns headings/size etc ?
Yes.
CREATE TABLE #MyTable (
....
)
If you don't want to type, then on a dev server do a select into and create a permanent table. You can then generate the script of that through enterprise manager and just change the table name.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 29, 2008 at 9:38 am
Just like you would have to if creating a permanent table. Base the column types off the data you are going to load from your queries.
October 30, 2008 at 3:17 am
Many Thanks for all your help, I have created a temp table at the beginning of the stored proceedure now, which is doing the job perfect.
October 30, 2008 at 10:01 pm
I stumbled across a very tricky way to create this table in the beginning a couple months ago. Apparently it's a pretty standard VB trick, but my background is in SQL, not VB =). Basically, the concept is this:
SELECT ...(All the columns you want in your temp table)
INTO #TempTable
FROM ... (The real tables / joins you'd have for your temp table)
WHERE 1=2
This lets you very easily create your temp table with no data in it... but with the column types set. Then, you can use INSERT INTO in your IF statements.
I'm sure most of you know about that one already... but I figured I'd throw it out there for anyone that didn't.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply