以下是之前写的一个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_typego
第二段插入明细数据和第三段更改值中的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_typego
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_noDECLARE @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
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) >10INSERT 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;