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

    Unanswered: has no parameters and arguments were supplied

    I'm kind of new at doing something like this. I'm trying to pull data into a results that exists in one of two databases. It works fine with query analyzer
    but gives me the error "has no parameters and arguments were supplied" when I try to convert it to a stored procedure.

    The procedure is as follows, any help would be appreciated.

    CREATE PROCEDURE sp_getInvoiceNoTest AS

    declare @InVoiceNo as VarChar(30)

    delete From Invoice_NBR



    Insert into Invoice_NBR (Tran_NBr,ADDR_Name,ADDR_Line2,ADDR_Line3,CITY_NAM E,State_Name,ADDR_NAME2,ADDR_LINE4,ADDR_LINE5,CITY _NAME2,State_Name2)



    select a.TranNo,b.AddrLine1,b.AddrLine2,b.AddrLine3,b.cit y,b.StateID,c.AddrName,c.AddrLine2,c.AddrLine3,c.C ity,c.StateID
    from Colucw17.Acuity_App.dbo.tarInvoice as a
    inner join
    Colucw17.Acuity_App.dbo.tciAddress as b
    on a.BilltoAddrKey=b.AddrKey
    inner join
    Colucw17.Acuity_App.dbo.tciAddress as c
    on a.BilltoAddrKey=c.AddrKey and a.BilltoAddrKey=a.BilltoCustAddrKey
    inner join
    Colucw17.Acuity_App.dbo.tarCustomer as d
    on a.CustKey=d.CustKey
    inner join
    Colucw17.Acuity_App.dbo.tciContact as f
    on a.confirmtoCntctKey=f.CntctKey
    where a.CreateuserID<>'admin' and a.TranNo='@InvoiceNo'



    --Insert into Invoice_NBr (Tran_NBr,ADDR_Name,ADDR_Line2,ADDR_Line3,CITY_NAM E,State_Name,ADDR_NAME2,ADDR_LINE4,ADDR_LINE5,CITY _NAME2,State_Name2)


    select a.TranNo,b.AddrLine1,b.AddrLine2,b.AddrLine3,b.cit y,b.StateID,c.AddrName,c.AddrLine2,c.AddrLine3,c.C ity,c.StateID
    from Colucw17.CSM_App.dbo.tarInvoice as a
    inner join
    Colucw17.CSM_App.dbo.tciAddress as b
    on a.BilltoAddrKey=b.AddrKey
    inner join
    Colucw17.CSM_App.dbo.tciAddress as c
    on a.BilltoAddrKey=c.AddrKey and a.BilltoAddrKey=a.BilltoCustAddrKey
    inner join
    Colucw17.CSM_App.dbo.tarCustomer as d
    on a.CustKey=d.CustKey
    inner join
    Colucw17.CSM_App.dbo.tciContact as f
    on a.confirmtoCntctKey=f.CntctKey
    where a.CreateuserID<>'admin' and a.TranNo='@InvoiceNo'
    GO

  2. #2
    Join Date
    Apr 2007
    Posts
    183
    CREATE PROCEDURE sp_getInvoiceNoTest AS
    (
    @InVoiceNo as VarChar(30)
    )
    as
    set nocount on

    delete From Invoice_NBR


    Insert into Invoice_NBR (Tran_NBr,ADDR_Name,ADDR_Line2,ADDR_Line3,CITY_NAM E,State_Name,ADDR_NAME2,ADDR_LINE4,ADDR_LINE5,CITY _NAME2,State_Name2)



    select a.TranNo,b.AddrLine1,b.AddrLine2,b.AddrLine3,b.cit y,b.StateID,c.AddrName,c.AddrLine2,c.AddrLine3,c.C ity,c.StateID
    from Colucw17.Acuity_App.dbo.tarInvoice as a
    inner join
    Colucw17.Acuity_App.dbo.tciAddress as b
    on a.BilltoAddrKey=b.AddrKey
    inner join
    Colucw17.Acuity_App.dbo.tciAddress as c
    on a.BilltoAddrKey=c.AddrKey and a.BilltoAddrKey=a.BilltoCustAddrKey
    inner join
    Colucw17.Acuity_App.dbo.tarCustomer as d
    on a.CustKey=d.CustKey
    inner join
    Colucw17.Acuity_App.dbo.tciContact as f
    on a.confirmtoCntctKey=f.CntctKey
    where a.CreateuserID<>'admin' and a.TranNo=@InvoiceNo



    --Insert into Invoice_NBr (Tran_NBr,ADDR_Name,ADDR_Line2,ADDR_Line3,CITY_NAM E,State_Name,ADDR_NAME2,ADDR_LINE4,ADDR_LINE5,CITY _NAME2,State_Name2)


    select a.TranNo,b.AddrLine1,b.AddrLine2,b.AddrLine3,b.cit y,b.StateID,c.AddrName,c.AddrLine2,c.AddrLine3,c.C ity,c.StateID
    from Colucw17.CSM_App.dbo.tarInvoice as a
    inner join
    Colucw17.CSM_App.dbo.tciAddress as b
    on a.BilltoAddrKey=b.AddrKey
    inner join
    Colucw17.CSM_App.dbo.tciAddress as c
    on a.BilltoAddrKey=c.AddrKey and a.BilltoAddrKey=a.BilltoCustAddrKey
    inner join
    Colucw17.CSM_App.dbo.tarCustomer as d
    on a.CustKey=d.CustKey
    inner join
    Colucw17.CSM_App.dbo.tciContact as f
    on a.confirmtoCntctKey=f.CntctKey
    where a.CreateuserID<>'admin' and a.TranNo=@InvoiceNo
    GO

  3. #3
    Join Date
    Aug 2007
    Posts
    29
    Using the code you supplied it generated the following error message when I checked the syntax:

    . .
    Error 170: Line 3: Incorrect syntax near @InvoiceNo.
    Line 11: Incorrect syntax near
    Must declare the variable :@Invo!ceNo:
    Must declare the variable @InvoiceNo.

    I added the declare statement but iot still errored.

  4. #4
    Join Date
    Apr 2007
    Posts
    183
    drop the AS between @InvoiceNo As VARCHAR

  5. #5
    Join Date
    Aug 2007
    Posts
    29
    Thanks Peso, that was it. You help is greatly appreciated. Hopefully, I'll get the hang of this stuff.

Posting Permissions

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