1-) MSSQL RMOS - tablonun columnları null ise varsayilan değer olarak sıfır atar sutunları

alter proc stpTemizlikBack

(

@COLUMN_NAME nvarchar(MAX),

@TABLE_NAME nvarchar(max)

)

as

begin

/*

exec stpTemizlikBack 'Param_Server','Pos_Param'

*/

SET NOCOUNT ON;

SELECT 'Update ' + @TABLE_NAME + ' Set ' + COLUMN_NAME + ' = ISNULL([' + COLUMN_NAME + '], ' +

 CASE

WHEN DATA_TYPE = 'bit' THEN '0'

WHEN DATA_TYPE = 'nvarchar' THEN ''

WHEN DATA_TYPE = 'varchar' THEN ''

WHEN DATA_TYPE = 'int' THEN '0'

WHEN DATA_TYPE = 'decimal' THEN '0'

WHEN DATA_TYPE = 'date' THEN '''1/1/1900'''

WHEN DATA_TYPE = 'datetime' THEN '''1/1/1900'''

WHEN DATA_TYPE = 'uniqueidentifier' THEN '00000000-0000-0000-0000-000000000000'

ELSE '''''' -- everything else get's an empty string

 END + ')' As Ramco

Into #Sonuc

FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TABLE_NAME

Declare @Cumle Nvarchar(MAX)

Declare Dongu Cursor For Select Ramco From #Sonuc

Open Dongu

Fetch Next From Dongu Into @Cumle

WHILE @@FETCH_STATUS = 0

BEGIN

Print (@Cumle)

 --Select @Cumle

EXEC (@Cumle)

Fetch Next From Dongu Into @Cumle

END

Close Dongu

DEALLOCATE Dongu

end

2-) SELECT

alter proc stpTemizlikBack(@COLUMN_NAME nvarchar(MAX),@TABLE_NAME nvarchar(max) ) as begin

--exec stpTemizlikBack 'Param_Server','Pos_Param'

SELECT 'ISNULL([' + COLUMN_NAME + '], ' +

  CASE

    WHEN DATA_TYPE = 'bit' THEN '0'

WHEN DATA_TYPE = 'nvarchar' THEN '0'

WHEN DATA_TYPE = 'varchar' THEN '0'

    WHEN DATA_TYPE = 'int' THEN '0'

    WHEN DATA_TYPE = 'decimal' THEN '0'

    WHEN DATA_TYPE = 'date' THEN '''1/1/1900'''

    WHEN DATA_TYPE = 'datetime' THEN '''1/1/1900'''

    WHEN DATA_TYPE = 'uniqueidentifier' THEN '00000000-0000-0000-0000-000000000000'

    ELSE '''''' -- everything else get's an empty string

  END + ') AS [' + COLUMN_NAME + '],' As Ramco

FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TABLE_NAME

end