Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2007
    Posts
    18

    Question Unanswered: stored procedure newbie

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

    The printed check file has a name that looks like this: A111111B222222222C3333333333D50E50.PDF.*
    In the example name above, 111111 is the check number, 2222222222 is the routing number and 3333333333 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: "A111111B222222222C3333333333" */
    SELECT SIBarCode
    FROM tblSIValidate
    WHERE SIBarCode LIKE @PrintedCheck



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


    GO
    Last edited by Pat Phelan; 12-16-07 at 01:13.

  2. #2
    Join Date
    Feb 2003
    Location
    India
    Posts
    216
    u will have to hold the column values in local variable for use. like

    declare @ChqNo varchar(50)
    declare @RoutNo varchar(50)
    declare @AccNo varchar(50)

    SELECT @ChqNo=CheckNo, @RoutNo=CPRoutingNumber, @AccNo=CPAccountID
    FROM tblCheckRegister
    WHERE maillogid = @maillogid

    --now they can be used the way u want

    set @PrintedCheck = 'A' + @ChqNo + 'B' + @RoutNo + 'C' + @AccNo

  3. #3
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    Quote Originally Posted by belcherman
    In the example name above, 111111 is the check number, 222222222 is the routing number and 3333333333 is the account number.
    I really really hope those aren't real...

    if they are, can you post what bank you work for? Just want to be sure I don't have any money there.

    Last edited by Pat Phelan; 12-16-07 at 01:14.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I've edited two of the posts in this thread to hide the ABA routing number, the checking account number, and the check number. Live banking information should never be published because it can be easily misused, the liability is potentially huge even though this information is readily available to the financial industry.

    -PatP

  5. #5
    Join Date
    Sep 2007
    Posts
    18
    They are fake numbers I made up. I should put that at the top I guess next time.
    Last edited by belcherman; 12-19-07 at 22:50.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •