CODEWITHSUNDEEP
sql-servert-sqlstored-procedures
Wild Goat
Wild Goat

Reputation: 3589

T-SQL stored procedure construction

I am trying to develop a T-SQL stored procedure and perform some logic and computation there, but for some reason getting syntax error. I will highlight critical places, where I am not sure how to do it. 

CREATE PROC AddInvestment
@_Investor nvarchar(89),
@_Sum decimal(19,4),
@_Currency smallint,
@_Rate tinyint,
@_Date date,
@_Comment nvarchar(max)

AS

DECLARE @_MavroRate decimal(19,4)
DECLARE @_Mavro decimal(19,4)

SELECT mr.[40] INTO @_MavroRate <==== ERROR
FROM dbo.MavroRateBuy mr
WHERE mr.Date = @_Date

@_Mavro = _Sum / @_MavroRate <==== ERROR

INSERT INTO Investments(Investor, Sum, Currency, Rate, Date, Mavro, MavroRate, Comment)
VALUES (@_Investor, @_Sum, @_Currency, @_Rate, @_Date, @_Mavro, @_MavroRate, @_Comment)

Could you please verify by T-SQL statement and point me what I am doing wrong here.

Thanks!

Upvotes: 0

Views: 213

Answers (3)

Nick O
Nick O

Reputation: 3826

You missing a SET in front of the line @_Mavro = _Sum / @_MavroRate. It should look like the code below.

SET @_Mavro = _Sum / @_MavroRate

Also, the INTO is used to insert your results into a new table, not to set a variable. The code below will is the correct way to set a variable in a select statement.

SELECT @_MavroRate = mr.[40]
FROM dbo.MavroRateBuy mr
WHERE mr.Date = @_Date

Upvotes: 1

Joseph Sturtevant
Joseph Sturtevant

Reputation: 13360

Try this:

CREATE PROC AddInvestment
@_Investor nvarchar(89),
@_Sum decimal(19,4),
@_Currency smallint,
@_Rate tinyint,
@_Date date,
@_Comment nvarchar(max)

AS

DECLARE @_MavroRate decimal(19,4)
DECLARE @_Mavro decimal(19,4)

SELECT @_MavroRate = mr.[40]
FROM dbo.MavroRateBuy mr
WHERE mr.Date = @_Date

SET @_Mavro = @_Sum / @_MavroRate

INSERT INTO Investments(Investor, Sum, Currency, Rate, Date, Mavro, MavroRate, Comment)
VALUES (@_Investor, @_Sum, @_Currency, @_Rate, @_Date, @_Mavro, @_MavroRate, @_Comment)

Upvotes: 1

Carlos Quintanilla
Carlos Quintanilla

Reputation: 13343

Something like this I suppose:

SELECT @_MavroRate = mr.[40]  
FROM dbo.MavroRateBuy mr
WHERE mr.Date = @_Date

SELECT @_Mavro = @_Sum / @_MavroRate

@_Sum needs to have a value before it is used.

Upvotes: 2

Related Questions