How can I create multiple views in a stored procedure?

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 se开发者_JS百科lect 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?

You can't have a go inside 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.

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 GO whenever it's required, the above should work.

This is easy you can achieve this using variable,assign the create view to the @variable then EXEC(@Variable) statements inside the procedure