Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie

Stored Proc error

Options

Comments

  • Options
    #2
    kayos
    Registered Users Posts: 2,494 ✭✭✭kayos


    The Mighty Dubs wrote: »
    CREATE PROCEDURE [dbo].[RQCurrencies]
    (
    @RQCurrency char OUTPUT
    @RQCurrencyVal float OUTPUT
    @RQCurrencyID INT OUTPUT
    @CounterID INT OUTPUT
    )


    Should be 
    CREATE PROCEDURE [dbo].[RQCurrencies]
(
@RQCurrency char OUTPUT,
@RQCurrencyVal float OUTPUT,
@RQCurrencyID INT OUTPUT,
@CounterID INT OUTPUT
)

    After that I'm looking at your porc and wondering WTF. Updates without where's ???


  • Options
    #3
    Trampas
    Registered Users Posts: 7,680 ✭✭✭Trampas


    The Mighty Dubs wrote: »
    Hi,
    I am trying to create a stored proc (see below) (SQL Server 2000) but Im getting an error for
    line 4: Incorrect syntax near '@RQCurrency'

    Basically i need 4 bits of information to be entered into tblRQRates and 1 piece into tblUnitmatrix. This occurs when an insert form is filled and entered. any ideas?

    CREATE PROCEDURE [dbo].[RQCurrencies]
    (
    @RQCurrency char OUTPUT
    @RQCurrencyVal float OUTPUT
    @RQCurrencyID INT OUTPUT
    @CounterID INT OUTPUT
    )

    , after the outputs


  • Options
    #4
    Delirium
    Moderators Posts: 51,799 ✭✭✭✭Delirium


    The Mighty Dubs wrote: »
    Hi,
    I am trying to create a stored proc (see below) (SQL Server 2000) but Im getting an error for
    line 4: Incorrect syntax near '@RQCurrency'

    Basically i need 4 bits of information to be entered into tblRQRates and 1 piece into tblUnitmatrix. This occurs when an insert form is filled and entered. any ideas?

    CREATE PROCEDURE [dbo].[RQCurrencies]
    (
    @RQCurrency char OUTPUT
    @RQCurrencyVal float OUTPUT
    @RQCurrencyID INT OUTPUT
    @CounterID INT OUTPUT
    )
    AS

    DECLARE @EuroVal Float
    DECLARE @SterlingVal Float
    DECLARE @DollarVal Float
    DECLARE @CounterID int
    DECLARE @RQCurrencyVal Float
    DECLARE @RQCurrencyID INT


    SELECT @CounterID = CounterValue from tblcounter
    SELECT @RQCurrencyID = CounterValue from tblcounter
    SELECT @RQCurrencyVal = CurrencyVal from tblCurrencyIE
    SELECT @RQCurrency = Currency from tblCurrencyIE

    SELECT @EuroVal=CurrenyVal from tblCurrencyIE Where Currency = 'Euro'
    SELECT @SterlingVal=CurrenyVal from tblCurrencyIE Where Currency = 'Sterling'
    SELECT @DollarVal=CurrenyVal from tblCurrencyIE Where Currency = 'Dollar'

    UPDATE TBLCOUNTER
    SET CounterValue=CounterValue+1
    WHERE COUNTERID=1

    UPDATE TBLRQRates
    SET RQCurrency = 'Euro',
    RQCurrencyVal = @EuroVal,
    RQCurrencyID = CounteriD

    UPDATE TBLRQRates
    SET RQCurrency = 'Sterling' ,
    RQCurrencyVal = @SterlingVal,
    RQCurrencyID = CounteriD

    UPDATE TBLRQRates
    SET RQCurrency = 'Dollar' ,
    RQCurrencyVal = @DollarVal,
    RQCurrencyID = CounteriD

    RETURN @CounterID
    RETURN @RQCurrency
    RETURN @RQCurrencyVal
    RETURN @RQCurrencyID

    GO

    Also, in your update statements, as in bold above.
    UPDATE TBLRQRates
SET RQCurrency = 'Dollar' ,
RQCurrencyVal = @DollarVal,
RQCurrencyID = @CounteriD

    Plus you have no WHERE clause specified so each update will overwrite the data in every record in table!!!

    If you can read this, you're too close!



  • Options
    #5
    The Mighty Dubs
    Registered Users Posts: 224 ✭✭The Mighty Dubs


    after getting a fresh load of errors after including the , after the outputs

    Error 170:Line 7: Incorrect syntax near ')'.
    The variable name '@CounterID' has been declared. Variable names must be unique within a query batch or stored procedure.
    The variable name '@RQCurrencyVal' has been declared. Variable names must be unique within a query batch or stored procedure.
    The variable name '@RQCurrencyID' has been declared. Variable names must be unique within a query batch or stored procedure.

    See updated Proc below


    CREATE PROCEDURE [dbo].[RQCurrencies]
    (
    @RQCurrency char OUTPUT,
    @RQCurrencyVal float OUTPUT,
    @RQCurrencyID INT OUTPUT,
    @CounterID INT OUTPUT
    )
    AS

    DECLARE @EuroVal Float
    DECLARE @SterlingVal Float
    DECLARE @DollarVal Float
    DECLARE @CounterID int
    DECLARE @RQCurrencyVal Float
    DECLARE @RQCurrencyID INT


    SELECT @CounterID = CounterValue from tblcounter
    SELECT @RQCurrencyID = CounterValue from tblcounter
    SELECT @RQCurrencyVal = CurrencyVal from tblCurrencyIE
    SELECT @RQCurrency = Currency from tblCurrencyIE

    SELECT @EuroVal=CurrenyVal from tblCurrencyIE Where Currency = 'Euro'
    SELECT @SterlingVal=CurrenyVal from tblCurrencyIE Where Currency = 'Sterling'
    SELECT @DollarVal=CurrenyVal from tblCurrencyIE Where Currency = 'Dollar'

    UPDATE TBLCOUNTER
    SET CounterValue=CounterValue+1
    WHERE COUNTERID=1

    UPDATE TBLRQRates
    SET RQCurrency = 'Euro',
    RQCurrencyVal = @EuroVal,
    RQCurrencyID = CounteriD

    UPDATE TBLRQRates
    SET RQCurrency = 'Sterling' ,
    RQCurrencyVal = @SterlingVal,
    RQCurrencyID = CounteriD

    UPDATE TBLRQRates
    SET RQCurrency = 'Dollar' ,
    RQCurrencyVal = @DollarVal,
    RQCurrencyID = CounteriD

    RETURN @CounterID
    RETURN @RQCurrency
    RETURN @RQCurrencyVal
    RETURN @RQCurrencyID

    GO


  • Options
    #6
    Delirium
    Moderators Posts: 51,799 ✭✭✭✭Delirium


    The Mighty Dubs wrote: »
    after getting a fresh load of errors after including the , after the outputs

    Error 170:Line 7: Incorrect syntax near ')'.

    The variable name '@CounterID' has been declared. Variable names must be unique within a query batch or stored procedure.

    The variable name '@RQCurrencyVal' has been declared. Variable names must be unique within a query batch or stored procedure.
    The variable name '@RQCurrencyID' has been declared. Variable names must be unique within a query batch or stored procedure.

    See updated Proc below


    CREATE PROCEDURE [dbo].[RQCurrencies]
    (
    @RQCurrency char OUTPUT,
    @RQCurrencyVal float OUTPUT,
    @RQCurrencyID INT OUTPUT,

    @CounterID INT OUTPUT

    )
    AS

    DECLARE @EuroVal Float
    DECLARE @SterlingVal Float
    DECLARE @DollarVal Float

    DECLARE @CounterID int

    DECLARE @RQCurrencyVal Float
    DECLARE @RQCurrencyID INT
    ......


    UPDATE TBLRQRates
    SET RQCurrency = 'Dollar' ,
    RQCurrencyVal = @DollarVal,
    RQCurrencyID = CounteriD

    RETURN @CounterID
    RETURN @RQCurrency
    RETURN @RQCurrencyVal
    RETURN @RQCurrencyID

    GO

    The warnings are actually telling you what is wrong.;)

    If you can read this, you're too close!



  • Advertisement
  • Options
    #7
    The Mighty Dubs
    Registered Users Posts: 224 ✭✭The Mighty Dubs


    Cheers for that. I was actually naming them wrong RQCurrenyVal should have been RQCurrencyVal. And you are right about the Update statement, need to change that to an Insert when inserting into the tblRQRates. Still getting errors though

    Server: Msg 207, Level 16, State 3, Procedure RQCurrencies, Line 28
    Invalid column name 'CounterID'.
    Server: Msg 207, Level 16, State 1, Procedure RQCurrencies, Line 33
    Invalid column name 'CounterID'.
    Server: Msg 207, Level 16, State 1, Procedure RQCurrencies, Line 38
    Invalid column name 'CounterID'.


    ***********************

    CREATE PROCEDURE [dbo].[RQCurrencies]
    (
    @RQCurrency char OUTPUT,
    @RQCurrencyVal float OUTPUT,
    @RQCurrencyID INT OUTPUT

    )
    AS

    DECLARE @EuroVal Float
    DECLARE @SterlingVal Float
    DECLARE @DollarVal Float
    DECLARE @COunterid int

    SELECT @CounterID = CounterValue from tblcounter
    SELECT @RQCurrencyID = CounterValue from tblcounter
    SELECT @RQCurrencyVal = CurrencyVal from tblCurrencyIE
    SELECT @RQCurrency = Currency from tblCurrencyIE

    SELECT @EuroVal=CurrencyVal from tblCurrencyIE Where Currency = 'Euro'
    SELECT @SterlingVal=CurrencyVal from tblCurrencyIE Where Currency = 'Sterling'
    SELECT @DollarVal=CurrencyVal from tblCurrencyIE Where Currency = 'Dollar'

    UPDATE TBLCOUNTER
    SET CounterValue=CounterValue+1
    WHERE COUNTERID=1

    UPDATE TBLRQRates
    SET RQCurrency = 'Euro',
    RQCurrencyVal = @EuroVal,
    RQCurrencyID = CounterID

    UPDATE TBLRQRates
    SET RQCurrency = 'Sterling',
    RQCurrencyVal = @SterlingVal,
    RQCurrencyID = CounterID

    UPDATE TBLRQRates
    SET RQCurrency = 'Dollar',
    RQCurrencyVal = @DollarVal,
    RQCurrencyID = CounterID

    RETURN @CounterID
    RETURN @RQCurrency
    RETURN @RQCurrencyVal
    RETURN @RQCurrencyID

    GO


  • Options
    #8
    Delirium
    Moderators Posts: 51,799 ✭✭✭✭Delirium


    The Mighty Dubs wrote: »
    Cheers for that. I was actually naming them wrong RQCurrenyVal should have been RQCurrencyVal. And you are right about the Update statement, need to change that to an Insert when inserting into the tblRQRates. Still getting errors though

    Server: Msg 207, Level 16, State 3, Procedure RQCurrencies, Line 28
    Invalid column name 'CounterID'.
    Server: Msg 207, Level 16, State 1, Procedure RQCurrencies, Line 33
    Invalid column name 'CounterID'.
    Server: Msg 207, Level 16, State 1, Procedure RQCurrencies, Line 38
    Invalid column name 'CounterID'.
    Actually suggested that could be one of two potential problems earlier in the thread. Link

    If you can read this, you're too close!



  • Options
    #9
    The Mighty Dubs
    Registered Users Posts: 224 ✭✭The Mighty Dubs


    OK, im now ready to throw the PC out the window ;-) You gotta love Mondays. I need to convert these from an Update statement to an insert statement...HELP!!!!!



    UPDATE TBLRQRates
    SET RQCurrency = 'Euro',
    RQCurrencyVal = @EuroVal,
    RQCurrencyID = CounterID

    UPDATE TBLRQRates
    SET RQCurrency = 'Sterling',
    RQCurrencyVal = @SterlingVal,
    RQCurrencyID = CounterID

    UPDATE TBLRQRates
    SET RQCurrency = 'Dollar',
    RQCurrencyVal = @DollarVal,
    RQCurrencyID = CounterID


  • Options
    #10
    Delirium
    Moderators Posts: 51,799 ✭✭✭✭Delirium


    The Mighty Dubs wrote: »
    OK, im now ready to throw the PC out the window ;-) You gotta love Mondays. I need to convert these from an Update statement to an insert statement...HELP!!!!!



    UPDATE TBLRQRates
    SET RQCurrency = 'Euro',
    RQCurrencyVal = @EuroVal,
    RQCurrencyID = CounterID

    UPDATE TBLRQRates
    SET RQCurrency = 'Sterling',
    RQCurrencyVal = @SterlingVal,
    RQCurrencyID = CounterID

    UPDATE TBLRQRates
    SET RQCurrency = 'Dollar',
    RQCurrencyVal = @DollarVal,
    RQCurrencyID = CounterID
    What have you tried so far to insert the records?

    If you can read this, you're too close!



  • Options
    #11
    The Mighty Dubs
    Registered Users Posts: 224 ✭✭The Mighty Dubs


    I have done a lot of insert statements in the past, but looking at various sites n'stuff with examples of stored proc insert statements and am finding it hard

    This is part of the initial insert statemetn that triggers the stored proc

    If request.Form("Submit")="Submit" then

    sSQL = "SELECT CounterValue FROM tblCounter"
    VIEWRECORDSET RS,SSQL
    sItemCode=rs.fields("CounterValue").value
    sDollarVal = request.form("DollarVal")
    sSterlingVal = request.form("SterlingVal")

    sSQL = "UPDATE tblCounter SET CounterValue=CounterValue+1"
    ExecuteCommand sSQL

    Insert into RQRates(RQCurrencyID,RQCurrencyVal,RQCurrency) Values ('"& RQCounterID &"','" & DollarVal &"', '"& SterlingVal &"', "' & Currency & '")


  • Advertisement
  • Options
    #12
    RangeR
    Registered Users Posts: 7,265 ✭✭✭RangeR


    The Mighty Dubs wrote: »
    OK, im now ready to throw the PC out the window ;-) You gotta love Mondays. I need to convert these from an Update statement to an insert statement...HELP!!!!!



    UPDATE TBLRQRates
    SET RQCurrency = 'Euro',
    RQCurrencyVal = @EuroVal,
    RQCurrencyID = CounterID

    UPDATE TBLRQRates
    SET RQCurrency = 'Sterling',
    RQCurrencyVal = @SterlingVal,
    RQCurrencyID = CounterID

    UPDATE TBLRQRates
    SET RQCurrency = 'Dollar',
    RQCurrencyVal = @DollarVal,
    RQCurrencyID = CounterID

    Without seeing the table structure it is hard to say but each record qhould have a unique identifier

    Something like this will work but you need to work out what the PrimaryKey is.

    UPDATE TBLRQRates
SET RQCurrency = 'Euro',RQCurrencyVal = @EuroVal,RQCurrencyID = CounterID
WHERE UniqueID = @UniqueID


  • Options
    #13
    The Mighty Dubs
    Registered Users Posts: 224 ✭✭The Mighty Dubs


    Here is the script to show you the structure of the site, just to give you a better idea.


    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblCurrencyIE]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
    drop table [dbo].[tblCurrencyIE]
    GO

    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblRQRates]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
    drop table [dbo].[tblRQRates]
    GO

    CREATE TABLE [dbo].[tblCurrencyIE] (
    [Currency] [varchar] (60) COLLATE Latin1_General_CI_AS NULL ,
    [CurrencyVal] [float] NULL ,
    [CurrencyID] [int] NULL
    ) ON [PRIMARY]
    GO

    CREATE TABLE [dbo].[tblRQRates] (
    [id] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
    [RQCurrencyID] [int] NULL ,
    [RQCurrencyVal] [float] NULL ,
    [RQCurrency] [char] (10) COLLATE Latin1_General_CI_AS NULL
    ) ON [PRIMARY]
    GO


  • Options
    #14
    RangeR
    Registered Users Posts: 7,265 ✭✭✭RangeR


    The Mighty Dubs wrote: »
    Here is the script to show you the structure of the site, just to give you a better idea.


    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblCurrencyIE]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
    drop table [dbo].[tblCurrencyIE]
    GO

    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblRQRates]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
    drop table [dbo].[tblRQRates]
    GO

    CREATE TABLE [dbo].[tblCurrencyIE] (
    [Currency] [varchar] (60) COLLATE Latin1_General_CI_AS NULL ,
    [CurrencyVal] [float] NULL ,
    [CurrencyID] [int] NULL
    ) ON [PRIMARY]
    GO

    CREATE TABLE [dbo].[tblRQRates] (
    [id] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
    [RQCurrencyID] [int] NULL ,
    [RQCurrencyVal] [float] NULL ,
    [RQCurrency] [char] (10) COLLATE Latin1_General_CI_AS NULL
    ) ON [PRIMARY]
    GO

    Do this
    UPDATE TBLRQRates
    SET RQCurrency = 'Euro',RQCurrencyVal = @EuroVal,RQCurrencyID = CounterID
    WHERE RQCurrencyID = @RQCurrencyID

    UPDATE TBLRQRates
    SET RQCurrency = 'Sterling',RQCurrencyVal = @SterlingVal,RQCurrencyID = CounterID
    WHERE RQCurrencyID = @RQCurrencyID

    UPDATE TBLRQRates
    SET RQCurrency = 'Dollar',RQCurrencyVal = @DollarVal,RQCurrencyID = CounterID
    WHERE RQCurrencyID = @RQCurrencyID

    Your tblCurrencyIE table should also have a Primary Key [the RQCurrency field] and set to NOT NULL.

    Also the tblRQRates table needs it's ID field to be Primary Key [if not already done]


  • Options
    #15
    Delirium
    Moderators Posts: 51,799 ✭✭✭✭Delirium 


    UPDATE TBLRQRates
SET RQCurrency  = 'Euro',RQCurrencyVal = @EuroVal,RQCurrencyID = CounterID
WHERE RQCurrencyID = @RQCurrencyID
    converted to an insert statement 
    INSERT INTO TBLRQRates( RQCurrency, RQCurrencyVal , RQCurrencyID )
VALUES( 'Euro', @EuroVal, @CounterID)

    If you can read this, you're too close!



  • Options
    #16
    yop
    Moderators, Home & Garden Moderators, Regional Midwest Moderators, Regional West Moderators Posts: 16,724 Mod ✭✭✭✭yop


    OP, what DB are you using?? SQL Server?

    You can right click on the table and choose "Script Table As" - "Insert To"

    It will point you in the right direction, just replace the parameters as needed.


Advertisement