如何在存储过程中创建多个视图？
我想创建一个临时存储过程来创建多个视图；像这样的事情：
create proc #t1 as
begin
create view v1 as select 1 as x
go
create view v2 as select 2 as x
end
不幸的是，当我在 Microsoft SQL Server 2005 中执行此操作时，我在第一行 create view 上收到语法错误。
像这样的东西是有效的：
create proc #t1 as
begin
exec('create view v1 as select 1 as x')
exec('create view v2 as select 2 as x')
end
然而，这似乎是做我想做的事情的一种糟糕的方式。
那么第一次尝试有什么问题，在存储过程中创建多个视图的最佳方法是什么？
I want to create a temporary stored procedure to create several views; so something like this:
create proc #t1 as
begin
create view v1 as select 1 as x
go
create view v2 as select 2 as x
end
Unfortunately, when I execute this in Microsoft SQL Server 2005, I get a syntax error on the first create view line.
Something like this works:
create proc #t1 as
begin
exec('create view v1 as select 1 as x')
exec('create view v2 as select 2 as x')
end
However, this seems like a terrible way of doing what I want.
So what's wrong with the first attempt, and what's the best way to create multiple views in a stored procedure?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
您不能在存储过程中使用
go。它不是 SQL 中的命令，而是 SQL 管理器中批次之间的分隔符，因此它将过程分成两个批次并导致语法错误，因为两个批次都不是完整的命令。You can't have a
goinside a stored procedure. It's not a command in SQL, it's a separator between batches in the SQL Manager, so it will cut the procedure into two batches and cause syntax errors because neither batch is a complete command.您不必编写一个完整的解析器来完成这项工作 - 您所需要做的就是命令行工具/SSMS 所做的事情 - 从文件中读取行并将它们累积在（在.Net中，它是一个字符串生成器，记不起 Java 中的对应词），直到遇到以单词
GO开头的行。每次到达该点时，将累积的缓冲区发送到 SQL Server，然后清空缓冲区并重新开始。只要您当前的脚本在需要时具有
GO，上述内容就应该有效。You don't have to write a full blown parser to make this work - all you need to do is what the command line tools/SSMS do - read lines from the file and accumulate them in a (in .Net, it's a stringbuilder, can't remember the equivalent in Java) until you encounter a line which starts with the word
GO. Each time you reach that point, send your accumulated buffer to SQL Server, and then empty your buffer and start again.So long as your current script has
GOwhenever it's required, the above should work.这很容易，您可以使用变量来实现此目的，将创建视图分配给@variable，然后在程序内执行EXEC（@Variable）语句
This is easy you can achieve this using variable,assign the create view to the @variable then EXEC(@Variable) statements inside the procedure