Plz帮我在这个存储过程中找到错误 [英] Plz help me to find error in this stored procedure
本文介绍了Plz帮我在这个存储过程中找到错误的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
CREATE PROCEDURE PackageMaster
AS
CREATE TABLE tbl_PackageMaster
(
Pkg_ID INT PRIMARY KEY NOT NULL ,
Pkg_Internal_ID INT IDENTITY ( 1 , 1 ) ,
Pkg_Name NVARCHAR (MAX),
Pkg_description VARCHAR (MAX),
Pkg_Image1 VARCHAR (MAX),
Pkg_Adv_Book_Days INT ,
Pkg_time_from < span class =code-keyword> DATETIME ,
Pkg_t ime_to DATETIME ,
Pkg_basic_guest_no INT ,
Pkg_hours_included INT ,
Pkg_Distance_Covered INT
)
解决方案
该存储过程中没有错误,但是如果多次运行它将导致运行时错误引用:Msg 2714,Level 16,State 6,Procedure PackageMaster,Line 3
数据库中已经有一个名为'tbl_PackageMaster'的对象。
您需要先查看表格是否已存在...例如如果你想删除它,如果它已经存在你可以使用创建PROCEDURE PackageMaster
AS
BEGIN
IF(EXISTS(SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE - TABLE_SCHEMA ='YourSchema'和
TABLE_NAME ='tbl_PackageMaster'))
DROP TABLE tbl_PackageMaster
CREATE TABLE tbl_PackageMaster
(
Pkg_ID INT PRIMARY KEY NOT NULL,
Pkg_Internal_ID INT IDENTITY(1,1),
Pkg_Name NVARCHAR(MAX),
Pkg_description VARCHAR(MAX),
Pkg_Image1 VARCHAR(MAX) ,
Pkg_Adv_Book_Days INT,
Pkg_time_from DATETIME,
Pkg_time_to DATETIME,
Pkg_basic_guest_no INT,
Pkg_hours_included INT,
Pkg_Distance_Covered INT
)
END或者如果您只想创建它,如果它不存在,您可以使用
CREATE PROCEDURE PackageMaster
AS
BEGIN
IF(不是EXISTS(从INFORMA中选择*
) TION_SCHEMA.TABLES
WHERE - TABLE_SCHEMA ='YourSchema'和
TABLE_NAME ='tbl_PackageMaster'))
CREATE TABLE tbl_PackageMaster
(
Pkg_ID INT PRIMARY KEY NOT NULL,
Pkg_Internal_ID INT IDENTITY(1,1),
Pkg_Name NVARCHAR(MAX),
Pkg_description VARCHAR(MAX),
Pkg_Image1 VARCHAR(MAX),
Pkg_Adv_Book_Days INT,
Pkg_time_from DATETIME,
Pkg_time_to DATETIME,
Pkg_basic_guest_no INT,
Pkg_hours_included INT,
Pkg_Distance_Covered INT
)
END
CREATE PROCEDURE PackageMaster
AS
CREATE TABLE tbl_PackageMaster
(
Pkg_ID INT PRIMARY KEY NOT NULL,
Pkg_Internal_ID INT IDENTITY(1,1),
Pkg_Name NVARCHAR(MAX),
Pkg_description VARCHAR(MAX),
Pkg_Image1 VARCHAR(MAX),
Pkg_Adv_Book_Days INT,
Pkg_time_from DATETIME,
Pkg_time_to DATETIME,
Pkg_basic_guest_no INT,
Pkg_hours_included INT,
Pkg_Distance_Covered INT
)
解决方案
There is no error in that stored procedure, however if you run it more than once you will get a runtime errorQuote:Msg 2714, Level 16, State 6, Procedure PackageMaster, Line 3
There is already an object named 'tbl_PackageMaster' in the database.
You need to see if the table already exists first .. e.g. if you want to drop it if it already exists you could usecreate PROCEDURE PackageMaster AS BEGIN IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE -- TABLE_SCHEMA = 'YourSchema' and TABLE_NAME = 'tbl_PackageMaster')) DROP TABLE tbl_PackageMaster CREATE TABLE tbl_PackageMaster ( Pkg_ID INT PRIMARY KEY NOT NULL, Pkg_Internal_ID INT IDENTITY(1,1), Pkg_Name NVARCHAR(MAX), Pkg_description VARCHAR(MAX), Pkg_Image1 VARCHAR(MAX), Pkg_Adv_Book_Days INT, Pkg_time_from DATETIME, Pkg_time_to DATETIME, Pkg_basic_guest_no INT, Pkg_hours_included INT, Pkg_Distance_Covered INT ) ENDor if you only want to create it if it doesn't exist you could use
CREATE PROCEDURE PackageMaster AS BEGIN IF (NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE -- TABLE_SCHEMA = 'YourSchema' and TABLE_NAME = 'tbl_PackageMaster')) CREATE TABLE tbl_PackageMaster ( Pkg_ID INT PRIMARY KEY NOT NULL, Pkg_Internal_ID INT IDENTITY(1,1), Pkg_Name NVARCHAR(MAX), Pkg_description VARCHAR(MAX), Pkg_Image1 VARCHAR(MAX), Pkg_Adv_Book_Days INT, Pkg_time_from DATETIME, Pkg_time_to DATETIME, Pkg_basic_guest_no INT, Pkg_hours_included INT, Pkg_Distance_Covered INT ) END
这篇关于Plz帮我在这个存储过程中找到错误的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文