This is the darndest thing I've ever seen.
I created a proc from a template that uses SYSNAME
as the parameter ty开发者_如何学运维pes. All portions of the proc that took the name from the parameter are throwing errors. Here is a sample:
IF EXISTS(select 1 from sysobjects where name=N'dbo.ms_lst_partner_break_types' and xtype='p')
BEGIN
PRINT 'DROP PROCEDURE dbo.ms_lst_partner_break_types'
DROP PROCEDURE dbo.ms_lst_partner_break_types
END
Here is the error:
Msg 102, Level 15, State 1, Line 4 Incorrect syntax near '_partner_break_types'.
The weirdest thing is that when I double click on dbo.ms_lst_partner_break_types
SSMS highlights either ms_lst
or _partner_break_types
depending on where I click. Copy the script into Textpad and back, same problem. Remove _partner_break_types
and suddenly it works.
Does anyone have any idea what gives?
I don't know why it happened, but Unicode character 0x1f was inserted into the script for some reason. It might be a bug in SSMS, but I don't think it's going to be answered that easily.
In SQL Server 2008, it's sys.objects
. Also, the field to look for the "name" is different, as well as other general syntax:
IF EXISTS (SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[my_proc]')
AND type in (N'P', N'PC'))
The easiest thing to do is to right-click on the SP and select "script as drop to new query window" via the context menu heirarchy.
精彩评论