USE [Gestoc]
CREATE OR ALTER PROCEDURE [dbo].[WebTrataCamposNullsSingle]
	@NomeTabela nvarchar(50),
	@Id int,
	@NomeCampo 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 and ' + @NomeCampo + '=' + CAST(@Id AS NVARCHAR(20))

	
	-- 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