mlcktmguy
asked on
Stored Procedure 'Select' statement syntax issues
SQL Server Query Syntax
I am creating a SQL SPROC that will be passed a headerID as the input parameter. The Header ID is the primary key of the table,
There are several fields that are output parameters. I want these fields loaded with the corresponding fields in the selected record, if any.
The SPROC is pretty simple but I definitely have my syntax wrong. THis is the first time I've tried to use the (NoLock) but there is more wrong than that. I'm pretty sure it's the way I'm trying to assign the table values to the return parameters. This is the SPROC:
These are the syntax errors I'm getting.
Msg 102, Level 15, State 1, Procedure sptblMuni_Master_Get, Line 34
Incorrect syntax near '@SchoolDistrictNum'.
Msg 319, Level 15, State 1, Procedure sptblMuni_Master_Get, Line 41
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
Can anyone spot my error(s)?
I am creating a SQL SPROC that will be passed a headerID as the input parameter. The Header ID is the primary key of the table,
There are several fields that are output parameters. I want these fields loaded with the corresponding fields in the selected record, if any.
The SPROC is pretty simple but I definitely have my syntax wrong. THis is the first time I've tried to use the (NoLock) but there is more wrong than that. I'm pretty sure it's the way I'm trying to assign the table values to the return parameters. This is the SPROC:
USE [JTSConversion]
GO
/****** Object: StoredProcedure [dbo].[tblMuni_Master_Get] Script Date: 3/14/2018 11:24:34 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: MJO
-- Create date:
-- Description: Get Payment Years
-- =============================================
Create PROCEDURE [dbo].[sptblMuni_Master_Get]
-- Add the parameters for the stored procedure here
@passedHeaderID int = 0,
@MuniCode int = 0 Output,
@SchoolDistrictNum int = 0 Output,
@MuniName nvarchar(25) Output,
@FormalName nvarchar(25) Output,
@Millage float = 0 Output,
@PerCap float = 0 Output,
@ContactName nvarchar(25) Output,
@Title nvarchar(25) output
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
SELECT Top 1
@MuniCode = MuniCode
@SchoolDistrictNum = SchoolDistrictNum
@MuniName = MuniName
@FormalName = FormalName
@Millage = Millage
@PerCap = PerCap
@ContactName = ContactName
@Title = Title
FROM dbo.tblMuni_Master With (NOLOCK)
WHERE MuniMastID = @passedPayHeaderID
END
These are the syntax errors I'm getting.
Msg 102, Level 15, State 1, Procedure sptblMuni_Master_Get, Line 34
Incorrect syntax near '@SchoolDistrictNum'.
Msg 319, Level 15, State 1, Procedure sptblMuni_Master_Get, Line 41
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
Can anyone spot my error(s)?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER