Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 248
  • Last Modified:

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;
0
VBdotnet2005
Asked:
VBdotnet2005
  • 5
  • 5
1 Solution
 
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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
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
 
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

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now