SQL-objectChangeTemplates/tmpl_scalarFunction.sql at master · rato-li/SQL-objectChangeTemplates · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
select 'script is' = '020_<srv>_f_<fn>', 'The time is :' = getdate(), 'on server' = @@servername
go

Use <db>
go

-- before change row counts
print 'Before image'
if exists (select * from sysobjects where id = object_id(N'[dbo].[<fn>]') and OBJECTPROPERTY(id, N'IsScalarFunction') in (0, 1))
begin
	print '	exec sp_helptext <fn>'
	exec sp_helptext '<fn>'
end
else
	print '	<fn> does not exist'
GO

set XACT_ABORT ON
;
-- for testing,
-- comment out for production migration
begin tran
go

print 'Update Begin'
go

if exists (select * from sysobjects where id = object_id(N'[dbo].[<fn>]') and OBJECTPROPERTY(id, N'IsScalarFunction') in (0, 1))
begin
	print '	Dropping <fn>'
	drop function if exists [dbo].[<fn>]
end
GO

print '	Creating <fn>'
go

SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON
GO

CREATE FUNCTION [dbo].[<fn>] (
	<params>
)
RETURNS int
AS
/*
** Purpose:
**
** Parameters:
**
** Return:
**
** Usage:
**
** Note:
**
** Revision History:
**	Date:
**	Author:
**	Purpose:
*/
BEGIN
	<body>
END
go

SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON
GO

GRANT  EXECUTE  ON [dbo].[<fn>] TO [public]
GO

print 'Update Done'

-- after change row counts
print 'After image'
print '	exec sp_helptext <fn>'
exec sp_helptext '<fn>'
go

-- for testing,
-- comment out for production migration
--rollback tran
commit
go

select 'script is' = '020_<srv>_f_<fn>', 'The time is :' = getdate(), 'on server' = @@servername
go