• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 263
  • Last Modified:

SQL Syntax Explantion Needed

I know what thisstatement does below but can someone explain two things?

1) What does the N' do in this expression? Is it necessary? What is its purpose?
2) In the Type function, what are valid arguments? Does P mean stored procedure? What does PC mean? What are the other values? Is there documentation someplace that details all of this?

/****** Object:  StoredProcedure [dbo].[glCalcPMBeginningBalance]    Script Date: 12/25/2009 12:43:53 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[glCalcPMBeginningBalance]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[glCalcPMBeginningBalance]
GO
0
rwheeler23
Asked:
rwheeler23
  • 3
2 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
N'xxxx' is the same as CAST( 'xxxxx'  as nvarchar )

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
for the possible values of type and xtype, RTFM:
http://msdn.microsoft.com/en-us/library/aa260447%28SQL.80%29.aspx
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
actually, for sql 2005+, sys.objects :
http://msdn.microsoft.com/en-us/library/ms190324.aspx
0
 
Mark WillsTopic AdvisorCommented:
Best to use : http://msdn.microsoft.com/en-us/library/ms190324.aspx it is the 2008 link and applies to 2005 as AngelIII says above. It does show you the list of types.

Also, the sys.objects view itself has the meaning of the values for [type] in [type_desc]

Now, literals will be autoconverted as needed by the datatype of the column in the comparison. So a lot of times you do not need to "specify" the data type of the literal. That is what the N'P' is doing - it is saying read this string as a unicode string.

The "N" prefix stands for National Language in the SQL-92 standard, without it, SQL Server will convert it to the non-Unicode code page of the current database before it uses the string. For "standard" english databases, it is often unnecessary to do it - SQL will do the conversion automatically.

There is a full explanation given here : http://support.microsoft.com/kb/239530

The irony of it is that it is totally unnecessary for the [type] column because that column is CHAR(2) and not unicode at all, so would be better as :

AND type in ('P', 'PC')
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now