Extract Query Review

[hramos25]

Dear Sir or Madam,

I have the below query that extract data out of direct deposit and place it into another table, the query is not running for me. Woul you please review the query and assist me to find the problem. Appreciate your assistance in advance.

Thanks

James

declare @empid as nvarchar(10)
declare @Bankacct00 as nvarchar(17)
declare @Bankacct01 as nvarchar(17)
declare @Bankacct02 as nvarchar(17)
declare @Bankacct03 as nvarchar(17)
declare @Bankacct04 as nvarchar(17)
declare @Bankacct05 as nvarchar(17)
declare @transit00 as nvarchar(9)
declare @transit01 as nvarchar(9)
declare @transit02 as nvarchar(9)
declare @transit03 as nvarchar(9)
declare @transit04 as nvarchar(9)
declare @transit05 as nvarchar(9)
declare @fulldeposit as nvarchar(1) -- Leave blank for No, and 'Y' for yes
declare @Amount00 as float
declare @Amount01 as float
declare @Amount02 as float
declare @Amount03 as float
declare @Amount04 as float
declare @accttype00 as nvarchar(2)
declare @accttype01 as nvarchar(2)
declare @accttype02 as nvarchar(2)
declare @accttype03 as nvarchar(2)
declare @accttype04 as nvarchar(2)
declare @sql as nvarchar(255)
declare @counter as int
declare @acctcol as nvarchar(2)
declare @ssn as nvarchar(9)
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'xtable11') AND type in (N'U'))
drop table xtable11
create table xtable11
(empid nvarchar(7),
Bankacct00 as nvarchar(17)
Bankacct01 as nvarchar(17)
Bankacct02 as nvarchar(17)
Bankacct03 as nvarchar(17)
Bankacct04 as nvarchar(17)
Bankacct05 as nvarchar(17)
transit00 as nvarchar(9)
transit01 as nvarchar(9)
transit02 as nvarchar(9)
transit03 as nvarchar(9)
transit04 as nvarchar(9)
transit05 as nvarchar(9)
fulldeposit as nvarchar(1)
Amount00 as float
Amount01 as float
Amount02 as float
Amount03 as float
Amount04 as float
accttype00 as nvarchar(2)
accttype01 as nvarchar(2)
accttype02 as nvarchar(2)
accttype03 as nvarchar(2)
accttype04 as nvarchar(2))

declare xcrap11 cursor for
select d.empid,from dddepositor d inner join employee e
on d.empid = e.empid
where e.status = 'A'
order by d.empid
open xcrap11
fetch next from xcrap11 into @empid,
set @counter = 0
while (@@fetch_status = 0)
begin
labelloop:

if @counter > 0
begin
set @sql = 'insert into xtable11(accttype00,accttype01,accttype02,accttype03,accttype04,accttype05,
banktransit00,banktransit01,banktransit02,banktransit03,banktransit04,
bankacct01,bankacct01,bankacct02,bankacct03,bankacct04,bankacct05,
amount00,amount01,amount02,amount03,amount04)'
set @sql = @sql + 'select rtrim(ltrim(accttype00' + rtrim(ltrim(str(@counter))) + ')),'
set @sql = @sql + 'select rtrim(ltrim(accttype01' + rtrim(ltrim(str(@counter))) + ')),'
set @sql = @sql + 'select rtrim(ltrim(accttype02' + rtrim(ltrim(str(@counter))) + ')),'
set @sql = @sql + 'select rtrim(ltrim(accttype03' + rtrim(ltrim(str(@counter))) + ')),'
set @sql = @sql + 'select rtrim(ltrim(accttype04' + rtrim(ltrim(str(@counter))) + ')),'
set @sql = @sql + 'select rtrim(ltrim(accttype05' + rtrim(ltrim(str(@counter))) + ')),'
set @sql = @sql + ' rtrim(ltrim(banktransit00' + rtrim(ltrim(str(@counter)))+ ')),'
set @sql = @sql + ' rtrim(ltrim(banktransit01' + rtrim(ltrim(str(@counter)))+ ')),'
set @sql = @sql + ' rtrim(ltrim(banktransit02' + rtrim(ltrim(str(@counter)))+ ')),'
set @sql = @sql + ' rtrim(ltrim(banktransit03' + rtrim(ltrim(str(@counter)))+ ')),'
set @sql = @sql + ' rtrim(ltrim(banktransit04' + rtrim(ltrim(str(@counter)))+ ')),'
set @sql = @sql + ' rtrim(ltrim(banktransit05' + rtrim(ltrim(str(@counter)))+ ')),'
set @sql = @sql + ' rtrim(ltrim(bankacct00' + rtrim(ltrim(str(@counter)))+ ')),'
set @sql = @sql + ' rtrim(ltrim(bankacct01' + rtrim(ltrim(str(@counter)))+ ')),'
set @sql = @sql + ' rtrim(ltrim(bankacct02' + rtrim(ltrim(str(@counter)))+ ')),'
set @sql = @sql + ' rtrim(ltrim(bankacct03' + rtrim(ltrim(str(@counter)))+ ')),'
set @sql = @sql + ' rtrim(ltrim(bankacct04' + rtrim(ltrim(str(@counter)))+ ')),'
set @sql = @sql + ' rtrim(ltrim(bankacct05' + rtrim(ltrim(str(@counter)))+ ')),'
set @sql = @sql + ' amount00' + rtrim(ltrim(str(@counter - 1))) + ','
set @sql = @sql + ' amount01' + rtrim(ltrim(str(@counter - 1))) + ','
set @sql = @sql + ' amount02' + rtrim(ltrim(str(@counter - 1))) + ','
set @sql = @sql + ' amount03' + rtrim(ltrim(str(@counter - 1))) + ','
set @sql = @sql + ' amount04' + rtrim(ltrim(str(@counter - 1))) + ','
set @sql = @sql + '''''' + ','
set @sql = @sql + @empid + ','
set @sql = @sql + ' from dddepositor where empid = ' + @empid;
select EmpID as FileNBR,AcctType00 as accttype00,AcctType01 as accttype01,AcctType03 as accttype03,AcctType04 as accttype04,
AcctType05 as accttype05,BankTransit00 as TransitNBR00,BankTransit01 as TransitNBR01,BankTransit03 as TransitNBR00BankAcct03,BankTransit04 as TransitNBR04,BankTransit05 as TransitNBR05,BankAcct00 as ACCTNBR00,BankAcct01 as ACCTNBR01,
BankAcct02 as ACCTNBR00Amount02,BankAcct03 as ACCTNBR03,BankAcct04 as ACCTNBR04,BankAcct05 as ACCTNBR05,
Amount00 as DepositAMT00,Amount01 as DepositAMT01,Amount02 as DepositAMT02,Amount03 as DepositAMT03,Amount04 as DepositAMT04,
Case
When AcctType00 = 'c' then N'01'
When AcctType01 = 's' then N'12'
When AcctType02 = 'c' then N'10'
When AcctType03 = 's' then N'13'
When AcctType04 = 'c' then N'11'
When AcctType05 = 's' then N'14'
Else N' '
End
from dddepositor
Order by EmpId

 

[SQLSister]

YOur first problem is you used a cursor. Never ever use a cursor again. This is a very bad practice.

your next problem is you tried to use dynamic SQl but never executed the SQL string. That's ok becasue dynamic SQl is alos a very bad practice and should not ever be used especially in a banking database as it is very insecure and can lead to something call SQL injection attacks. I certainly wouldn't bank anywhere if I knew they used this kind of unsafe code. So get rid of all that as well.
All you need is the insert and the select statment that gets the records you want to insert. No variables, cursors or dynamic SQl should be in the statment at all.

"NOTHING is more important in a database than integrity." ESquared

 

[gmmastros]

Yeah... What sis said.

Also, you are NOT building your table properly.

[tt][blue]create table xtable11
(empid nvarchar(7),
Bankacct00 [!]as[/!] nvarchar(17)[!],[/!]
Bankacct01 [!]as[/!] nvarchar(17)[!],[/!]
Bankacct02 [!]as[/!] nvarchar(17)[!],[/!]
etc...[/blue][/tt]

You need to have a comma separating each column, and you need to remove the [!]as[/!] from each line.

Also.... NEVER use float to store dollar amounts. Floats (and real) are approximate number data types. Instead, you should use decimal. Something like.... Decimal(20,4)

Decimal(20,4) can store a range of numbers from:
[tt]9,999,999,999,999,999.9999 To 9,999,999,999,999,999.9999[/tt]



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[r937]

another problem: you are allowed to have only one SELECT keyword, but you have several --

set @sql = @sql + 'select rtrim(ltrim(accttype00' + rtrim(ltrim(str(@counter))) + ')),'
set @sql = @sql + 'select rtrim(ltrim(accttype01' + rtrim(ltrim(str(@counter))) + ')),'
set @sql = @sql + 'select rtrim(ltrim(accttype02' + rtrim(ltrim(str(@counter))) + ')),'
set @sql = @sql + 'select rtrim(ltrim(accttype03' + rtrim(ltrim(str(@counter))) + ')),'
set @sql = @sql + 'select rtrim(ltrim(accttype04' + rtrim(ltrim(str(@counter))) + ')),'
set @sql = @sql + 'select rtrim(ltrim(accttype05' + rtrim(ltrim(str(@counter))) + ')),'


r937.com | rudy.ca

 

[chrissie1]

Can we know the name of the bank you are working for so we can withdraw are money ASAP.

Christiaan Baes
Belgium

My Blog