存储过程返回值的问题

[zzxing]

CREATE PROCEDURE sp_month
@str_year varchar(4),
@str_month varchar(2),
@str_day varchar(2),
@amt_purch numeric(9,2) OUTPUT,
@amt_sale numeric(9,2) OUTPUT
AS
begin
declare @tb_purch varchar(10)
declare @tb_sale varchar(10)
declare @purch_sql varchar(500)
declare @sale_sql varchar(500)
declare @day varchar(2)

select @tb_purch = 'purch_'+@str_year;
select @tb_sale = 'sale_'+@str_year;
select @day = '01'
select @purch_sql ='select sum(qty * price) from '+@tb_purch+' where purch_date >= cast(str('+@str_year+@str_month+@day+') as datetime) and purch_date <= cast(str('+@str_year+@str_month+@str_day+') as datetime) ;'
select @sale_sql = 'select sum(qty * price) from '+@tb_sale+' where sale_date >= cast(str('+@str_year+@str_month+@day+') as datetime) and sale_date <= cast(str('+@str_year+@str_month+@str_day+') as datetime) ;'

EXEC (@purch_sql)
EXEC (@sale_sql)

end
GO

//------------------------------------
1,请问怎样将 EXEC (@purch_sql) 的值赋给@amt_purch, EXEC (@sale_sql)的值赋给@amt_sale
2,或者不用返回参数直接执行过程也能得到两个值，但在调用程序中怎么取这两个值？

[dawugui]

D. 使用 OUTPUT 参数
OUTPUT 参数允许外部过程、批处理或多条 Transact-SQL 语句访问在过程执行期间设置的某个值。下面的示例创建一个存储过程 (titles_sum)，并使用一个可选的输入参数和一个输出参数。

首先，创建过程：

USE pubs
GO
IF EXISTS(SELECT name FROM sysobjects
WHERE name = 'titles_sum' AND type = 'P')
DROP PROCEDURE titles_sum
GO
USE pubs
GO
CREATE PROCEDURE titles_sum @@TITLE varchar(40) = '%', @@SUM money OUTPUT
AS
SELECT 'Title Name' = title
FROM titles
WHERE title LIKE @@TITLE
SELECT @@SUM = SUM(price)
FROM titles
WHERE title LIKE @@TITLE
GO

接下来，将该 OUTPUT 参数用于控制流语言。



说明 OUTPUT 变量必须在创建表和使用该变量时都进行定义。


参数名和变量名不一定要匹配，不过数据类型和参数位置必须匹配（除非使用 @@SUM = variable 形式）。

DECLARE @@TOTALCOST money
EXECUTE titles_sum 'The%', @@TOTALCOST OUTPUT
IF @@TOTALCOST < 200
BEGIN
PRINT ' '
PRINT 'All of these titles can be purchased for less than $200.'
END
ELSE
SELECT 'The total cost of these titles is $'
+ RTRIM(CAST(@@TOTALCOST AS varchar(20)))

下面是结果集：

Title Name
------------------------------------------------------------------------
The Busy Executive's Database Guide
The Gourmet Microwave
The Psychology of Computer Cooking

(3 row(s) affected)

Warning, null value eliminated from aggregate.

All of these titles can be purchased for less than $200.

[中国风]

CREATE   PROCEDURE   sp_month     
			@str_year   varchar(4), 
			@str_month   varchar(2), 
			@str_day   varchar(2), 
			@amt_purch   numeric(9,2)   OUTPUT,     
			@amt_sale   numeric(9,2)   OUTPUT     
		AS 
begin 
declare   @tb_purch   varchar(10) 
declare   @tb_sale   varchar(10) 
declare   @purch_sql   nvarchar(500) --改为nvarchar
declare   @sale_sql   nvarchar(500) --改为nvarchar
declare   @day   varchar(2) 

select   @tb_purch   =   'purch_ '+@str_year; 
select   @tb_sale   =   'sale_ '+@str_year; 
select   @day   =   '01 ' 
select   @purch_sql   = 'select   @amt_purch=sum(qty   *   price)   from   '+@tb_purch+ '   where   purch_date   > =   cast(str( '+@str_year+@str_month+@day+ ')   as   datetime)   and   purch_date   <=     cast(str( '+@str_year+@str_month+@str_day+ ')   as   datetime)   ; ' 
select   @sale_sql   =   'select   @amt_sale=sum(qty   *   price)   from   '+@tb_sale+ '   where   sale_date   > =   cast(str( '+@str_year+@str_month+@day+ ')   as   datetime)   and   sale_date   <=   cast(str( '+@str_year+@str_month+@str_day+ ')   as   datetime)   ; ' 

--EXEC   (@purch_sql) 
--EXEC   (@sale_sql)   
exec sp_executesql @purch_sql,N'@amt_purch numeric(9,2) output'
,@amt_purch  output
exec sp_executesql @purch_sql,N'@amt_sale numeric(9,2) output',@amt_sale output

select @amt_purch,@amt_sale
end 
GO 

[中国风]

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

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


--------------------------------------------------------------------------------

动态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, 
        @sqls nvarchar(4000) 
set @sqls='select count(*) from tableName' 
exec(@sqls) 
--如何将exec执行结果放入变量中？ 

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