CREATE PROC p_voucher_setsql
@vouchertype varchar(20),
@jd VARCHAR(20),
@accountnum VARCHAR(20),
@sql_out varchar(MAX) out
AS
begin
/***
DECLARE @sql_out VARCHAR(MAX) = ''
EXEC p_voucher_setsql 'ACC','借','10001',@sql_out output
print @sql_out
***/
--借
DECLARE @col VARCHAR(30),@col_rel VARCHAR(500),@coltype VARCHAR(20)
DECLARE @sql VARCHAR(MAX) = '',@ins_sql VARCHAR(MAX) = ''
DECLARE @frosql VARCHAR(MAX),@whersql VARCHAR(MAX),@groupsql varchar(200)
SELECT @frosql = frosql,@whersql = whersql,@groupsql = groupby
FROM dbo.d_u8voucher_set WHERE vouchertype = 'ACC'
declare B_Cursor cursor for select col,col_rel,coltype FROM dbo.d_u8voucher_rel WHERE jd = @jd AND vouchertype = @vouchertype AND accountnum = @accountnum
open B_Cursor;
fetch next from B_Cursor into @col ,@col_rel,@coltype
while @@FETCH_STATUS =0
BEGIN
SET @ins_sql = @ins_sql + @col + ','
SET @sql = @sql + @col_rel + ' as ' + @col + ','
fetch next from B_Cursor into @col ,@col_rel,@coltype
end
close B_Cursor;
deallocate B_Cursor;
SET @sql = LEFT(@sql,LEN(@sql) - 1 )
SET @ins_sql = LEFT(@ins_sql,LEN(@ins_sql) -1 ) + ')'
SET @ins_sql = 'insert into d_u8voucher_list (' + @ins_sql + CHAR(13)
SET @sql = 'select ' + @sql + ' ' + CHAR(13) + @frosql + ' ' + @whersql + ' ' + @groupsql
SET @sql_out = @ins_sql + @sql
END
@vouchertype varchar(20),
@jd VARCHAR(20),
@accountnum VARCHAR(20),
@sql_out varchar(MAX) out
AS
begin
/***
DECLARE @sql_out VARCHAR(MAX) = ''
EXEC p_voucher_setsql 'ACC','借','10001',@sql_out output
print @sql_out
***/
--借
DECLARE @col VARCHAR(30),@col_rel VARCHAR(500),@coltype VARCHAR(20)
DECLARE @sql VARCHAR(MAX) = '',@ins_sql VARCHAR(MAX) = ''
DECLARE @frosql VARCHAR(MAX),@whersql VARCHAR(MAX),@groupsql varchar(200)
SELECT @frosql = frosql,@whersql = whersql,@groupsql = groupby
FROM dbo.d_u8voucher_set WHERE vouchertype = 'ACC'
declare B_Cursor cursor for select col,col_rel,coltype FROM dbo.d_u8voucher_rel WHERE jd = @jd AND vouchertype = @vouchertype AND accountnum = @accountnum
open B_Cursor;
fetch next from B_Cursor into @col ,@col_rel,@coltype
while @@FETCH_STATUS =0
BEGIN
SET @ins_sql = @ins_sql + @col + ','
SET @sql = @sql + @col_rel + ' as ' + @col + ','
fetch next from B_Cursor into @col ,@col_rel,@coltype
end
close B_Cursor;
deallocate B_Cursor;
SET @sql = LEFT(@sql,LEN(@sql) - 1 )
SET @ins_sql = LEFT(@ins_sql,LEN(@ins_sql) -1 ) + ')'
SET @ins_sql = 'insert into d_u8voucher_list (' + @ins_sql + CHAR(13)
SET @sql = 'select ' + @sql + ' ' + CHAR(13) + @frosql + ' ' + @whersql + ' ' + @groupsql
SET @sql_out = @ins_sql + @sql
END