Solved

create sp and drop temp table

Posted on 2014-10-15
10
224 Views
Last Modified: 2014-10-15
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;
0
Comment
Question by:VBdotnet2005
  • 5
  • 5
10 Comments
 
LVL 65

Expert Comment

by:Jim Horn
Comment Utility
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
 

Author Comment

by:VBdotnet2005
Comment Utility
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
 

Author Comment

by:VBdotnet2005
Comment Utility
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
 
LVL 65

Accepted Solution

by:
Jim Horn earned 500 total points
Comment Utility
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
 

Author Comment

by:VBdotnet2005
Comment Utility
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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 65

Expert Comment

by:Jim Horn
Comment Utility
>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
 

Author Comment

by:VBdotnet2005
Comment Utility
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
 
LVL 65

Expert Comment

by:Jim Horn
Comment Utility
yes
0
 

Author Comment

by:VBdotnet2005
Comment Utility
Thank you Jim. I appreciate your help.
0
 
LVL 65

Expert Comment

by:Jim Horn
Comment Utility
Anytime.
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This video discusses moving either the default database or any database to a new volume.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now