
/****** Object:  StoredProcedure [dbo].[_ini_creartabla]    Script Date: 10/11/2016 14:47:18 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[_ini_creartabla]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[_ini_creartabla]
GO

create procedure [dbo].[_ini_creartabla]	
	@p_tabla  varchar(100),
	@p_identidad bit
as	
begin	

declare @p_tablaSinCorchete varchar(100)	
declare @tablaHija bit
declare @tablaIntermedia bit

set @p_tablaSinCorchete = replace(@p_tabla,'.','_')	

if @p_tablaSinCorchete = @p_tabla
begin
	set @tablaHija = 0
end
else
begin
	set @tablaHija=1
end

if CHARINDEX ( '_' ,@p_tablaSinCorchete ,5 )=0
begin
	set @tablaIntermedia = 0
end
else
begin
	set @tablaIntermedia = 1
end


set @p_tabla = '[' + @p_tabla + ']'	


declare @query varchar (8000)	
set @query = 'IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'''+ @p_tabla +''') AND type in (N''U'')) drop table ' + @p_tabla
exec (@query)	

set @query = 'create table '  + @p_tabla + '(	
	id int NOT NULL '

if @p_identidad=1 	
begin	
	set @query = @query + 'IDENTITY (0,1)'
end	
	set @query = @query + ',
	uid varchar(500) NOT NULL,
	eid varchar(50) NOT NULL,
	id_usuario_modifico int NOT NULL default (0),
	id_usuario_creo int NOT NULL default (0),
	fechaModificacion datetime NOT NULL default (getdate()),
	fechaCreacion smalldatetime NOT NULL default (getdate()),
	active bit NOT NULL default (1),
	[' + right(@p_tabla,len(@p_tabla)-5) + ' varchar(300) NOT NULL
	)  ON [PRIMARY]'

exec( @query)	

--primary key	
set @query = 'ALTER TABLE '  + @p_tabla + ' ADD CONSTRAINT	PK_'  + replace(replace(replace(@p_tabla,'.','_'),'[',''),']','') + ' PRIMARY KEY CLUSTERED (id)  ON [PRIMARY]'
exec (@query)	

--campo codigo
if @tablaHija=0 and @tablaIntermedia=0
begin
	set @query = 'ALTER TABLE '  + @p_tabla + ' ADD [' + right(@p_tablaSinCorchete,len(@p_tablaSinCorchete)-4) + '_codigo] varchar(100)'
exec (@query)	
end

--index X eid	
set @query = 'CREATE NONCLUSTERED INDEX IX_'+ @p_tablaSinCorchete +' ON '+ @p_tabla +' (	
	eid
	) ON [PRIMARY]'
exec (@query)	

--registro cero	
if @p_identidad=1 	
begin	
	set @query = 'insert into '  + @p_tabla + ' (uid,eid,[' + right(@p_tabla,len(@p_tabla)-5) + ',active) values (''SAMM0800101'',''00'','''',0)'
	exec (@query)
end	
else	
begin	
	set @query = 'insert into '  + @p_tabla + ' (id,uid,eid,[' + right(@p_tabla,len(@p_tabla)-5) + ',active) values (0,''SAMM0800101'',''00'','''',0)'
	exec (@query)
end	
end	


GO


