SQL SPROC syntax Issues

I am using Developer 14, trying to create a SPROC that will return information contained in a view 'vtblProperty_Muni_LB_TB_Owner'

I pass three fields to the SPROC that are used to find the record I'm looking for, or not find it.

If a matching record is found I want to return three fields back to the routine calling the SPROC.  If a matching record is not found I return 0, 'UnKnown' and  'UnKnown' respectively.

This is what I have so far:
Create PROCEDURE [dbo].[spGetPropertyIDOwnerAndPropAddrFromMuni_LB_TB] 
	-- Add the parameters for the stored procedure here
	@Muni             int = 0,
	@LotBlock         nvarchar(30) = null,
	@TieBreaker       nvarchar(2) = null,
	@PropertyRecID    int = 0             OUTPUT,
	@PropertyOwner    nvarchar(10) = null Output,
	@PropertyAddress  nvarChar(10) = null Output
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here

	Set @PropertyRecID = IsNull(PropertyRecID,0),
		@PropertyOwner = isNull(LandOwner,'UnKnown'),
		@PropertyAddress = IsNull(PropAddr,'UnKnown')
	From vtblProperty_Muni_LB_TB_Owner
Select PropertyRecID, LandOwner, PropAdd 
	Where Muni            = @Muni         and
	      LotBlock        = @LotBlock     and
		  TieBreaker      = @TieBreaker  

Open in new window


But I am getting an syntax error:
Msg 102, Level 15, State 1, Procedure spGetPropertyIDOwnerAndPropAddrFromMuni_LB_TB, Line 25
Incorrect syntax near ','.

This is line 25 and 26
      Set @PropertyRecID = IsNull(PropertyRecID,0),
            @PropertyOwner = isNull(LandOwner,'UnKnown'),

Can anyone see what the issue is?
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.

Pawan KumarDatabase ExpertCommented:
You need to use SELECT instead of SET if we are assigning multiple variables. SET will work only for work for setting single variable value.  SELECT can be used to SET SINGLE or MULTIPLE Variables.

Please read more from - https://www.mssqltips.com/sqlservertip/1888/when-to-use-set-vs-select-when-assigning-values-to-variables-in-sql-server/

I changed below
SELECT @PropertyRecID = IsNull(PropertyRecID,0), -----> Set @PropertyRecID = IsNull(PropertyRecID,0),

Please use below-

Create PROCEDURE [dbo].[spGetPropertyIDOwnerAndPropAddrFromMuni_LB_TB] 
	-- Add the parameters for the stored procedure here
	@Muni             int = 0,
	@LotBlock         nvarchar(30) = null,
	@TieBreaker       nvarchar(2) = null,
	@PropertyRecID    int = 0             OUTPUT,
	@PropertyOwner    nvarchar(10) = null Output,
	@PropertyAddress  nvarChar(10) = null Output
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 @PropertyRecID = IsNull(PropertyRecID,0),
		@PropertyOwner = isNull(LandOwner,'UnKnown'),
		@PropertyAddress = IsNull(PropAddr,'UnKnown')
	From vtblProperty_Muni_LB_TB_Owner

	Select PropertyRecID, LandOwner, PropAdd 
	Where Muni            = @Muni         and
	      LotBlock        = @LotBlock     and
		  TieBreaker      = @TieBreaker  

END

Open in new window

0
ste5anSenior DeveloperCommented:
Just collect your values first, do the adjustments later. This covers the case, when your view returns no rows for the given predicates.

CREATE PROCEDURE [dbo].[spGetPropertyIDOwnerAndPropAddrFromMuni_LB_TB]
    @Muni INT = 0 ,
    @LotBlock NVARCHAR(30) = NULL ,
    @TieBreaker NVARCHAR(2) = NULL ,
    @PropertyRecID INT = 0 OUTPUT ,
    @PropertyOwner NVARCHAR(10) = 'UnKnown' OUTPUT ,
    @PropertyAddress NVARCHAR(10) = 'UnKnown' OUTPUT
AS
    SET NOCOUNT ON;

    SELECT @PropertyRecID = PropertyRecID ,
           @PropertyOwner = LandOwner ,
           @PropertyAddress = PropAddr
    FROM   vtblProperty_Muni_LB_TB_Owner
    WHERE  Muni = @Muni
           AND LotBlock = @LotBlock
           AND TieBreaker = @TieBreaker;

    SET @PropertyRecID = ISNULL(@PropertyRecID, 0);
    SET @PropertyOwner = ISNULL(@PropertyOwner, 'UnKnown');
    SET @PropertyAddress = ISNULL(@PropertyAddress, 'UnKnown');

Open in new window


Caveat: Without TOP 1 and ORDER BY or a guarantee, that this view will return only one row, the results may be arbitrary.

btw, use the correct default return values.
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
mlcktmguyAuthor Commented:
Thanks for the information and suggestions.  

Pawan: Your answer was not completely correct, it wouldn't compile.  

Ste5an saw that I had two selects and removed one in his response.
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
Microsoft SQL Server

From novice to tech pro — start learning today.