(真心求解惑)关于SQL存储过程有几个疑问

和永恒有關 2012-09-11 09:27:02

先上段代码



create table Account

(

	accid char(32),

	balance money,

	creditlast datetime,

	userid varchar(32)

)

go

if exists(select * from sysobjects where name='pro_Account')

	drop procedure pro_Account

go

create procedure pro_Account

	@UserID nvarchar(36),	--用户ID	

	@Operate int,		--操作 0充值，1支取

	@MyMoney money,	         --金额

	@BankID int,		--银行ID

	@State int		--返回状态0失败，1成功，2余额不足

as

                  declare @isUser int=0,	--是否存在用户

		@mySql nvarchar(max),	--执行SQL语句

		@errNo int=0,		--总错误

		@myCount int=0		--影响行数

begin transaction

	select @isUser=COUNT(accid) from Account where userid=@UserID

	if(@isUser<=0)

		begin

			declare @accid nvarchar(36)=REPLACE(convert(varchar(36),NEWID()),'-',''),

					@mydate nvarchar(100)=convert(nvarchar(100),GETDATE(),20)

			print @mydate

			print @accid

			

			set @MyMoney=CAST(@MyMoney as money)

			print cast(@mymoney as nvarchar(20))

			set @mySql='insert into Account values('''+@accid+''','+@MyMoney+','''+@mydate+''','''+@UserID+''')'

			exec sp_executesql @mySql,N'@accid nvarchar(36),@MyMoney money,@mydate nvarchar(100),@UserID nvarchar(36)',@accid,@MyMoney,@mydate,@UserID

			--exec sp_executesql @mySql,N'@accid nvarchar(36),@mydate nvarchar(100),@UserID nvarchar(36)',@accid,@mydate,@UserID			

			

			set @mycount=@myCount+@@ROWCOUNT

			print @mysql

			print @mycount

			set	@errNo=@errNo+@@ERROR	

			print @errNo		

			if(@errNo>0 or @myCount<=0)

				begin

					print '回滚'

					rollback	

					return

				end

		end

	commit



调用

declare @id nvarchar(36)

set @id=REPLACE(convert(varchar(36),NEWID()),'-','')

exec pro_Account @id,0,21.3,0,0

问题1
为什么上述代码调用时给参数 21.3 money类型，总是报错，说转换字符出错之类的
从网上查是因为21.3中的点存储过程认成字符了？？很疑惑
问题2
exec sp_executesql 和 exec(@sql) 有什么本质区别吗？我知道前者给参，后者是定义完整直接执行
可是什么时候用哪个呢？
问题3
比如在存储过程中执行一句@sql=insert 在存储过程中调用 exec(@sql)，然后再调用存储过程
直接写 insert 之后调用存储过程 exec procedureName
有什么区别吗？在什么时候用到哪个呢？

真心求解惑！

...全文

414 50 打赏收藏转发到动态举报

写回复

用AI写文章

50 条回复

切换为时间正序

请发表友善的回复…

发表回复

和永恒有關 2012-09-14

打赏
举报

第一种：
SET @Sql='select top 1 @ID=@column FROM sysobjects'
第二种：
SET @Sql='select top 1 @ID='+@column+' FROM sysobjects'

EXEC sp_executesql @sql,N'@ID int output,@column int',@ID OUTPUT,@column

前面语法不对，改了下语法
就是一个加参数加引号，可是我试了试不加引号也没问题

和永恒有關 2012-09-14

打赏
举报

[Quote=引用 46 楼的回复:]

今天忙看来已经没我什么事情了
[/Quote]

来了就好嘿嘿！
帮忙看看48L呗

和永恒有關 2012-09-14

打赏
举报

[Quote=引用 47 楼的回复:]

引用 41 楼的回复:

引用 38 楼的回复:

sp_executesql主要用于条件的参数是动态变化时需要

不是说这种sp_executesql 有缓存机制性能好些吗
条件参数是不是只 where 后面的列名？
比如
select * from tb where @column=@value
里面的@column

这个看11楼啊
[/Quote]

汤哥看看45L
重点是这
第一种：
SET @Sql='select top 1 @ID=@column FROM sysobjects where ID>@ID order by ID asc'
第二种：
SET @Sql='select top 1 @ID='+@column+' FROM sysobjects where ID>@ID order by ID asc'

EXEC sp_executesql @sql,N'@ID int output',@ID OUTPUT

第一种第二种有区别吗

中国风 2012-09-13

打赏
举报

表名和列名先用字符串加起来

如:

SET @Sql='select top 1 @ID='+@column+' FROM sysobjects where ID>@ID order by ID asc'

中国风 2012-09-13

打赏
举报

最好贴完整

这类情况可用sp_executesql

DECLARE @column sysname

SET @column='ID'





DECLARE @ID INT,@Sql NVARCHAR(1000)

SET @ID=1000000

SET @Sql='select top 1 @ID='+@column+' FROM sysobjects where ID>@ID order by ID asc'



EXEC sp_executesql @sql,N'@ID int output',@ID OUTPUT

SELECT * FROM sysobjects WHERE ID=@ID

和永恒有關 2012-09-13

打赏
举报

[Quote=引用 38 楼的回复:]

sp_executesql主要用于条件的参数是动态变化时需要
[/Quote]

不是说这种sp_executesql 有缓存机制性能好些吗
条件参数是不是只 where 后面的列名？
比如
select * from tb where @column=@value
里面的@column

和永恒有關 2012-09-13

打赏
举报

[Quote=引用 33 楼的回复:]

楼主的语句没贴完整?

如果是以上语句用一句就行了,加事务都不用

SQL code
if exists(select * from sysobjects where name='pro_Account')
drop procedure pro_Account
go
create procedure pro_Account
@UserID nvarchar(36……
[/Quote]

嗯我的语句没贴完整，还有很多没贴。

中国风 2012-09-13

打赏
举报

参照：動態語句的使用方法(exec/sp_executesql)
http://blog.csdn.net/roy_88/article/details/3020586

中国风 2012-09-13

打赏
举报

sp_executesql主要用于条件的参数是动态变化时需要

中国风 2012-09-13

打赏
举报

DECLARE @column sysname

SET @column='ID'

EXEC('select '+@column+' FROM tb')

这样用

和永恒有關 2012-09-13

打赏
举报

[Quote=引用 31 楼的回复:]

引用 27 楼的回复:

引用 13 楼的回复:

我感觉你第三个问题是不是这样：
情景1：declare @sql varchar(max)='insert into xxxx select * from xxx' exec(@sql);注意，我这在08的环境。
情景2：insert into xxxx exec procedureName

我个人理解你的第三个问题是……
[/Quote]

那这样
insert into tb(id) values(@id)
或者这样
selet @column from tb

为什么可以呢？？？

中国风 2012-09-13

打赏
举报

事务方法

begin try

begin tran

--DML语句
commit tran

end try
begin catch
print '出错'
rollback tran
end catch

中国风 2012-09-13

打赏
举报

楼主的语句没贴完整?

如果是以上语句用一句就行了,加事务都不用

if exists(select * from sysobjects where name='pro_Account')

    drop procedure pro_Account

go

create procedure pro_Account

    @UserID nvarchar(36),    --用户ID    

    @Operate int,        --操作 0充值，1支取

    @MyMoney money,             --金额

    @BankID int,        --银行ID

    @State int        --返回状态0失败，1成功，2余额不足

as

BEGIN TRY

    if NOT EXISTS(SELECT 1 from Account where userid=@UserID)

            insert into Account values(@UserID,@MyMoney,GETDATE(),@UserID)

END TRY

BEGIN CATCH

	PRINT N'回滚'

END CATCH

中国风 2012-09-13

打赏
举报

[Quote=引用 30 楼的回复:]

引用 28 楼的回复:

引用 26 楼的回复:

引用 14 楼的回复:

set 其实就是在拼接出来一个sql 语句的字符串，既然是拼接字符串，那么要求数据类型要一致
不一致的会发生隐式转换，money 优先级要高于varchar 所以要把varchar值转为money 就报错了

insert exec sp
这种语法是你在一个sp里面要调用另一个sp的……
[/Quote]

情景2： --为什么不这样用,没看到有必要嵌套多1层字符串
insert into xxxx values(',')

汤姆克鲁斯 2012-09-13

打赏
举报

[Quote=引用 27 楼的回复:]

引用 13 楼的回复:

我感觉你第三个问题是不是这样：
情景1：declare @sql varchar(max)='insert into xxxx select * from xxx' exec(@sql);注意，我这在08的环境。
情景2：insert into xxxx exec procedureName

我个人理解你的第三个问题是上面这两种情景的区别。
如果是，……
[/Quote]
第三个问题如果是简单的 insert 语句就没必要拼接在 exec

如果你要穿字段或者表名的话就必须拼接

如： insert into @tb(id,name) values(1,'aa')
或者
insert into tb(@id,@name) values(1,'aa')
或者
insert into tb(id,name) select id,name from @tb2

insert into tb(id,name) select @id,name from tb

要执行这样的必须动态拼接

和永恒有關 2012-09-13

打赏
举报

[Quote=引用 28 楼的回复:]

引用 26 楼的回复:

引用 14 楼的回复:

set 其实就是在拼接出来一个sql 语句的字符串，既然是拼接字符串，那么要求数据类型要一致
不一致的会发生隐式转换，money 优先级要高于varchar 所以要把varchar值转为money 就报错了

insert exec sp
这种语法是你在一个sp里面要调用另一个sp的结果时候用到的。

不是不看阿……
[/Quote]

汤哥比我回复的快哈哈，我理解了，帮我看看27楼呗

和永恒有關 2012-09-13

打赏
举报

[Quote=引用 25 楼的回复:]

引用 22 楼的回复:

引用 20 楼的回复:

你去查查联机丛书,declare 语句什么时候允许直接在定义时赋值了?
如果是过程的形参,那是可以赋一个默认值的,这与定义变量不是一回事.

好吧，改了之后调用还是报一样的错误
“消息 235，级别 16，状态 0，过程 pro_test，第 26 行
无法将 char 值转换为 money。该 char 值的语法……
[/Quote]

有点明白了
你的意思是说这句set @mySql='insert into Account
values('''+@accid+''','+LTRIM(@MyMoney)+','''+@mydate+''','''+@UserID+''')'

拼接字符串其实就是全部隐式转换成了字符类型，之所以int可以，money不可以，是因为money优先级高于varchar，加Ltrim其实关键不在于去空格，而是返回字符串类型。其实我加str(@Mymoney),或者cast，convert都可以只要返回时字符类型就ok是吧

再帮我看看第三个问题呗

汤姆克鲁斯 2012-09-13

打赏
举报

[Quote=引用 26 楼的回复:]

引用 14 楼的回复:

set 其实就是在拼接出来一个sql 语句的字符串，既然是拼接字符串，那么要求数据类型要一致
不一致的会发生隐式转换，money 优先级要高于varchar 所以要把varchar值转为money 就报错了

insert exec sp
这种语法是你在一个sp里面要调用另一个sp的结果时候用到的。

不是不看阿汤哥，是CSDN最近抽了··，我昨……
[/Quote]
Ltrim 在这里不是去空格，返回的结果是字符串类型的，cast 或者convert or str不是写的比较长，所以很多时候习惯写个简单的。

拼接就是连接起来就是加法运算啊，加的时候当然会发生数据类型隐式转换了啊
你运行一下下面的看看结果。
select '100'+'100'
select '100'+100

和永恒有關 2012-09-13

打赏
举报

[Quote=引用 13 楼的回复:]

我感觉你第三个问题是不是这样：
情景1：declare @sql varchar(max)='insert into xxxx select * from xxx' exec(@sql);注意，我这在08的环境。
情景2：insert into xxxx exec procedureName

我个人理解你的第三个问题是上面这两种情景的区别。
如果是，我就说说我的理解：
情景1：……
[/Quote]
[SQL]
额，第一个问题的表我已经在最上面给出建表语句了啊，就一个表
第三个问题其实是这样
情景1：
create create procedure pro_test
as
begin
declare @sql varchar(max)='insert into xxxx values('','')'
exec(@sql)
end
--调用
exec pro_test
情景2：
create create procedure pro_test
as
begin
insert into xxxx values('','')
end
--调用
exec pro_test
[/SQL]
不知道这样清晰吗

和永恒有關 2012-09-13

打赏
举报

[Quote=引用 14 楼的回复:]

set 其实就是在拼接出来一个sql 语句的字符串，既然是拼接字符串，那么要求数据类型要一致
不一致的会发生隐式转换，money 优先级要高于varchar 所以要把varchar值转为money 就报错了

insert exec sp
这种语法是你在一个sp里面要调用另一个sp的结果时候用到的。
[/Quote]
不是不看阿汤哥，是CSDN最近抽了··，我昨天压根就看不到页面有显示你的回复
果然是这么回事，确实用Ltrim就可不报错了。可是阿汤哥，我还有个疑问
“set 其实就是在拼接出来一个sql 语句的字符串，既然是拼接字符串，那么要求数据类型要一致”
这句话不太理解。
还有就是为什么用Ltrim就可以了呢？ltrim是去掉左边空格，可是我输出我的@mymoney的时候是没有空格的啊