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?
 
ste5anConnect With a Mentor Senior 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
 
Pawan KumarConnect With a Mentor Database 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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.