CODEWITHSUNDEEP
sqlsql-serverstored-proceduresparameterstemp-tables
Nick G
Nick G

Reputation: 1229

Simple If Statement in Stored Procedure

I am in no way a DBA and am trying to write what I perceive as a simple stored procedure. I am passing in some parameters with a value of either 0 or 1, depending on what that parameter value is I want to create a temporary table and then dump the data out. Basically, my code is as follows;

ALTER PROCEDURE [dbo].[cfn_ReportP360_Calendar_MV_and_Performance]
 @IUID int = NULL
, @USERTYPE varchar(1) = 'R'
, @RepID varchar(20) = null -- this can be vchplanid or vchhhplanid depENDs ON the @reporttype
, @iWorkListID int = 0 -- this is the P360 Group id
, @Reporttype varchar(1) = 'A' -- 'A' = acct by acct, 'H' = Household
, @debug tinyint = 0 
, @showdata tinyint = 1 -- 1 = show data, 0 = show year

AS

SET NOCOUNT ON
IF OBJECT_ID('tempdb.dbo.#tempFinal') IS NOT NULL
    DROP TABLE dbo.#tempFinal


IF @showdata = 0
Begin
CREATE table dbo.#tempFinal(
    YEAR1PERF text,
    YEAR2PERF text,
    YEAR3PERF text,
    YEAR4PERF text,
    YEAR5PERF text,
    YEAR6PERF text)

    INSERT into dbo.#tempFinal
    SELECT CONVERT(VARCHAR(8), GETDATE(), 1), '12/31/2012', '12/31/2011', '12/31/2010', '12/31/2009', '12/31/2008'
END


IF @showdata = 1
BEGIN
CREATE table dbo.#tempFinal(
    HHLEVEL_INDICATOR text,
    IPPSACCOUNTID text,
    ICFNACCOUNTID text,
    VCHACCOUNTNUMBER text,
    VCHACCOUNTNAME text,
    VCHPLANID text,
    IHHID text,
    VCHHHNAME text,
    YEAR1PERF text,
    YEAR2PERF text,
    YEAR3PERF text,
    YEAR4PERF text,
    YEAR5PERF text,
    YEAR6PERF text)

    Insert into dbo.#tempFinal
    SELECT 
        HHLEVEL_INDICATOR,IPPSACCOUNTID,ICFNACCOUNTID,VCHACCOUNTNUMBER,VCHACCOUNTNAME,VCHPLANID,IHHID,VCHHHNAME,YEAR1PERF,YEAR2PERF,YEAR3PERF,YEAR4PERF,YEAR5PERF,YEAR6PERF
    from dbo.tbl_cfn_Calendar_Performance
END
SELECT * from dbo.#tempFinal


IF OBJECT_ID('tempdb.dbo.#tempFinal') IS NOT NULL
    DROP TABLE dbo.#tempFinal

I feel like that should be good, but everytime I try to run it i get the error of; Msg 2714, Level 16, State 1, Procedure cfn_ReportP360_Calendar_MV_and_Performance, Line 47

There is already an object named '#tempFinal' in the database.

except on line 47 there is no reference to that temp table at all, it may be because comments are ignored and I have a whole bunch of comments above the code I pasted, but doesn't my checking for the table and dropping it at the end of the SPROC eliminate it? I am not sure where to go and any help would be great. Thank you in advance, NickG

Upvotes: 1

Views: 4021

Answers (2)

Sonam
Sonam

Reputation: 3466

At the first place, here I don't see any need for creating the temp table, you can directly run your select statements and get the result. I'm not sure if you are performing any operations on the data in temp table, otherwise you just don't need them.

Upvotes: 3

orgtigger
orgtigger

Reputation: 4122

Switch 

IF OBJECT_ID('tempdb.dbo.#tempFinal') IS NOT NULL

with

IF OBJECT_ID('tempdb..dbo.#tempFinal') IS NOT NULL

Upvotes: 1

Related Questions