如何使用动态SQL [英] How to use dynamic SQL

问题描述

我正在使用动态sql创建新架构并在新创建的schem中创建表。我写了以下存储过程。

Hi,
I am using dynamic sql to create a new schema and to create a table in that new created schem. I have written following stored procedure.

IF exists(select * from sys.objecs where name=dbo.BuildClientSchema and [schema_id]=SCHEMA_ID('dbo'))
drop procedure dbo.BuildClientSchema
go

CREATE PROCEDURE dbo.BuildClientSchema
@schemaname NVARCHAR(20)
AS
BEGIN
DECLARE @sql NVARCHAR(MAX) = N'CREATE SCHEMA [' + @schemaName + '] AUTHORIZATION [dbo];'

PRINT @sql

EXEC @sql

SET @sql = 'CREATE TABLE [' + @schemaName + '].[Products](' + CHAR(13) + CHAR(10) +
        CHAR(9) + 'Id INT PRIMARY KEY,' + CHAR(13) + CHAR(10) +
        CHAR(9) + 'Name NVARCHAR(30) NOT NULL,' + CHAR(13) + CHAR(10) +
        CHAR(9) + 'ProductDesc NVARCHAR(100),' + CHAR(13) + CHAR(10) +
        CHAR(9) + 'Price DECIMAL (20),' + CHAR(13) + CHAR(10) +
        CHAR(9) + 'CategoryId INT NOT NULL)'

		PRINT @sql

		EXEC @sql

END

它显示if语句中无法识别语句的错误 - 错误 - SQL70001。帮助解决这个问题。

It shows me error in if statement that the statement is not recognized - error - SQL70001. Help to resolve this.

推荐答案

试试这个：

Try this:

IF exists(select * from sys.objects where name='dbo.BuildClientSchema' and [schema_id]=SCHEMA_ID('dbo'))
drop procedure dbo.BuildClientSchema;
go

您的代码有1）sys.objecs而不是sys.objects 2）程序名称周围没有引号

Your code had 1) sys.objecs instead of sys.objects 2) No quotes around procedure name

我相信我前几天给了你这个SQL。



首先，在 IF EXISTS 语句中，您需要将对象放在单引号中。你也不能把架构名称放在里面。如果查询 sys.objects 视图，您将看到原因。您的架构在 [schema_id] = SCHEMA_ID（'dbo'）部分条件下进行过滤。

I believe I gave you this SQL the other day.

First, in your IF EXISTS statement, you need to place the object in single quotes. You also can't put the schema name in it has well. If you query the sys.objects view, you will see why. Your schema is filtered on the [schema_id]=SCHEMA_ID('dbo') part of the condition.

IF EXISTS(SELECT * FROM sys.objecs WHERE name='BuildClientSchema' AND [schema_id]=SCHEMA_ID('dbo'))
    DROP PROCEDURE dbo.BuildClientSchema
GO

如果这个陈述仍然给你带来麻烦，试试这个：

If this statement is still giving you trouble, try this:

IF EXISTS(SELECT * FROM sys.objecs WHERE name='BuildClientSchema' AND [schema_id]=SCHEMA_ID('dbo'))
BEGIN
    DROP PROCEDURE dbo.BuildClientSchema
END
GO

现在， IF EXISTS 声明是不是存储过程定义的一部分。因此，如果要将其添加到SQL Server数据库项目中，则需要删除此行。在部署数据库时，Visual Studio SSDT实际上将处理何时执行 CREATE 与 ALTER 。



此外，如果您使用的是数据库项目或SSDT，则需要添加对 master 数据库的引用，以便构建工具知道服务器上存在 sp_executesql 的位置。此链接显示了如何执行此操作：

SSDT：未解析的对象引用[dbo]。[sp_executesql]。 [ ^ ]



如果您在尝试执行存储过程时遇到错误，那是因为您是使用 EXE @sql 而不是 EXE sp_executesql @sql 。要更好地理解这一点，请查看本文以更好地理解动态SQL：

在SQL Server中执行动态SQL命令 [ ^ ]

Now, the IF EXISTS statement is not apart of your stored procedure definition. So if you are adding this to a SQL Server Database project, This line needs to be removed. The Visual Studio SSDT will actually handle when to do a CREATE vs an ALTER when deploying a database.

Also, if you are using a database project or SSDT, you will need to add reference to the master database so that build tools knows where sp_executesql lives on the server. This link shows how to do that:
SSDT: unresolved reference to object [dbo].[sp_executesql].[^]

If you are getting an error trying to execute the stored procedure, it is because you are using EXE @sql instead of EXE sp_executesql @sql. To understand this better, take a look at this article to better understand dynamic SQL:
Execute Dynamic SQL commands in SQL Server[^]

这篇关于如何使用动态SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！