简单一点:declare @css nchar(100), @bb int select @css = 'select @c = (@a+@b)/2' exec sp_executesql @css, '@a int, @b int, @c int output', 10, 29, @bb output select @bb as bb 通过系统存储过程sp_executesql执行动态的SQL命令(公式保存在@css), 这个动态的SQL共有3个参数, 2个参数为input, 1个为output..... declare @sx_sum int set @sum1='select @a = count(*) as sx_sum from givemainten_tab where equip_name = '+@eq_name exec sp_executesql @sum1, '@a int output', @sx_sum output ....这样, @xs_sum 就是你需要的一个count(*)值.
在set @sum1='select @a = count(*) as sx_sum from givemainten_tab where equip_name = '+@eq_name中,你加了@a=是什么意思?还有后面的sx_sum是不是你定义的@sx_sum,怎么用了=还用as,我看不明白,请加以解释,谢谢!!
count(*) as sx_sum 是没必要, 应该是set @sum1='select @a = count(*) from givemainten_tab where equip_name = '+@eq_name
declare @sx_sum int, @qx_sum int declare @sumSQL varchar(100) declare @mainten_sum int declare statistic_cursor CURSOR for select equip_name from equipments_tab open statistic_cursor FETCH NEXT FROM statistic_cursor INTO @eq_name WHILE(@@fetch_status=0) begin set @sumSQL = 'select @a = count(*) from givemainten_tab where equip_name = '''+@eq_name +'''' exec sp_executesql @sumSQL, '@a int output', @sx_sum output set @sumSQL = 'select @a = count(*) from gomainten_tab where equip_name = '''+@eq_name +'''' exec sp_executesql @sumSQL, '@a int output', @qx_sum output set @mainten_sum=sx_sum+qx_sum
insert into statisticequip_tab values (@eq_name,@mainten_sum) FETCH NEXT FROM statistic_cursor INTO @eq_name end close statistic_cursor deallocate statistic_cursor
nononono(null,null),我就是这样,我执行的时候出现了以下的错误提示: Cannot convert parameter '@statement' to ntext/nchar/nvarchar data type expected by procedure. 这是什么错误提示???
这样试试:declare @sx_sum int, @qx_sum int declare @sumSQL nvarchar(100) declare @mainten_sum int declare statistic_cursor CURSOR for select equip_name from equipments_tab open statistic_cursor FETCH NEXT FROM statistic_cursor INTO @eq_name WHILE(@@fetch_status=0) begin set @sumSQL = N'select @a = count(*) from givemainten_tab where equip_name = '''+@eq_name +'''' exec sp_executesql @sumSQL, '@a int output', @sx_sum output set @sumSQL = N'select @a = count(*) from gomainten_tab where equip_name = '''+@eq_name +'''' exec sp_executesql @sumSQL, N'@a int output', @qx_sum output set @mainten_sum=sx_sum+qx_sum
insert into statisticequip_tab values (@eq_name,@mainten_sum) FETCH NEXT FROM statistic_cursor INTO @eq_name end close statistic_cursor deallocate statistic_cursor
declare @sx_sum int, @qx_sum int declare @sumSQL varchar(300),@NsumSQL nvarchar(300) declare @mainten_sum int declare statistic_cursor CURSOR for select equip_name from equipments_tab open statistic_cursor FETCH NEXT FROM statistic_cursor INTO @eq_name WHILE(@@fetch_status=0) begin set @sumSQL = 'select @a = count(*) from givemainten_tab where equip_name = '''+@eq_name +'''' set @NsumSQL = convert(nvarchar(300),@sumSQL) exec sp_executesql @NsumSQL, N'@a int output', @sx_sum output set @sumSQL = 'select @a = count(*) from gomainten_tab where equip_name = '''+@eq_name +'''' set @NsumSQL = convert(nvarchar(300),@sumSQL) exec sp_executesql @NsumSQL, N'@a int output', @qx_sum output set @mainten_sum=sx_sum+qx_sum
insert into statisticequip_tab values (@eq_name,@mainten_sum) FETCH NEXT FROM statistic_cursor INTO @eq_name end close statistic_cursor deallocate statistic_cursor
set @insertsql='insert into statistic_sb'+@user_id+' values('''+@eq_name+''','+@mainten_sum+')' execute(@insertsql)哪里错了, 提示为Syntax error converting the varchar value 'insert into statistic_sb29 values('计算机',' to a column of data type int.
类型转换错误呀! 你的@insertsql是什么类型?,而@mainten_sum又是什么类型??set @insertsql='insert into statistic_sb'+@user_id+' values('''+@eq_name+''','+str(@mainten_sum)+')'
select @bb = 29
select @css = N'select @c = (@a+@b)/2'
exec sp_executesql @css, N'@a int, @b int, @c int output', 10, 29, @bb output
select @bb as bb
select @css = 'select @c = (@a+@b)/2'
exec sp_executesql @css, '@a int, @b int, @c int output', 10, 29, @bb output
select @bb as bb 通过系统存储过程sp_executesql执行动态的SQL命令(公式保存在@css), 这个动态的SQL共有3个参数, 2个参数为input, 1个为output.....
declare @sx_sum int
set @sum1='select @a = count(*) as sx_sum from givemainten_tab where equip_name = '+@eq_name
exec sp_executesql @sum1, '@a int output', @sx_sum output
....这样, @xs_sum 就是你需要的一个count(*)值.
declare @sumSQL varchar(100)
declare @mainten_sum int declare statistic_cursor CURSOR for select equip_name from equipments_tab
open statistic_cursor
FETCH NEXT FROM statistic_cursor INTO @eq_name
WHILE(@@fetch_status=0)
begin
set @sumSQL = 'select @a = count(*) from givemainten_tab where equip_name = '''+@eq_name +''''
exec sp_executesql @sumSQL, '@a int output', @sx_sum output set @sumSQL = 'select @a = count(*) from gomainten_tab where equip_name = '''+@eq_name +''''
exec sp_executesql @sumSQL, '@a int output', @qx_sum output set @mainten_sum=sx_sum+qx_sum
insert into statisticequip_tab values (@eq_name,@mainten_sum) FETCH NEXT FROM statistic_cursor INTO @eq_name
end
close statistic_cursor
deallocate statistic_cursor
Cannot convert parameter '@statement' to ntext/nchar/nvarchar data type expected by procedure.
这是什么错误提示???
declare @sumSQL nvarchar(100)
declare @mainten_sum int declare statistic_cursor CURSOR for select equip_name from equipments_tab
open statistic_cursor
FETCH NEXT FROM statistic_cursor INTO @eq_name
WHILE(@@fetch_status=0)
begin
set @sumSQL = N'select @a = count(*) from givemainten_tab where equip_name = '''+@eq_name +''''
exec sp_executesql @sumSQL, '@a int output', @sx_sum output set @sumSQL = N'select @a = count(*) from gomainten_tab where equip_name = '''+@eq_name +''''
exec sp_executesql @sumSQL, N'@a int output', @qx_sum output set @mainten_sum=sx_sum+qx_sum
insert into statisticequip_tab values (@eq_name,@mainten_sum) FETCH NEXT FROM statistic_cursor INTO @eq_name
end
close statistic_cursor
deallocate statistic_cursor
declare @sumSQL varchar(300),@NsumSQL nvarchar(300)
declare @mainten_sum int declare statistic_cursor CURSOR for select equip_name from equipments_tab
open statistic_cursor
FETCH NEXT FROM statistic_cursor INTO @eq_name
WHILE(@@fetch_status=0)
begin
set @sumSQL = 'select @a = count(*) from givemainten_tab where equip_name = '''+@eq_name +''''
set @NsumSQL = convert(nvarchar(300),@sumSQL)
exec sp_executesql @NsumSQL, N'@a int output', @sx_sum output set @sumSQL = 'select @a = count(*) from gomainten_tab where equip_name = '''+@eq_name +''''
set @NsumSQL = convert(nvarchar(300),@sumSQL)
exec sp_executesql @NsumSQL, N'@a int output', @qx_sum output set @mainten_sum=sx_sum+qx_sum
insert into statisticequip_tab values (@eq_name,@mainten_sum) FETCH NEXT FROM statistic_cursor INTO @eq_name
end
close statistic_cursor
deallocate statistic_cursor
execute(@insertsql)哪里错了, 提示为Syntax error converting the varchar value 'insert into statistic_sb29 values('计算机',' to a column of data type int.
你的@insertsql是什么类型?,而@mainten_sum又是什么类型??set @insertsql='insert into statistic_sb'+@user_id+' values('''+@eq_name+''','+str(@mainten_sum)+')'