asked on

SQL - Checking temp procedure whether exist

Dear Experts,

I have a temp procedure, which works fine if once started:

USE SAP
GO

CREATE PROCEDURE #usp_TempCity4 @City nvarchar(30)
AS
SELECT * 
FROM AP.LFA1
WHERE AP.LFA1.ORT01 = @City
GO

EXEC #usp_TempCity4 @City = 'Berlin'

Open in new window

But in the case of running second time, getting an error message:
There is already an object named '#usp_TempCity4' in the database

Could you please advise how that could be checked in advance and deleting the temp procedure if exist? I tried version:

USE SAP
GO

IF OBJECT_ID (N'tempdb..#usp_TempCity4', N'U') is not null
DROP PROCEDURE #usp_TempCity4
;

CREATE PROCEDURE #usp_TempCity4 @City nvarchar(30)
AS
SELECT * 
FROM AP.LFA1
WHERE AP.LFA1.ORT01 = @City
GO

EXEC #usp_TempCity4 @City = 'Berlin'

Open in new window

but that gives error message:
'CREATE/ALTER PROCEDURE' must be the first statement in a query batch.

Thanks in advance,

Vitor Montalvão

Why do you need a stored procedure to do that?
Can't you run the SELECT command directly?

SELECT * 
FROM AP.LFA1
WHERE AP.LFA1.ORT01 = 'Berlin'

Open in new window

csehz

ASKER

It is a kind of training for me in SQL, I would like to know the technical way of it.

In live examples would use this method, when having no authorization to create procedures on a certain database

Vitor Montalvão

What SQL Server version are you using?

csehz

ASKER

It is Microsoft SQL Server 2016 (SP1)

ASKER CERTIFIED SOLUTION

Vitor Montalvão

membership

This solution is only available to members.

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

Start Free Trial

csehz

ASKER

Thanks that works great

Vitor Montalvão

You're welcome.
Btw, a temporary stored procedure is something that I never saw being used in the "real world".
Even Microsoft doesn't recommend it: https://technet.microsoft.com/en-us/library/ms190669(v=sql.105).aspx
"Temporary stored procedures are useful when connecting to earlier versions of SQL Server that do not support the reuse of execution plans for Transact-SQL statements or batches. Applications connecting to SQL Server 2000 and higher should use the sp_executesql system stored procedure instead of temporary stored procedures."

csehz

ASKER

Thanks for the information.

Actually for me the 'real world' use is trying things which learnt on the SQL training, on a database which I know very well (because I am SAP specialist) but my SQL skills are not that strong yet.

Being aware that could use the AdventureWorks2012 database for such purposes, but not knowing those tables

Vitor Montalvão

Sure. For academic purposes we can do whatever we want. In fact, the best way to learn is to try everything.
Good luck with your SQL Server skill improvements.
Cheers

csehz

ASKER

Thanks Vitor, EE is a great place for such skill improvements

Jim Horn

As Vitor stated above, in the real world temporary stored procedures do not exist, so I propose adjusting your training to reflect that. Temporary tables within a stored procedure, yes. Temporary stored procedures, no.

btw check out SQL Server Training for some useful training resources.