[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 261
  • 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

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

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