Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 71
  • Last Modified:

UDF - What is causing this error?

I am getting this error when trying to compile.
Error:
Msg 102, Level 15, State 1, Procedure xTest, Line 43 [Batch Start Line 7]
Incorrect syntax near '@BookList'.


Function:
create FUNCTION [dbo].[xTest] 
(	

	@ShippingCost smallmoney = 0

)
RETURNS @BookList Table (
ISBN nvarhcar(13),
Title nvarchar(150),
Publisher nvarchar(150)
)


AS
Begin

With CTE As(

Select	g.ISBN, 
		g.Title,
		Case when bp.ISBN Is Null 
			Then g.Publisher Else bp.Publisher End 
													As Publisher,
		@ShippingCost								As ShippingCost
		

	From		tblGuides g
	Join		tblGuideHeader gh			On g.GuideNumber = gh.GuideNumber
	Join		tblPOSupplierItemList sl	On g.ISBN = sl.ISBN
	Left Join	tblBookPublisher bp			On bp.ISBN = g.ISBN

	where	gh.FinalShipDate < getDate()
		And
			(dbo.QtyLeft_Guide(g.GuideNumber, g.ISBN) > 0)
)
select  ISBN, Title, Publisher, ShippingCost
Into xxxx  from cte
 
 Select ISBN, Title, Publisher
 Into @BookList
 from xxxx Where ShippingCost = 1

return

End

Open in new window

0
Jess31
Asked:
Jess31
1 Solution
 
SharathData EngineerCommented:
Replace it with INSERT
create FUNCTION [dbo].[xTest] 
(	

	@ShippingCost smallmoney = 0

)
RETURNS @BookList Table (
ISBN nvarhcar(13),
Title nvarchar(150),
Publisher nvarchar(150)
)


AS
Begin

With CTE As(

Select	g.ISBN, 
		g.Title,
		Case when bp.ISBN Is Null 
			Then g.Publisher Else bp.Publisher End 
													As Publisher,
		@ShippingCost								As ShippingCost
		

	From		tblGuides g
	Join		tblGuideHeader gh			On g.GuideNumber = gh.GuideNumber
	Join		tblPOSupplierItemList sl	On g.ISBN = sl.ISBN
	Left Join	tblBookPublisher bp			On bp.ISBN = g.ISBN

	where	gh.FinalShipDate < getDate()
		And
			(dbo.QtyLeft_Guide(g.GuideNumber, g.ISBN) > 0)
)
select  ISBN, Title, Publisher, ShippingCost
Into xxxx  from cte
 
 INSERT @BookList
 Select ISBN, Title, Publisher
 from xxxx Where ShippingCost = 1

return

End

Open in new window

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.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now