Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

What is the purpose and use of "AND type in (N'P', N'PC')" in sql stored procedures?

I am new to creating stored procedures and I saw this sql below in an existing stored procedure:

IF  EXISTS (SELECT * FROM table WHERE object_id = OBJECT_ID(N'stored_proc_name') AND type in (N'P', N'PC'))
DROP PROCEDURE 'stored_proc_name' Go

After some investigating I did, I found that we are using N for any unicode characters that may be present. However, I am not sure why we are using "type in (N'P', N'PC')"?

Can anyone please explain this construct?

Also just confirm if I am correct in my thinking about the use of N here?

like image 967
NoviceMe Avatar asked Dec 13 '22 00:12

NoviceMe


2 Answers

To add in to the other answers:

You're checking whether the object [dbo].[sp_NAME] exists and is a stored procedure (P) or a assembly stored procedure (PC).

Here is an example of what we use:

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_NAME]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_NAME]
GO

Here is a similar question with answers.

For information on an assembly stored procedure.

like image 170
James Drinkard Avatar answered Feb 09 '23 01:02

James Drinkard


Yes, the N means that the strings are unicode.

What else you have is the in operator, i.e. n in (n, ...) where the first operand is the field type and the values in the parentheses are two unicode strings.

So, it has the same meaning as and (type = N'P' or type = N'PC').

like image 27
Guffa Avatar answered Feb 08 '23 23:02

Guffa