存储过程有多种执行Data Maniplation Language(DML)语句的方式。现在呈现两种方式。
--drop procedure [dbo].[pro_CreateUserAndAddUserProfile]
--Verssion Zero 存储过程中直接执行sql语句 Script Date: ??/??/????
CREATE PROCEDURE [dbo].[pro_CreateUserAndAddUserProfile]
(
@sys_userName nvarchar(50),
@sys_userEmail nvarchar(256),
@sys_roleId int,
@sys_password nvarchar(32)
)
AS
SET NOCOUNT ON
SET ANSI_WARNINGS ON
IF ISNULL(@sys_userName, 'NULLNONE') = 'NULLNONE'
BEGIN
RETURN -1;
END
ELSE
BEGIN
--声明user表的自增id
declare @autoUserId int;
declare @insertUserRecordUTCTime datetime;
set @autoUserId = -1;
set @insertUserRecordUTCTime = GETUTCDATE();
BEGIN
BEGIN TRY
BEGIN TRAN ProcessingInsertUser
insert into [dbo].[tb_User]([Email], [Password], [CreateTime], [RoleID], [IsEnable], [IsEmailConfirm], [IsLocked], [Alias], [UserName])
values(@sys_userEmail, @sys_password, @insertUserRecordUTCTime, @sys_roleId, 1, 0, 0, 'Alias', @sys_userName)
select @autoUserId = [ID] from [dbo].[tb_User] where [UserName] = @sys_userName and [Email] = @sys_userEmail and [RoleID] = @sys_roleId
and [Password] = @sys_password and [CreateTime] = @insertUserRecordUTCTime;
IF ISNULL(@autoUserId, 'NULLNONE') = 'NULLNONE' or -1 = @autoUserId
BEGIN
RETURN -2;
END
insert into [dbo].[tb_UserProfile]([UserID], [Email_1], [Active]) values(@autoUserId, @sys_userEmail, 0)
insert into [dbo].[tb_User_AdditonalInfo]([UserID]) values(@autoUserId)
COMMIT TRAN ProcessingInsertUser
END TRY
BEGIN CATCH
ROLLBACK TRAN ProcessingInsertUser
RETURN -3;
END CATCH
END
END
RETURN 1;
GO
</pre><pre>
--drop procedure [dbo].[pro_CreateUserAndAddUserProfile]<pre name="code" class="sql">--Script Date: ??/??/????
--Verssion One 存储过程中调用拼接的sql语句(EXEC SP_EXECUTESQL @SqlText)CREATE PROCEDURE [dbo].[pro_CreateUserAndAddUserProfile](@sys_userName nvarchar(50),@sys_userEmail nvarchar(256),@sys_roleId int,@sys_password nvarchar(32))IF (ISNULL(@sys_userName, 'NULLNONE') = 'NULLNONE' or ISNULL(@sys_userEmail, 'NULLNONE') = 'NULLNONE' or ISNULL(@sys_password, 'NULLNONE') = 'NULLNONE' or @roleId < 1)BEGINRETURN -1;ENDELSEBEGIN--声明user表的自增iddeclare @autoUserId int;declare @insertUserRecordUTCTime datetime;declare @executeActionSql nvarchar(4000);set @autoUserId = -1;set @insertUserRecordUTCTime = GETUTCDATE();BEGINBEGIN TRYBEGIN TRAN ProcessingInsertUserset @executeActionSql = 'insert into [dbo].[tb_User]([Email], [Password], [CreateTime], [RoleID], [IsEnable], [IsEmailConfirm], [IsLocked], [Alias], [UserName])values(@sys_userEmail, @sys_password, @insertUserRecordUTCTime, @sys_roleId, 1, 0, 0, ''Alias'', @sys_userName)';EXEC SP_EXECUTESQL @executeActionSql, N'@sys_userEmail nvarchar(256), @sys_password nvarchar(32), @insertUserRecordUTCTime datetime, @sys_roleId int, @sys_userName nvarchar(50)',@sys_userEmail, @sys_password, @insertUserRecordUTCTime, @sys_roleId, @sys_userName;set @executeActionSql = 'select @autoUserId = [ID] from [dbo].[tb_User] where [CreateTime] = @insertUserRecordUTCTime';EXEC SP_EXECUTESQL @executeActionSql, N'@autoUserId int OUT, @insertUserRecordUTCTime datetime',@autoUserId OUTPUT, @insertUserRecordUTCTime;IF (ISNULL(@autoUserId, 'NULLNONE') = 'NULLNONE' or -1 = @autoUserId)BEGINRETURN -2;ENDset @executeActionSql = 'insert into [dbo].[tb_UserProfile]([UserID], [Email_1], [Active]) values(@autoUserId, @sys_userEmail, 0);insert into [dbo].[tb_User_AdditonalInfo]([UserID]) values(@autoUserId);';EXEC SP_EXECUTESQL @executeActionSql, N'@autoUserId int, @sys_userEmail nvarchar(256)', @autoUserId, @sys_userEmailCOMMIT TRAN ProcessingInsertUserEND TRYBEGIN CATCHROLLBACK TRAN ProcessingInsertUserRETURN -3;END CATCHENDENDRETURN 1;GO
再来一发。试试ntext字段类型。
select * from #tab
--create occasional table
CREATE TABLE #temporaryTable
(
ID int identity(1,1) primary key,
LargeText ntext null
)
select * from #temporaryTable
insert into #temporaryTable(LargeText) select N'你是谁?' union select N'我是中国人'
--drop table #temporaryTable
--alter table #temporaryTable alter column LargeText text