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?
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.
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')
.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With