asked on

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;

Jim Horn

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

VBdotnet2005

ASKER

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

VBdotnet2005

ASKER

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

ASKER CERTIFIED SOLUTION

Jim Horn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

VBdotnet2005

ASKER

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?

Jim Horn

>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

VBdotnet2005

ASKER

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

Jim Horn

yes

VBdotnet2005

ASKER

Thank you Jim. I appreciate your help.

Jim Horn

Anytime.