SQL Syntax Explantion Needed

Posted on 2009-12-25
Last Modified: 2012-05-08
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]
Question by:rwheeler23
    LVL 142

    Assisted Solution

    by:Guy Hengel [angelIII / a3]
    N'xxxx' is the same as CAST( 'xxxxx'  as nvarchar )

    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    for the possible values of type and xtype, RTFM:
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    actually, for sql 2005+, sys.objects :
    LVL 51

    Accepted Solution

    Best to use : 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 :

    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')

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
    Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
    In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…
    This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor ( If you're looking for how to monitor bandwidth using netflow or packet s…

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now