Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2007
    Posts
    29

    Unanswered: Dropping and re-adding characters on a variable.

    I've searched the forum and haven't really found anything to address my issue and with all the great advice I've gotten before I tought I would give it a shot and see if someone can point me in the right direction.

    I need to create a stored procedure that uses a variable to define what table data will be pulled from but the first two characters have to be dropped to pull the actual data and then re-assembled to populate another table.

    I tought a Temp table would work well but have no idea how to remove the first two characters from the variable and the re-add them in the temp table.

    The following stored procedure works from data retrival but the actual variable will have either a SF or SI prefix that is used to define the different tables.

    create procedure Test_getVocBillID

    @Bill_ID char(20)
    as
    create Table ##Test_VocBillIds
    (BillID char(20),
    InvoiceNo char(20),
    FName char(30),
    LName Char(35),
    InvoiceDate datetime,
    SubmitDate datetime)

    Insert into ##Test_VocBillIds
    select
    a.ID as Bill_ID,

    b.ID as INVOICE_NO,

    c.FirstName as FIRST_NAME,

    c.LastName as LAST_NAME ,

    b.Create_Date as INVOICEDATE,

    a.Create_Date as SUBMITDATE



    FROM
    colucw12.RehabTrack.dbo.C19_Invoice_Header as a

    inner join
    colucw12.RehabTrack.dbo.C19_Invoice_Header_BAM as b
    on a.Invoice_Header_BAM_ID=b.ID
    inner join
    colucw12.RehabTrack.dbo.IW_Demo as c
    on a.IW_DEMO_ID=c.ID

    WHERE
    A.id=@Bill_ID

    select

    BillID ,
    InvoiceNo,
    FName,
    LName,
    CONVERT(varchar(10),InvoiceDate,101) as INVOICE_DATE,
    CONVERT(varchar(10),SubmitDate,101) as SUBMIT_DATE

    From
    ##Test_VocBillIds

    Drop Table ##Test_VocBillIds

    The second set of tables are SI_Invoice_header and SI_Invoice_Header_bam
    with the same fields.

    Any help is appreciated.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    SELECT @variable
         , Len(@variable)
         , Len(@variable) - 2
         , Right(@variable, Len(@variable) - 2)
    George
    Home | Blog

Posting Permissions

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