· 8 years ago · Feb 02, 2018, 09:34 AM
1if exists (
2 select
3 *
4 from
5 INFORMATION_SCHEMA.ROUTINES
6 where
7 ROUTINE_NAME = 'usp_CreatePerson'
8 and SPECIFIC_SCHEMA = 'PsychoProductions'
9 and ROUTINE_TYPE = 'PROCEDURE'
10)
11drop procedure PsychoProductions.usp_CreatePerson;
12go
13
14create procedure PsychoProductions.usp_CreatePerson
15 @PersonRoleId int,
16 @Name varchar(500) = null,
17 @Organization varchar(500) = null,
18 @Website varchar(500) = null,
19 @DefaultBillingMethodId int = 1, --unassigned
20 @AddressTypeId int = null,
21 @StreetAddress varchar(500) = null,
22 @City varchar(200) = null,
23 @State char(2) = null,
24 @Zip varchar(10) = null,
25 @PhoneTypeId int = null,
26 @PhoneNumber varchar(20) = null,
27 @EmailTypeId int= null,
28 @EmailAddress varchar(200) = null
29as
30set nocount on;
31
32begin transaction;
33
34if not exists (select 1 from PsychoProductions.PersonRoles where Id = @PersonRoleId)
35begin;
36 throw 50001, 'Invalid PersonRoleId', 1
37 rollback transaction;
38end;
39else
40begin;
41 insert into PsychoProductions.Persons (
42 PersonRoleId,
43 Name,
44 Organization,
45 Website,
46 DefaultBillingMethodId,
47 IsActive
48 ) values (
49 @PersonRoleId,
50 @Name,
51 @Organization,
52 @Website,
53 @DefaultBillingMethodId,
54 1
55 );
56
57 declare @NewPersonId int = @@IDENTITY;
58
59 if @AddressTypeId is not null
60 begin;
61 if not exists (select 1 from PsychoProductions.AddressTypes where Id = @AddressTypeId)
62 begin;
63 throw 50001, 'Invalid AddressTypeId', 1;
64 rollback transaction;
65 end;
66 else
67 begin;
68 insert into PsychoProductions.PersonAddresses (
69 PersonId,
70 AddressTypeId,
71 StreetAddress,
72 City,
73 St,
74 Zip
75 ) values (
76 @NewPersonId,
77 @AddressTypeId,
78 @StreetAddress,
79 @City,
80 @State,
81 @Zip
82 );
83 end;
84 end;
85
86 if @PhoneTypeId is not null
87 begin;
88 if not exists (select 1 from PsychoProductions.PhoneTypes where Id = @PhoneTypeId)
89 begin;
90 throw 50001, 'Invalid PhoneTypeId', 1;
91 rollback transaction;
92 end;
93 else
94 begin;
95 insert into PsychoProductions.PersonPhones (
96 PersonId,
97 PhoneTypeId,
98 PhoneNumber
99 ) values (
100 @NewPersonId,
101 @PhoneTypeId,
102 @PhoneNumber
103 );
104 end;
105 end;
106
107 if @EmailTypeId is not null
108 begin;
109 if not exists (select 1 from PsychoProductions.EmailTypes where Id = @EmailTypeId)
110 begin;
111 throw 50001, 'Invalid EmailTypeId', 1;
112 rollback transaction;
113 end;
114 else
115 begin;
116 insert into PsychoProductions.PersonEmails (
117 PersonId,
118 EmailTypeId,
119 Email
120 ) values (
121 @NewPersonId,
122 @EmailTypeId,
123 @EmailAddress
124 );
125 end;
126 end;
127 commit transaction;
128end;
129
130go
131
132DECLARE @RC int
133DECLARE @PersonRoleId int = 2
134DECLARE @Name varchar(500) = 'John Doe'
135DECLARE @Organization varchar(500) = 'John Doe Inc'
136DECLARE @Website varchar(500) = null
137DECLARE @DefaultBillingMethodId int = 1
138DECLARE @AddressTypeId int = 1
139DECLARE @StreetAddress varchar(500) = '123 Main Street'
140DECLARE @City varchar(200) = 'New York'
141DECLARE @State char(2) = 'NY'
142DECLARE @Zip varchar(10) = '12345'
143DECLARE @PhoneTypeId int = 1
144DECLARE @PhoneNumber varchar(20) = '(111)111-1111'
145DECLARE @EmailTypeId int = 1
146DECLARE @EmailAddress varchar(200) = 'john@johndoeinc.biz'
147
148EXECUTE @RC = [PsychoProductions].[usp_CreatePerson]
149 @PersonRoleId
150 ,@Name
151 ,@Organization
152 ,@Website
153 ,@DefaultBillingMethodId
154 ,@AddressTypeId
155 ,@StreetAddress
156 ,@City
157 ,@State
158 ,@Zip
159 ,@PhoneTypeId
160 ,@PhoneNumber
161 ,@EmailTypeId
162 ,@EmailAddress
163
164Id PersonRoleId Name Organization Website DefaultBillingMethodId IsActive CreatedDate
165----------- ------------ -------- ------------ ----------- ---------------------- ----------- -----------------------
16610 2 John Doe John Doe Inc NULL 1 1 2016-06-17 17:35:56.813
167
168Id PersonId AddressTypeId StreetAddress City St Zip CreatedDate
169----------- ----------- ------------- --------------- -------- ---- ----- -----------------------
1702 10 1 123 Main Street New York NY 12345 2016-06-17 17:35:56.812
171
172Id PersonId PhoneTypeId PhoneNumber CreatedDate
173----------- ----------- ----------- ------------- -----------------------
1746 10 1 (111)111-1111 2016-06-17 17:35:56.813
175
176Id PersonId EmailTypeId Email CreatedDate
177----------- ----------- ----------- ------------------- -----------------------
1785 10 1 john@johndoeinc.biz 2016-06-17 17:35:56.813
179
180create table PsychoProductions.Persons (
181 Id int identity(1,1) primary key,
182 PersonRoleId int not null
183 references PsychoProductions.PersonRoles(Id),
184 Name varchar(500) null,
185 Organization varchar(500) null,
186 Website varchar(500) null,
187 DefaultBillingMethodId int not null
188 references PsychoProductions.BillingMethods(Id),
189 IsActive bit default 1,
190 CreatedDate datetime default getdate()
191);
192
193create table PsychoProductions.PersonAddresses (
194 Id int identity(1,1) primary key,
195 PersonId int not null
196 references PsychoProductions.Persons(Id),
197 AddressTypeId int not null
198 references PsychoProductions.AddressTypes(Id),
199 StreetAddress varchar(500) null,
200 City varchar(200) null,
201 St char(2) null,
202 Zip varchar(10) null,
203 CreatedDate datetime default getdate()
204);
205
206create table PsychoProductions.PersonPhones (
207 Id int identity(1,1) primary key,
208 PersonId int not null
209 references PsychoProductions.Persons(Id),
210 PhoneTypeId int not null
211 references PsychoProductions.PhoneTypes(Id),
212 PhoneNumber varchar(20) not null,
213 CreatedDate datetime default getdate()
214);
215
216create table PsychoProductions.PersonEmails (
217 Id int identity(1,1) primary key,
218 PersonId int not null
219 references PsychoProductions.Persons(Id),
220 EmailTypeId int not null
221 references PsychoProductions.EmailTypes(Id),
222 Email varchar(200) not null,
223 CreatedDate datetime default getdate()
224);
225
226-- ... blah blah blah ...
227
228-- This table will store the Id of the person you inserted
229declare @PersonId table (Id int)
230
231-- ... blah blah blah ...
232
233insert into PsychoProductions.Persons (
234 PersonRoleId,
235 Name,
236 Organization,
237 Website,
238 DefaultBillingMethodId,
239 IsActive
240)
241output Inserted.Id into @PersonId (Id)
242values (
243 @PersonRoleId,
244 @Name,
245 @Organization,
246 @Website,
247 @DefaultBillingMethodId,
248 1
249);
250
251-- ... blah blah blah ...