create sp and drop temp table

Is this the correct way of create a store procedure?  I want to drop a temp tables everytime
when demo procedure is called.


IF OBJECT_ID('tempdb..#TEMP_1') IS NOT NULL
   DROP TABLE #TEMP_1
go
IF OBJECT_ID('tempdb..#TEMP_2') IS NOT NULL
   DROP TABLE #TEMP_2
go
IF OBJECT_ID('tempdb..#TEMP_3') IS NOT NULL
   DROP TABLE #TEMP_3
go

GO

CREATE PROCEDURE dbo.Demo

    @StartsWith nvarchar(50)

AS

BEGIN

    SET NOCOUNT ON;

 

    SELECT

        p.Name,

        OrderCount = COUNT_BIG(DISTINCT th.ReferenceOrderID)

    FROM Production.Product AS p

    JOIN Production.TransactionHistory AS th ON

        th.ProductID = p.ProductID

    WHERE

        p.Name LIKE @StartsWith + N'%'

    GROUP BY

        p.Name;

END;
VBdotnet2005Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
CREATE PROCEDURE dbo.Demo (@StartsWith nvarchar(50)) AS

SELECT
    p.Name,
    COUNT_BIG(DISTINCT th.ReferenceOrderID) as OrderCount
FROM Production.Product AS p
   JOIN Production.TransactionHistory AS th ON th.ProductID = p.ProductID
WHERE p.Name LIKE @StartsWith + '%'
GROUP BY p.Name
GO

Open in new window

1.

Significantly cleaned up the code

2.

Can lose the DROP TABLE #tmp's, as those are not used in the SP, so it's not necessary

3.

BEGIN and END is not necessary, as the SP is only one query

4.

You'll want to test the OrderCount column, as I can't test it on my end.

5.

If by 'Do I need to put Drop table first?' you mean drop procedure, then you can put the below block before the CREATE PROC
IF EXISTS(SELECT name FROM sys.procedures WHERE name='Demo') 
   DROP PROC demo
GO

Open in new window

0
VBdotnet2005Author Commented:
I want to  drop  temp tables everytime  when procedure demo is called.  Not dropping demo.
DROP TABLE #TEMP_1
DROP TABLE #TEMP_2
DROP TABLE #TEMP_13
0
VBdotnet2005Author Commented:
Like this?
CREATE PROCEDURE dbo.Demo (@StartsWith nvarchar(50)) AS

IF OBJECT_ID('tempdb..#TEMP_1') IS NOT NULL
   DROP TABLE #TEMP_1
go
IF OBJECT_ID('tempdb..#TEMP_2') IS NOT NULL
   DROP TABLE #TEMP_2
go
IF OBJECT_ID('tempdb..#TEMP_3') IS NOT NULL
   DROP TABLE #TEMP_3
go

GO

SELECT
    p.Name,
    COUNT_BIG(DISTINCT th.ReferenceOrderID) as OrderCount
FROM Production.Product AS p
   JOIN Production.TransactionHistory AS th ON th.ProductID = p.ProductID
WHERE p.Name LIKE @StartsWith + '%'
GROUP BY p.Name
GO
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
In that case you can include the IF EXISTS..DROP right after the CREATE PROC, like in the below code block.

Also, the keyword GO executes all statements as a batch, which means any parameter declare before the GO does not exist after it, so notice that there's only a GO right after DROP PROC and the end of the CREATE PROC.

The IF EXISTS .. DROP PROC is the same concept as with the temp tables, if you CREATE PROC and it already exists it'll throw an error, so the best way to handle it is to either DROP..CREATE.  There's also an ALTER PROC,  but again if the proc doesn't exist it'll throw an error.

IF EXISTS(SELECT name FROM sys.procedures WHERE name='Demo') 
   DROP PROC demo
GO

CREATE PROCEDURE dbo.Demo (@StartsWith nvarchar(50)) AS

DROP TABLE #TEMP_1
DROP TABLE #TEMP_2
DROP TABLE #TEMP_3

SELECT
    p.Name,
    COUNT_BIG(DISTINCT th.ReferenceOrderID) as OrderCount
FROM Production.Product AS p
   JOIN Production.TransactionHistory AS th ON th.ProductID = p.ProductID
WHERE p.Name LIKE @StartsWith + '%'
GROUP BY p.Name
GO

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
VBdotnet2005Author Commented:
So when sp demo is called, it will drop #temp_1, 2 & 3 first? It wouldn't keep droping Demo everytime sp Demo is called, right?
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>So when sp demo is called, it will drop #temp_1, 2 & 3 first?
correct

>It wouldn't keep droping Demo everytime sp Demo is called, right?
correct

executing the script does the IF EXISTS..DROP SP and CREATE SP
After that, executing the SP does not alter the SP, it just runs it
0
VBdotnet2005Author Commented:
Sorry, I am still new. Do I need to copy and paste the whole sample when creating sp?

IF EXISTS(SELECT name FROM sys.procedures WHERE name='Demo')
   DROP PROC demo
GO

CREATE PROCEDURE dbo.Demo (@StartsWith nvarchar(50)) AS

DROP TABLE #TEMP_1
DROP TABLE #TEMP_2
DROP TABLE #TEMP_3

SELECT
    p.Name,
    COUNT_BIG(DISTINCT th.ReferenceOrderID) as OrderCount
FROM Production.Product AS p
   JOIN Production.TransactionHistory AS th ON th.ProductID = p.ProductID
WHERE p.Name LIKE @StartsWith + '%'
GROUP BY p.Name
GO
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
yes
0
VBdotnet2005Author Commented:
Thank you Jim. I appreciate your help.
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Anytime.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.