Stored Proc help needed Urgentlly

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 2000 Forums

SQL Server Development (2000)

Stored Proc help needed Urgentlly

Author

Topic

gregdillon
Starting Member

2 Posts

Posted - 2008-12-12 : 11:54:02

Hi All,

This is my first attempt at a stored proc so please dont laugh. Basically i need a couple of bits of information inserted into 2 tables. Below is my "effort" of a proc. Naturally its getting loads of errors(See bottom). Any help would be much appreciated.

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

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

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

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

UPDATE TBLRQRates
SET RQCurrency = 'Euro'
SET RQCurrencyVal = tblCurrencyIE.CurrencyVal Where tblCurrencyIE.CurrencyVal = 'Euro'
SET RQCurrencyID = CounteriD

UPDATE TBLRQRates
SET RQCurrency = 'Sterling'
SET RQCurrencyVal = tblCurrencyIE.CurrencyVal Where tblCurrencyIE.CurrencyVal = 'Sterling'
SET RQCurrencyID = CounteriD

UPDATE TBLRQRates
SET RQCurrency = 'Dollar'
SET RQCurrencyVal = tblCurrencyIE.CurrencyVal Where tblCurrencyIE.CurrencyVal = 'Dollar'
SET RQCurrencyID = CounteriD

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

GO

*********************************
Errors

Server: Msg 170, Level 15, State 1, Procedure RQCurrencies, Line 4
Line 4: Incorrect syntax near '@RQCurrencyVal'.
Server: Msg 137, Level 15, State 1, Procedure RQCurrencies, Line 10
Must declare the variable '@CounterID'.
Server: Msg 137, Level 15, State 1, Procedure RQCurrencies, Line 11
Must declare the variable '@RQCurrencyID'.
Server: Msg 137, Level 15, State 1, Procedure RQCurrencies, Line 12
Must declare the variable '@RQCurrencyVal'.
Server: Msg 170, Level 15, State 1, Procedure RQCurrencies, Line 21
Line 21: Incorrect syntax near '='.
Server: Msg 170, Level 15, State 1, Procedure RQCurrencies, Line 26
Line 26: Incorrect syntax near '='.
Server: Msg 170, Level 15, State 1, Procedure RQCurrencies, Line 31
Line 31: Incorrect syntax near '='.
Server: Msg 137, Level 15, State 1, Procedure RQCurrencies, Line 34
Must declare the variable '@CounterID'.
Server: Msg 137, Level 15, State 1, Procedure RQCurrencies, Line 36
Must declare the variable '@RQCurrencyVal'.
Server: Msg 137, Level 15, State 1, Procedure RQCurrencies, Line 37
Must declare the variable '@RQCurrencyID'.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-13 : 01:12:06

your procedure doesnt make much sense to me. Can you explain what you're requirement is before i tweak code for you?
Also just keep in mind that if initial select statements return more than 1 record, you cant store all values in variable. it just holds a single value and all others will be lost.

gregdillon
Starting Member

2 Posts

Posted - 2008-12-15 : 09:43:34

quote:
Originally posted by visakh16

your procedure doesnt make much sense to me. Can you explain what you're requirement is before i tweak code for you?
Also just keep in mind that if initial select statements return more than 1 record, you cant store all values in variable. it just holds a single value and all others will be lost.

Hi, I have updated the code(see below) but am still 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.

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-15 : 13:29:05

quote:
Originally posted by gregdillon

quote:
Originally posted by visakh16

your procedure doesnt make much sense to me. Can you explain what you're requirement is before i tweak code for you?
Also just keep in mind that if initial select statements return more than 1 record, you cant store all values in variable. it just holds a single value and all others will be lost.

Hi, I have updated the code(see below) but am still 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.

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

you've missed commas
also did you check if any of select queries which assign value to variables return more than 1 record?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources