USE [Gestoc]
-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
GO
/****** Object:  StoredProcedure [dbo].[WebTrataCamposNulls]    Script Date: 17/01/2018 09:00:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[WebTrataCamposNulls]
	@NomeTabela nvarchar(50)
AS
DECLARE @teTabelaL AS nVarChar(20) 
DECLARE @teColunaL AS nVarChar(100) 
DECLARE @teTipoL AS nVarChar(20) 
DECLARE @inTamanhoL AS Int 
DECLARE @teInstrucaoL AS nVarChar(4000) 
DECLARE @teNova_LinhaL AS nVarChar(2) 
DECLARE @rcsColunasL AS CURSOR 
  
   
			  
-- Inicializar 
SET @teNova_LinhaL = CHAR(13) + CHAR(10) 

	
-- Obter dados sobre as colunas da tabela 
SET @rcsColunasL = CURSOR Dynamic FOR 
	SELECT sysobjects.name AS Tabela, 
			syscolumns.name AS Coluna, 
			systypes.name AS Tipo_Dados, 
			syscolumns.length AS Tamanho 
	FROM sysobjects 
	JOIN syscolumns ON sysobjects.id = syscolumns.id 
	JOIN systypes ON syscolumns.xtype=systypes.xtype 
	WHERE sysobjects.xtype='U' 
	AND sysobjects.name = @NomeTabela 
	AND systypes.name <> 'sysname' 
	ORDER BY syscolumns.colid 

	
-- Abrir o cursor 
OPEN @rcsColunasL 
  
	
-- Carregar o primeiro registo 
FETCH NEXT FROM @rcsColunasL 
INTO @teTabelaL, @teColunaL, @teTipoL, @inTamanhoL 

	
-- Juntar 
SET @teInstrucaoL = 'UPDATE '+@NomeTabela 

	
-- Percorrer todas as colunas 
WHILE @@FETCH_STATUS = 0 
BEGIN 
			   
			   
	-- Juntar 
	SET @teInstrucaoL = 'UPDATE '+@NomeTabela +' SET ' + @teColunaL + ' = ' + CASE 
														WHEN @teTipoL = 'bit' THEN '0' 
														WHEN @teTipoL = 'datetime' THEN ' ''01/01/1980'' ' 
														WHEN @teTipoL = 'float' THEN '0' 
														WHEN @teTipoL = 'image' THEN  ' '''' ' 
														WHEN @teTipoL = 'int' THEN '0' 
														WHEN @teTipoL = 'money' THEN '0' 
														WHEN @teTipoL = 'ntext' THEN ' '''' ' 
														WHEN @teTipoL = 'nvarchar' THEN ' '''' ' 
														WHEN @teTipoL = 'real' THEN '0' 
														WHEN @teTipoL = 'smallint' THEN '0' 
														WHEN @teTipoL = 'varchar' THEN ' '''' ' 
													END  
	SET @teInstrucaoL =@teInstrucaoL+ 'WHERE ' + @teColunaL + ' IS Null' 

	
	-- Debug  
PRINT @teInstrucaoL 
			   
-- Limpar coluna 
EXEC (@teInstrucaoL) 
			   
-- Proximo registo 
FETCH NEXT FROM @rcsColunasL 
INTO @teTabelaL, @teColunaL, @teTipoL, @inTamanhoL 
			   
End 
			   
-- FECHAR 
			   
-- Libertar o recordset 
CLOSE @rcsColunasL 
DEALLOCATE @rcsColunasL