stored procedure newbie - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

stored procedure newbie

Author

Topic

belcherman
Starting Member

5 Posts

Posted - 2007-12-14 : 21:52:46

I am trying to write a to do the following below:

The printed check file has a name that looks like this: <removed>.PDF.
In the example name above, <removed> is the check number, <removed> is the routing number and <removed> is the account number.

The letters define the piece of data that follows.
A = Check number
B = Routing number
C = Account number

Steps:
a. stored procedure to pull the check number, routing number and account number from tblCheckRegister.
b. Join them together in proper format.
b. Once it has done that, do a wildcard search on a table called tblSIValidate column SIBarCode.
c. The return value should be the entire SIBarCode column. Append the .PDF to it and look in the same image directory as the cancelled check.

I am used to VB.net and vbscripting and the syntax there. I am sure there are things below in the stored procedure that are not correct.
Any help is appreciated.

---------------------------------------------------------------
STORED PROCEDURE
---------------------------------------------------------------

/*
Name: usp_GetCheckImage
Description: Gets Printed Check
Author: Mike
Modification Log: Change

Description Date Changed By
Created procedure 12/15/2007 Mike Belcher
*/

CREATE PROCEDURE dbo.rx_bhd_GetCheckImage
@maillogid int,
PrintedCheckFile OUTPUT
AS

SET NOCOUNT ON

/* Get Check#, Routing#, and Account# */
SELECT CheckNo, CPRoutingNumber, CPAccountID
FROM tblCheckRegister
WHERE maillogid = @maillogid

/* Combine Check#, Routing#, and Account# into proper format */
declare @PrintedCheck char(255)

@PrintedCheck = "a"

@PrintedCheck = "A"
@PrintedCheck = @PrintedCheck & CheckNo

@PrintedCheck = @PrintedCheck & "B"
@PrintedCheck = @PrintedCheck & CPRoutingNumber

@PrintedCheck = @PrintedCheck & "C"
@PrintedCheck = @PrintedCheck & PAccountID

/*
if @PrintedCheck = null
Give dummy value
*/

/* Search tblSIValidate column SIBarCode combined PrintedCheck# Example: "<removed>" */
SELECT SIBarCode
FROM tblSIValidate
WHERE SIBarCode LIKE @PrintedCheck

/* Need to return the @PrintedCheckFile */
SET @PrintedCheckFile = SIBarCode & ".pdf"

GO

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-12-14 : 22:59:19

there are syntax errors in this. Did you try running this script?

elsasoft.org

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-12-15 : 23:02:30

just noticed there is a real and valid bank routing number for a bank in maryland in the above post.

first, for the moderators: someone might want to edit the OP to remove these pieces:

1. routing number
2. check number
3. bank acct number

second, for the OP: wtf?

elsasoft.org

belcherman
Starting Member

5 Posts

Posted - 2007-12-19 : 17:00:21

They are fake numbers

belcherman
Starting Member

5 Posts

Posted - 2007-12-19 : 17:03:55

they are fake numbers

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-12-20 : 08:37:30

well, that's good.

I ran the original routing number you posted through this page and got a hit, so I assumed they were real:
http://www.gregthatcher.com/Financial/Default.aspx

elsasoft.org

belcherman
Starting Member

5 Posts

Posted - 2007-12-21 : 18:10:28

Maybe I should play the lotto. If I could use that talent for making up numbers and win I would be able to retire. I am almost having this work. I still have one issue left.

Manually doing a query in SQL server itself produces a result for the following:

SELECT SIBarCode
FROM tblValidate
WHERE (SIBarCode LIKE '%A11111B222222222C3333333333%')

But in the stored procedure no result is returned.
@PrintedCheck does have value like the folowing A11111B222222222C3333333333 in the manual search.

declare @PrintedCheckBuild char(255)

SELECT @PrintedCheckBuild=SIBarCode
FROM tblValidate
WHERE SIBarCode LIKE '%' + @PrintedCheck + '%'

/* Need to return the @PrintedCheckFile */
SET @PrintedCheckFile = @PrintedCheckBuild

Any ideas why the same query would return results in SQL QUERY and not in the stored procedure?

Thanks.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-12-21 : 22:45:16

Use varchar instead of char for your declare.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources