在过程中使用动态SQL创建模式和表 [英] Using dynamic sql in a procedure to create schemas and tables

问题描述

我的任务是创建一个示例数据库，用于存储来自我大学15个校区的数据。每个园区的数据必须与其余园区分开（使用模式），并且每个模式必须具有相同的表和列。这是必须使用动态sql的地方（如作业中所述）。

I've been tasked with creating a sample database that stores data from my college's 15 campuses. The data from each campus must be separate from the rest (with the use of schemas) and each schema must have the same tables and columns. This is where dynamic sql has to be used (as stated in the assignment).

以下代码段演示了我的努力（请记住，我仍然是新手。

The following code snippet demonstrates my efforts (bare in mind that I'm still new to this):

USE master
GO
CREATE DATABASE CTUDB
GO
USE CTUDB
GO

CREATE PROCEDURE AddCampus_proc(@campus varchar(50))
AS
DECLARE @DynamicSQL varchar(MAX)
BEGIN
SET @DynamicSQL = 'CREATE schema ['+@campus+']'
EXEC (@DynamicSQL)

SET @DynamicSQL = 'CREATE table ['+@campus+'].Student_tbl(
StudentID number(4,0) not null,
Name varchar(50) not null,
Surname varchar(50) not null,
ID_Number number(13,0) not null,
Address varchar(100) not null,
PRIMARY KEY (StudentID),
CONSTRAINT CheckStudentID check (length(StudentID) = 4),
CONSTRAINT CheckIDNumber check (length(ID_Number) = 13)
);'
EXEC (@DynamicSQL)

SET @DynamicSQL = 'CREATE table ['+@campus+'].Course_tbl(
CourseID integer not null,
CourseName varchar(50) not null,
Description varchar(100) not null,
StudentID number(4,0) not null,
PRIMARY KEY (CourseID),
FOREIGN KEY (StudentID) REFERENCES Student_tbl(StudentID),
CONSTRAINT CheckStudentID check (length(StudentID) = 4)
);'
EXEC (@DynamicSQL)

SET @DynamicSQL = 'CREATE table ['+@campus+'].ClassMarks_tbl(
ClassMarksID integer not null,
StudentID number(4,0) not null,
CourseID integer not null,
Semester1_Mark1 integer not null check (Semester1_Mark1 between 0 and 100),
Semester1_Mark2 integer not null check (Semester1_Mark2 between 0 and 100),
Semester1_Mark3 integer not null check (Semester1_Mark3 between 0 and 100),
Semester1_Average integer not null check (Semester1_Average between 0 and 100),
Semester1_Test_Mark integer not null check (Semester1_Test_Mark between 0 and 100),
Semester2_Mark1 integer not null check (Semester2_Mark1 between 0 and 100),
Semester2_Mark2 integer not null check (Semester2_Mark2 between 0 and 100),
Semester2_Mark3 integer not null check (Semester2_Mark3 between 0 and 100),
Semester2_Average integer not null check (Semester2_Average between 0 and 100),
Semester2_Test_Mark integer not null check (Semester2_Test_Mark between 0 and 100),
PRIMARY KEY (ClassMarksID),
FOREIGN KEY StudentID REFERENCES Student_tbl(StudentID),
FOREIGN KEY CourseID REFERENCES Course_tbl(CourseID),
CONSTRAINT CheckStudentID check (length(StudentID) = 4)
);'
EXEC (@DynamicSQL)

SET @DynamicSQL = 'CREATE table ['+@campus+'].Facilitator_tbl(
FacilitatorID integer not null,
Name varchar(50) not null,
Surname varchar(50) not null,
Address varchar(100) not null,
Paycheck deciaml(19,4) not null,
CourseID integer not null,
PRIMARY KEY (FacilitatorID),
FOREIGN KEY CourseID REFERENCES Course_tbl(CourseID)
);'
EXEC (@DynamicSQL)

SET @DynamicSQL = 'CREATE table ['+@campus+'].Parents_tbl(
ParentID integer not null,
Name varchar(50) not null,
Surname varchar(50) not null,
ID_Number number(13,0) not null,
StudentID number(4,0) not null,
PRIMARY KEY (ParentID),
FOREIGN KEY StudentID REFERENCES Student_tbl(StudentID),
CONSTRAINT StudentID check (length(StudentID) = 4)
);'
EXEC (@DynamicSQL)
END

EXEC AddCampus_proc 'Nelspruit'
EXEC AddCampus_proc 'Roodepoort'
EXEC AddCampus_proc 'Sandton'
EXEC AddCampus_proc 'Boksburg'
EXEC AddCampus_proc 'Pretoria'
EXEC AddCampus_proc 'Cape_Town'
EXEC AddCampus_proc 'Vereniging'
EXEC AddCampus_proc 'Bloemfontein'
EXEC AddCampus_proc 'Polokwane'
EXEC AddCampus_proc 'Durban'
EXEC AddCampus_proc 'Stellenbosch'
EXEC AddCampus_proc 'Port_Elizabeth'
EXEC AddCampus_proc 'Pochefstroom'
EXEC AddCampus_proc 'Auckland_Park'

查询被执行成功，但问题是实际上并未真正创建模式和表：

The query gets executed successfully but the problem is that the schemas and tables are not actually being created:

未创建表

未创建模式

我的问题是，为什么不创建表和模式？我推断出这是由于动态sql引起的，因为它可能是错误的，但是我不明白为什么在这种情况下查询才能成功执行。

My question is, why are the tables and schemas not being created? I deduced that it is because of the dynamic sql as it may be wrong, but I don't understand why the query executes successfully if that is the case.

推荐答案

好，修复了所有拼写错误，语法错误和名称不唯一的问题。都更好。
添加了一些错误处理。希望它能使您再次前进。

ok, fixed all the misspellings, incorrect syntax and non-unique names. all better. added a bit of error handling. hope it gets you going again.

USE master
GO
if not exists (select 1 from master.sys.databases where name = 'CTUDB')
CREATE DATABASE CTUDB
GO
USE CTUDB
GO
if object_id('AddCampus_proc','P') is not null
    begin
        drop proc AddCampus_proc;
    end;
GO
CREATE PROCEDURE AddCampus_proc(@campus varchar(50)
)
AS
BEGIN
DECLARE @DynamicSQL varchar(MAX)
SET @DynamicSQL = 'CREATE schema '+quotename(@campus)+''
begin try
    EXEC (@DynamicSQL);
end try
begin catch
    if error_number() <> 2759 --this just means the schema already exists.
        begin
            print(error_number())
            print(error_message())
        end
end catch

SET @DynamicSQL = 'CREATE table '+quotename(@campus)+'.Student_tbl(
StudentID numeric(4,0) not null PRIMARY KEY,
Name varchar(50) not null,
Surname varchar(50) not null,
ID_numeric numeric(13,0) not null,
Address varchar(100) not null,
CONSTRAINT '+@campus+'_Student_tbl_CheckStudentID check (len(StudentID) = 4),
CONSTRAINT '+@campus+'_Student_tbl_CheckIDnumeric check (len(ID_numeric) = 13)
);'
--print (@DynamicSQL);
EXEC (@DynamicSQL);

SET @DynamicSQL = 'CREATE table '+quotename(@campus)+'.Course_tbl(
CourseID integer not null PRIMARY KEY,
CourseName varchar(50) not null,
Description varchar(100) not null,
StudentID numeric(4,0) not null,
FOREIGN KEY (StudentID) REFERENCES ' + quotename(@campus) + '.Student_tbl(StudentID),
CONSTRAINT '+@campus+'_Course_tbl_CheckStudentID check (len(StudentID) = 4)
);'
--print (@DynamicSQL);
EXEC (@DynamicSQL);

SET @DynamicSQL = 'CREATE table '+quotename(@campus)+'.ClassMarks_tbl(
ClassMarksID integer not null PRIMARY KEY,
StudentID numeric(4,0) not null,
CourseID integer not null,
Semester1_Mark1 integer not null check (Semester1_Mark1 between 0 and 100),
Semester1_Mark2 integer not null check (Semester1_Mark2 between 0 and 100),
Semester1_Mark3 integer not null check (Semester1_Mark3 between 0 and 100),
Semester1_Average integer not null check (Semester1_Average between 0 and 100),
Semester1_Test_Mark integer not null check (Semester1_Test_Mark between 0 and 100),
Semester2_Mark1 integer not null check (Semester2_Mark1 between 0 and 100),
Semester2_Mark2 integer not null check (Semester2_Mark2 between 0 and 100),
Semester2_Mark3 integer not null check (Semester2_Mark3 between 0 and 100),
Semester2_Average integer not null check (Semester2_Average between 0 and 100),
Semester2_Test_Mark integer not null check (Semester2_Test_Mark between 0 and 100),
FOREIGN KEY (StudentID) REFERENCES ' + quotename(@campus) + '.Student_tbl(StudentID),
FOREIGN KEY (CourseID) REFERENCES ' + quotename(@campus) + '.Course_tbl(CourseID),
CONSTRAINT '+@campus+'_ClassMarks_tbl_CheckStudentID check (len(StudentID) = 4)
);'
--print (@DynamicSQL);
EXEC (@DynamicSQL);

SET @DynamicSQL = 'CREATE table '+quotename(@campus)+'.Facilitator_tbl(
FacilitatorID integer not null PRIMARY KEY,
Name varchar(50) not null,
Surname varchar(50) not null,
Address varchar(100) not null,
Paycheck decimal(19,4) not null,
CourseID integer not null,
FOREIGN KEY (CourseID) REFERENCES ' + quotename(@campus) + '.Course_tbl(CourseID)
);'
--print (@DynamicSQL);
EXEC (@DynamicSQL);

SET @DynamicSQL = 'CREATE table '+quotename(@campus)+'.Parents_tbl(
ParentID integer not null PRIMARY KEY,
Name varchar(50) not null,
Surname varchar(50) not null,
ID_numeric numeric(13,0) not null,
StudentID numeric(4,0) not null,
FOREIGN KEY (StudentID) REFERENCES ' + quotename(@campus) + '.Student_tbl(StudentID),
CONSTRAINT '+@campus+'_Parents_tbl_StudentID check (len(StudentID) = 4)
);'
--print (@DynamicSQL);
EXEC (@DynamicSQL);
END
GO
EXEC AddCampus_proc 'Nelspruit'
EXEC AddCampus_proc 'Roodepoort'
EXEC AddCampus_proc 'Sandton'
EXEC AddCampus_proc 'Boksburg'
EXEC AddCampus_proc 'Pretoria'
EXEC AddCampus_proc 'Cape_Town'
EXEC AddCampus_proc 'Vereniging'
EXEC AddCampus_proc 'Bloemfontein'
EXEC AddCampus_proc 'Polokwane'
EXEC AddCampus_proc 'Durban'
EXEC AddCampus_proc 'Stellenbosch'
EXEC AddCampus_proc 'Port_Elizabeth'
EXEC AddCampus_proc 'Pochefstroom'
EXEC AddCampus_proc 'Auckland_Park'

这篇关于在过程中使用动态SQL创建模式和表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！