Two implemations of the store procedure

存储过程有多种执行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


  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值