SQLTeam.com | Weblogs | Forums

Creating Stored Procedure with Select Statements


#1

Hi,

I am trying to create a Stored Procedure but I am receiving the following error message:

Msg 156, Level 15, State 1, Procedure Freebalance, Line 23
Incorrect syntax near the keyword 'END'.

My Stored Procedure is shown below:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Tracy Samuels
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE Freebalance
-- Add the parameters for the stored procedure here
@startdate datetime,
@enddate datetime

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
SELECT year1, '02', trandate, period, jv, tran_desc, 'item', account1, '/', account2, '/', account3,account9, debit, credit, commitment_ref 

FROM
(SELECT B1.TRXDATE AS trandate, B1.JRNENTRY AS jv, B1.REFRENCE AS tran_desc, '2016' as year1, '12' as period, C1.ACTNUMBR_1 AS account1, C1.ACTNUMBR_2 AS account2, C1.ACTNUMBR_3 AS account3 from GL20000 B1
INNER JOIN GL00105 C1 ON GL20000.ACTINDX = GL00105.ACTINDX
where TRXDATE BETWEEN @startdate and @enddate)
END
GO

What is causing the error ?

Thanks


#2

You just need to add an alias for your derived table, like this:

where TRXDATE BETWEEN @startdate and @enddate) aliasname --I often will just use t for the alias name.