October 2, 2019 at 4:09 pm
When trying to create / save / execute a single .sql file containing multiple DDL operations, such as:
use database
go
create procedure [procname]()
as
begin
------------------
end
go
use database
go
create view [viewname]
as
use database
go
create anotherview [viewname2]
as
..............ETC
and getting errors such as "Create View must be the only statement in the batch" .....
I've concluded my understanding of control-of-flow, statements, batches is a little lacking.
Mostly what I'm focused on here is I'd like to find a modest sized reading that will allow me to better understand the control-of-flow concepts to enable me to properly write SQL code to combine the creation of multiple SQL objects into one executable code file.
I am seeking suggestions to that end; thanks. I've certainly done my own online research, but there is such a vast amount of information ranging from transactions and control of flow relating to if then else and a lot of other things.
October 2, 2019 at 4:29 pm
Test if the object exists first, if it doesn't CREATE the object with minimal functionality, then ALTER the object to have the full code you required.
use [database]
go
if object_id('[procname]','P') IS NULL BEGIN
EXEC ('CREATE PROCEDURE [procname] AS')
END
GO
ALTER procedure [procname]
as
begin
------------------
print 'hello'
end
go
use [database]
go
if object_id('[viewname]','V') IS NULL BEGIN
EXEC ('CREATE view [viewname] AS SELECT 1 X')
end
go
ALTER view [viewname]
as
SELECT 1 X
go
use [database]
go
if object_id('[viewname2]','V') IS NULL BEGIN
EXEC ('CREATE view [viewname2] AS SELECT 1 X')
end
go
alter view [viewname2]
as
SELECT 1 X
go
October 2, 2019 at 5:09 pm
If you're getting that error, the file isn't in the format you have shown. You must have something like:
create table mytable(
id int
)
create view myview as
select * from mytable
If you separate into batches, everything will run. Note, this is completely separate from transactions, which are open while your connection is open, regardless of batches.
I would follow Jonathan's advice for SQL2014-. For SQL2016+, I'd write CREATE OR ALTER
October 2, 2019 at 6:04 pm
@SSC Guru:
Okay, so ... i had a feeling it was something stupidly simple on my part, and apparently even more simple than I'd imagined.
I must have just been missing semicolons to indicate separate batches, then. I can't easily reproduce what the scenario was now, but I think that was probably it. I thought I had GO's after everything, but I guess the semicolons were missing.
@both: So I hadn't seen the create with exec and then alter method before. I am reading this, but then I see SSCGuru's reply, which makes it look like I was just missing something extremely simple. Given that, why the recommendation to do Jonathon's method? This seems just intuitively a little ... weirder or unnatural. What is the nutshell thinking behind this?
October 2, 2019 at 6:20 pm
why the recommendation to do Jonathon's method? This seems just intuitively a little ... weirder or unnatural. What is the nutshell thinking behind this?
If you drop an object then create it again you will lose all the permissions on that procedure.
If you just do an alter you will leave all the permissions on the object in-place as they were before you ran the script.
Also, if you just did CREATE and you re-ran the script you would get errors from SSMS as the object would already exist, so it makes the script rerunnable.
October 2, 2019 at 7:03 pm
Got it. thanks!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply