-- This procedure is useful to copy diagrams from a DataBase to another.
-- The script create the procedure "sp_ImportDiagram" in a DB (called First); you can then import diagrams from any other DB (called Second) 
-- in the same server. It's not difficult to make possible import from 
-- other servers. To do this you need only pass two parameters:
-- the name of the DB source,
-- the name of the diagram.
-- The operation is aborted if already exists a diagram with the same name in the DB First or if none or more than one diagram with the given name are retrived in the DB Second.
-- involved in!!!
-- This script is not guaranteed to work correctly. In order to improve its performance i need you 
-- write me every error you discover. 
-- Please, send message to: 
-- Flavio Valentini <FlavioV@mo-da.com>
-- date: 8 Feb 1999


CREATE PROCEDURE sp_ImportDiagram
-- Parameters: DB_source_name , Diagram_name
@dbname varchar(30), @diagrname varchar(32)
AS
begin transaction
declare @objectid int
declare @newobjectid int
declare @statement nvarchar(400)

create table #target  (objectid int)

select @statement = 'select isnull(objectid,0) '
select @statement = @statement + 'from '+ rtrim(@dbname) + '..dtproperties '
select @statement = @statement +'where property = ''DtgSchemaNAME'' and value = ''' + @diagrname + ''''
print @statement
insert #target  exec (@statement)
select @objectid = count(*) from #target
if (@objectid > 1)
begin
  print 'More diagrams have the same name. Operation failed.'
  rollback transaction
end
else
begin
	select @objectid = max(objectid) from #target
	if (@objectid > 0)
	begin
		select @newobjectid = isnull(max(objectid),0)
		from dtproperties
		where property = 'DtgSchemaNAME' and  value = @diagrname
		if (@newobjectid = 0) 
		begin
			select @newobjectid = max(objectid)+1  from dtproperties
			select @statement = 'insert into dtproperties (objectid,property,value,lvalue,version) '
			select @statement = @statement + 'select ' + cast(@newobjectid as varchar) + ', property, value, lvalue, version '
			select @statement = @statement + 'from '+ rtrim(@dbname) + '..dtproperties '
			select @statement = @statement + 'where objectid = ' +  cast(@objectid as varchar)
			print @statement
			exec (@statement)
			commit transaction
		end
		else
		begin 
			print 'Diagram name already exists. Operation failed'
			rollback transaction
		end
	end
	else
	begin
		print 'Diagram not found in the source DB.'
		rollback transaction
	end
end