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:
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

Open in new window


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)?
LVL 1
mlcktmguyAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulEE Topic AdvisorCommented:
You need commas...
  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

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulEE Topic AdvisorCommented:
By the way...

When you use TOP there should be an ORDER BY otherwise you may get unexpected results.
0
Nitin SontakkeDeveloperCommented:
Typically, only select statement is used and not the output parameters. Values are returned and can be consumed by language specific variables. Different case altogether if this sp is being called from another stored procedure, of course.

In addition, if MuniMastID is a primary key column / unique column (which it sounds like) you don't need top 1 as only one record will be returned anyway.
0
ste5anSenior DeveloperCommented:
First of all: Don't use NOLOCK. You may get terrible, wrong results.

Then: you misspelled the input parameter.

There is a third possibility: Using functions.

CREATE FUNCTION [dbo].[sptblMuni_Master_Get]
    (
        @passedHeaderID INT = 0
    )
RETURNS @Result TABLE
    (
        MuniCode INT ,
        SchoolDistrictNum INT ,
        MuniName NVARCHAR(25) ,
        FormalName NVARCHAR(25) ,
        Millage FLOAT ,
        PerCap FLOAT ,
        ContactName NVARCHAR(25) ,
        Title NVARCHAR(25)
    )
AS
    BEGIN
        INSERT INTO @Result ( MuniCode ,
                              SchoolDistrictNum ,
                              MuniName ,
                              FormalName ,
                              Millage ,
                              PerCap ,
                              ContactName ,
                              Title )
                    SELECT TOP 1 MuniCode ,
                           SchoolDistrictNum ,
                           MuniName ,
                           FormalName ,
                           Millage ,
                           PerCap ,
                           ContactName ,
                           Title
                    FROM   dbo.tblMuni_Master
                    WHERE  MuniMastID = @passedHeaderID;
        RETURN;
    END;
GO

CREATE PROCEDURE [dbo].[sptblMuni_Master_Get]
    (
        @passedHeaderID INT = 0
    )
AS
    SET NOCOUNT ON;

    SELECT TOP 1 MuniCode ,
           SchoolDistrictNum ,
           MuniName ,
           FormalName ,
           Millage ,
           PerCap ,
           ContactName ,
           Title
    FROM   dbo.tblMuni_Master
    WHERE  MuniMastID = @passedHeaderID;
GO

CREATE PROCEDURE [dbo].[sptblMuni_Master_Get]
    (
        @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
    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
    WHERE  MuniMastID = @passedHeaderID;
GO

Open in new window


Caveat: Functions are not good performance wise, when used in multi-level statements.

Pro: As "end-point" where the front-end (data layer) of the application queries they data, they are a good tool to simplify data retrieval.

Contra: Encapsulating such simple queries makes only sense in complex permission management scenarios.. normally you would simply query the data directly.
0
mlcktmguyAuthor Commented:
Thanks for all the comments and insight.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.