SQL Server stored procedure meaning

Question

Hi. I'm developing a simple database architecture in VisualParadigm and lately ran over next code excerpt.

IF EXISTS (SELECT * FROM sys.objects 
WHERE object_id = OBJECT_ID(N'getType') AND type in (N'P', N'PC')) 
DROP PROCEDURE getType;

Next goes my stored procedure:

CREATE PROCEDURE getType @typeId int
AS
SELECT * FROM type t WHERE t.type_id = @typeId;

Can anyone explain what does it mean/do (the former one)?

P.S.: It would be great, if you may also check for any syntax errors as I'm totally new to SQL Server and stored procedures.

Answer 1

The IF EXISTS part first checks if a stored procedure with the same name exists. if it does it drops it before creating it. Without this check you'd get an error that the stored procedure already exists.

Answer 2

It looks like this is part of a script to generate your DB. The first statement looks to see if your sproc called "getType" exists. If it does then it will drop it. Why? Because the next line is going to create it.

The only other way it could create it and make sure it matches the current version of your procedure is to change create to alter. That would make for longer code because it would have to list the sproc twice. Or it could generate dynamic sql which is not nearly as clean.

Answer 3

It's doing a drop and recreate

if a database object called getType exists:

WHERE object_id = OBJECT_ID(N'getType')

and it's a stored procedure:

AND type in (N'P', N'PC')) 

then drop it before adding your stored procedure:

DROP PROCEDURE getType;

Answer 4

a CREATE PROCEDURE getType... will fail if the object already exists. by including the IF EXISTS... code which will drop the object if it exists first, you eliminate the error and the CREATE... will run.

The OBJECT_ID(N'getType') just returns the numeric ID of an object named N'getType' and the AND type in (N'P', N'PC')) makes sure that the object is a P=stored procedure or a PC=Assembly (CLR).

You could try using something like this, so you can use ALTER to keep permissions (DROP+CREATE removes any):

BEGIN TRY EXEC ('CREATE PROCEDURE YourProcedureName AS SELECT ''ERROR'' RETURN 999') END TRY BEGIN CATCH END CATCH
GO
ALTER PROCEDURE YourProcedureName 
AS
SELECT 'WORKS!2'
GO

EXEC YourProcedureName

OUTPUT:

-------
WORKS!2

(1 row(s) affected)

Answer 5

The first query drops procedure if it exists. The second creates a new procedure that takes integer parameter and returns resultset.

Answer 6

Adding to Raj's post, there is no means to do an "upsert" with stored procedures. The Create Procedure statement must be the first statement of the batch. Thus, the following would not work:

If Not Exists(Select 1 From sys.procedures Where Name = 'getType')
    Create Procedure...
Else
    Alter Procedure...

The only means to "update" a procedure and not have it throw an error if it already exists is to drop it and re-create it.

ADDITION

To address a specific question you made in comments, sys.objects is a catalog view which contains a list of all objects (tables, constraints, columns, indexes etc. Every "thing" in the database) of which procedures are one of them. Thus, this is checking whether the procedure object (based on filters on type) exist. The primary key of the sys.objects table/view is object_id which is an integer. In your example, they are using the OBJECT_ID function to find the id of the object getType and determine if it is a procedure. (It probably would have been safe to just use If OBJECT_ID(N'getType') is not null but just in case there is another object with that name that isn't a procedure, they added the check on the object type).