初学者急问,带输出参数的存储过程报错
//存储过程
CREATE PROCEDURE [dbo].[gettotalusetime]
@tablename varchar(20),
@starttime varchar(10),
@endtime varchar(10),
@usetime bigint output
AS
declare @strsql varchar(200)

set @strsql=' select @usetime = sum(time12) from '+@tablename+' where date2>='''+ltrim(@starttime)+''' and date2<='''+ltrim(@endtime)+''''

exec(@strsql)
GO
//调用
declare @tablename varchar(20)
declare @starttime varchar(10)
declare @endtime varchar(10)
declare @usetime bigint

set @tablename = 'Table09'
set @starttime = '2007/09/15'
set @endtime = '2007/09/15'
exec gettotalusetime @tablename,@starttime,@endtime,@usetime output
print @usetime
报错提示:
必须声明变量 '@usetime'。
请帮忙看看怎么改呢,谢谢!

[解决办法]

SQL code

CREATE PROCEDURE [dbo].[gettotalusetime] @tablename varchar(20),@starttime varchar(10),@endtime varchar(10),@usetime bigint  outputAS--declare @strsql varchar(200)declare @strsql nvarchar(400)set @strsql='select  @usetime = sum(time12)  from '+@tablename+'where date2 >='''+ltrim(@starttime)+'''and date2 <='''+ltrim(@endtime)+''''exec sp_executesql @strsql,N'@usetime bigint output',@usetime output--exec(@strsql)GO
[解决办法]
CREATE PROCEDURE [dbo].[gettotalusetime] 
@tablename nvarchar(20),  
@starttime nvarchar(10),  
@endtime nvarchar(10),  
@usetime bigint  output  
AS  
declare @strsql nvarchar(200)  

set @strsql= N' select  @usetime = sum(time12)  from  '+@tablename+ N' where date2 >= ' ' '+ltrim(@starttime)+ N' ' ' and date2 <= ' ' '+ltrim(@endtime)+ N' ' ' '  

exec sq_executesql @strsql,N'@usetime int output',@usetime output  
GO  

[解决办法]

动态sql语句基本语法  
1 :普通SQL语句可以用Exec执行  

eg:   Select * from tableName  
        Exec('select * from tableName')  
        Exec sp_executesql N'select * from tableName'    -- 请注意字符串前一定要加N  

2:字段名，表名，数据库名之类作为变量时，必须用动态SQL  

eg:  
declare @fname varchar(20)  
set @fname = 'FiledName'  
Select @fname from tableName              -- 错误,不会提示错误，但结果为固定值FiledName,并非所要。  
Exec('select ' + @fname + ' from tableName')     -- 请注意 加号前后的 单引号的边上加空格  

当然将字符串改成变量的形式也可  
declare @fname varchar(20)  
set @fname = 'FiledName' --设置字段名  

declare @s varchar(1000)  
set @s = 'select ' + @fname + ' from tableName'  
Exec(@s)                -- 成功  
exec sp_executesql @s   -- 此句会报错  



declare @s Nvarchar(1000)  -- 注意此处改为nvarchar(1000)  
set @s = 'select ' + @fname + ' from tableName'  
Exec(@s)                -- 成功    
exec sp_executesql @s   -- 此句正确  

3. 输出参数  
declare @num int, @sql nvarchar(4000)  
set @sql='select count(*) from tableName'  
exec(@sql)  


--如何将exec执行结果放入变量中？  

declare @num int, @sql nvarchar(4000)  
set @sql='select @a=count(*) from tableName '  
exec sp_executesql @sql,N'@a int output',@num output  
select @num  


[解决办法]
把不该替换的空格给替换了，CSDN会自动加很多空格，楼主下次最好用插入代码的方式贴出代码。

下面已更正：

SQL code
alter PROCEDURE [dbo].[gettotalusetime]  
 @tablename varchar(20), 
 @starttime varchar(10), 
 @endtime varchar(10), 
 @usetime bigint  output 
 AS 
 --declare @strsql varchar(200) 
 declare @strsql nvarchar(400)  
 
  
 set @strsql='select @usetime = sum(time12) from '+@tablename+' where date2 >='''+ltrim(@starttime)+''' and date2  <='''+ltrim(@endtime)+'''' 
 exec sp_executesql @strsql,N'@usetime bigint output',@usetime output 
  
 --exec(@strsql) 
 go