Skip to content
Advertisement

SQL Server syntax correction [closed]

I am trying to create a SQL Server stored procedure but I am getting some errors.

create procedure testproc8
as
begin
    declare @x int 
    declare @z int
    declare @y money OUTPUT

    set @y = (select (sum([OrderQty] * [UnitPrice]) / sum([OrderQty])) * @x
              from [dbo].[Transactions]
              where [ProductID] = @z
              group by [ProductID])

    return 
end

Here are the errors:

Msg 102, Level 15, State 1, Procedure testproc8, Line 6 [Batch Start Line 60]
Incorrect syntax near ‘OUTPUT’

Msg 137, Level 15, State 1, Procedure testproc8, Line 7 [Batch Start Line 60]
Must declare the scalar variable “@y”

Thanks in advance

Advertisement

Answer

Remove Output and just select @y for your result

create proc testproc8
-- Add the parameters for the stored procedure here 
as
begin
declare @x int 
--set @x = certain integer 'if your stored procedure did not have parameters'
declare @z int
--set @z = certain integer 'if your stored procedure did not have parameters'
declare @y money 
set @y = 
(
    select (SUM([OrderQty]*[UnitPrice])/SUM([OrderQty])) * @x
    from [dbo].[Transactions]
    where [ProductID] = @z
    group by [ProductID]
)

select @y
end
User contributions licensed under: CC BY-SA
9 People found this is helpful
Advertisement