Function error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Function error

Author

Topic

mistux
Yak Posting Veteran

90 Posts

Posted - 2005-02-28 : 10:12:27

I am trying to make this function so that I can use it in another stored procedure, it basically returns one value a number (I'm converting an VBA function from an Access MDB into this function so that I can use it in an Access ADP--where I can't use VBA function is queries now.


Server: Msg 156, Level 15, State 1, Procedure FN_QCntr, Line 17
Incorrect syntax near the keyword 'If'.
Server: Msg 156, Level 15, State 1, Procedure FN_QCntr, Line 18
Incorrect syntax near the keyword 'IF'.
Server: Msg 170, Level 15, State 1, Procedure FN_QCntr, Line 19
Line 19: Incorrect syntax near '@Cntr'.
Server: Msg 156, Level 15, State 1, Procedure FN_QCntr, Line 28
Incorrect syntax near the keyword 'If'.
Server: Msg 156, Level 15, State 1, Procedure FN_QCntr, Line 29
Incorrect syntax near the keyword 'IF'.
Server: Msg 170, Level 15, State 1, Procedure FN_QCntr, Line 30
Line 30: Incorrect syntax near '@Cntr'.
Server: Msg 1075, Level 15, State 1, Procedure FN_QCntr, Line 50
RETURN statements in scalar valued functions must include an argument.

Here is what I have so far:


CREATE FUNCTION "FN_QCntr"

/*
QCntr(x, Optional Y As Long) As Long:  X will be like E152 and Y will either be 1 or 2
*/

(@X nvarchar(50), @Y int)
RETURNS int /* datatype */
AS
BEGIN
		/* sql statement ... */
DECLARE @Cntr as int, @QCntr as int, @MaxItem as int
set @Cntr = 0
set @MaxItem = Go SELECT MAX(ItemID) AS Expr1 FROM T_Schedule_ProductionItems
If Y=1
		(If @MaxItem IS NOT NULL
				(IF @Cntr>@MaxItem
						@Cntr=@Cntr+1
						@QCntr=@Cntr
					Else
						@Cntr>@MaxItem
						@Cntr=@Cntr+1
						@QCntr=@Cntr
				End)
		END)
Else IF Y=2
		(If @MaxItem IS NOT NULL
				(IF  @Cntr>@MaxItem
						@Cntr=@Cntr+1
						@QCntr=@Cntr
					Else
						@Cntr>@MaxItem
						@Cntr=@Cntr+1
						@QCntr=@Cntr
				End)
		END)
	Else
	    @Cntr = @Cntr + 1
                @QCntr = @Cntr
        End
Else
	@Cntr = @Cntr + 1
	@QCntr = @Cntr
End
 )


	RETURN /* value */
END

Am I doing the IFs correct? Or what?

===================
Michael

mistux
Yak Posting Veteran

90 Posts

Posted - 2005-02-28 : 11:22:32

Ok, I've gotten rid of most of the errors, but still get these two:


Server: Msg 170, Level 15, State 1, Procedure FN_QCntr, Line 24
Line 24: Incorrect syntax near '@Cntr'.
Server: Msg 170, Level 15, State 1, Procedure FN_QCntr, Line 38
Line 38: Incorrect syntax near '@Cntr'.

Here is my updated code:


CREATE FUNCTION "FN_QCntr"

/*
'Used to count the records in a queary, 
QCntr(x, Optional Y As Long) As Long:  X will be like E152 and Y will either be 1 or 2
*/

(@X nvarchar(50), @Y int)
RETURNS int /* datatype */
AS
BEGIN
		/* sql statement ... */
DECLARE @Cntr as int, @QCntr as int, @MaxItem as int
set @Cntr = 0
set @MaxItem = Go SELECT MAX(ItemID) AS Expr1 FROM T_Schedule_ProductionItems

IF @Y=1
	
		
		If @MaxItem IS NOT NULL		--found items (NOT NULL) start numbering at DMax + 1
				--begin
				IF @Cntr > @MaxItem
						
						@Cntr=@Cntr+1
						@QCntr=@Cntr
						
					Else
						@Cntr>@MaxItem
						@Cntr=@Cntr+1
						@QCntr=@Cntr
				
				
	
		
Else IF @Y=2	--  put 2 in because we wanted this to run independently for 1 particular query
		If @MaxItem IS NOT NULL
				IF  @Cntr > @MaxItem
						@Cntr=@Cntr+1
						@QCntr=@Cntr
					Else
						@Cntr>@MaxItem
						@Cntr=@Cntr+1
						@QCntr=@Cntr
				
		
	Else		--No items found (NULL)  start numbering with 1
	    @Cntr = @Cntr + 1
            @QCntr = @Cntr
        
Else
	@Cntr = @Cntr + 1
	@QCntr = @Cntr
End
 )


RETURN @QCntr /* value */

===================
Michael

PW
Yak Posting Veteran

95 Posts

Posted - 2005-02-28 : 11:41:23

You need a SET (or SELECT) when assigning to variables:

SET @Cntr=@Cntr+1

X002548
Not Just a Number

15586 Posts

Posted - 2005-02-28 : 11:45:42

Can you describe what exactly you are trying to do...

Forget the code...tell us the business function...

You have a lot more than 2 errors in there...

Brett

8-)

mistux
Yak Posting Veteran

90 Posts

Posted - 2005-02-28 : 12:50:31

quote:
Can you describe what exactly you are trying to do...

I am basically creating a unique number, that starts with the next number from a field in a table, and increments that number by one

===================
Michael

X002548
Not Just a Number

15586 Posts

Posted - 2005-02-28 : 12:58:21

You could probably use a variation of something like this

http://weblogs.sqlteam.com/brettk/archive/2004/06/29/1687.aspx

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources