存储过程，写一个存储过程，一个参数每次执行的时候需要手工输入

[fancydong]

以下是之前写的一个SQL语句，现在需要把这个语句写成存储进程
第二段插入明细数据和第三段更改值中的card_type = @card_type，需要每次执行这个存储进程的时候作为入参，需要操作者或者程序调用的时候传值过来



use testxx
go
/******************************备份原始表******************************************/
declare @table_no varchar(120)
select @table_no = 'bak'+replace(replace(replace(convert(char(19),getdate(),121),'-',''),':',''),' ','')
--select @table_no

DECLARE @sql NVARCHAR(MAX)
SET @sql='select * into ['+@table_no+'] from t_rm_vip_info'
EXEC (@sql);


/*****************************插入明细数据*******************************************/

---select * from t_rm_vip_info where dbo.f_get_descry_star(residual_amt) >10

---select card_id,dbo.f_get_descry_star(residual_amt) from t_rm_vip_info where dbo.f_get_descry_star(residual_amt) >10
use testxx
go
declare @card_type varchar(120)

set @card_type = '01'

insert into t_rm_saving_plus_record
(branch_no,card_id,plus_count,oper_id ,oper_date,old_count,
num1,other1,residual_amt,com_flag,plus_real_count,real_date,pay_way)
select '000',card_id, (10-dbo.f_get_descry_star(residual_amt)) as plus_count ,'1001',getdate(),dbo.f_get_descry_star(residual_amt),
'0','自动清除','0','0',(10-dbo.f_get_descry_star(residual_amt)) ,null,'RMB'
from t_rm_vip_info
where dbo.f_get_descry_star(residual_amt) >10 and card_type = @card_type


/*****************************更改值*******************************************/
update t_rm_vip_info set residual_amt = '<:66420.-' where dbo.f_get_descry_star(residual_amt) >10 and card_type = @card_type

go

[二月十六]

这个意思？

CREATE PROC dbo.pro_11
 @card_type NVARCHAR(120)
AS
/******************************备份原始表******************************************/
DECLARE @table_no VARCHAR(120);
SELECT @table_no = 'bak' + REPLACE(REPLACE(REPLACE(CONVERT(CHAR(19), GETDATE(), 121), '-', ''), ':', ''), ' ', '');
--select @table_no

DECLARE @sql NVARCHAR(MAX);
SET @sql = 'select * into [' + @table_no + '] from t_rm_vip_info';
EXEC (@sql);


/*****************************插入明细数据*******************************************/

---select * from t_rm_vip_info where dbo.f_get_descry_star(residual_amt) >10

---select card_id,dbo.f_get_descry_star(residual_amt)  from t_rm_vip_info where dbo.f_get_descry_star(residual_amt) >10


INSERT INTO t_rm_saving_plus_record
(
    branch_no,
    card_id,
    plus_count,
    oper_id,
    oper_date,
    old_count,
    num1,
    other1,
    residual_amt,
    com_flag,
    plus_real_count,
    real_date,
    pay_way
)
SELECT '000',
       card_id,
       (10 - dbo.f_get_descry_star(residual_amt)) AS plus_count,
       '1001',
       GETDATE(),
       dbo.f_get_descry_star(residual_amt),
       '0',
       '自动清除',
       '0',
       '0',
       (10 - dbo.f_get_descry_star(residual_amt)),
       NULL,
       'RMB'
FROM t_rm_vip_info
WHERE dbo.f_get_descry_star(residual_amt) > 10
      AND card_type = @card_type;


/*****************************更改值*******************************************/
UPDATE t_rm_vip_info
SET residual_amt = '<:66420.-'
WHERE dbo.f_get_descry_star(residual_amt) > 10
      AND card_type = @card_type;

GO

[中国风]

第1段改改简化一下语句，判断一下表存在时抛错 e.g.

USE testxx;
GO
CREATE PROC P1
(
@card_type VARCHAR(120)='01'
)
AS

/******************************备份原始表******************************************/
DECLARE @table_no VARCHAR(120);
SELECT @table_no='bak'+FORMAT(GETDATE(),'yyyyMMddHHmmss');
IF OBJECT_ID(@table_no,'U') IS NOT NULL
BEGIN
	RAISERROR(N'表名已存在',16,1)
	RETURN -1
END


DECLARE @sql NVARCHAR(MAX);
SET @sql='select * into ['+@table_no+'] from t_rm_vip_info';
EXEC (@sql);



/*****************************插入明细数据*******************************************/

---select * from t_rm_vip_info where dbo.f_get_descry_star(residual_amt) >10

---select card_id,dbo.f_get_descry_star(residual_amt)  from t_rm_vip_info where dbo.f_get_descry_star(residual_amt) >10



INSERT INTO t_rm_saving_plus_record (branch_no
                                   , card_id
                                   , plus_count
                                   , oper_id
                                   , oper_date
                                   , old_count
                                   , num1
                                   , other1
                                   , residual_amt
                                   , com_flag
                                   , plus_real_count
                                   , real_date
                                   , pay_way)
SELECT '000'
     , card_id
     , (10-dbo.f_get_descry_star(residual_amt)) AS plus_count
     , '1001'
     , GETDATE()
     , dbo.f_get_descry_star(residual_amt)
     , '0'
     , '自动清除'
     , '0'
     , '0'
     , (10-dbo.f_get_descry_star(residual_amt))
     , NULL
     , 'RMB'
FROM t_rm_vip_info
WHERE dbo.f_get_descry_star(residual_amt) >10
      AND card_type=@card_type;


/*****************************更改值*******************************************/
UPDATE t_rm_vip_info
SET residual_amt='<:66420.-'
WHERE dbo.f_get_descry_star(residual_amt) >10
      AND card_type=@card_type;

[fancydong]

出参怎么定义？