Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2

    Unanswered: Problem writing T-SQL function - gives error 170

    Morning all

    I'm trying to convert a set of VBA functions into T-SQL, and I thought that I'd start with the simple ones and work my way up. Given that my direct experience of T-SQL consists in the main of repairing existing databases, this seemed logical to me.

    Here is the VBA function:
    Code:
    Function DCFORECAST(intPeriod As Integer, Optional intFlag As Integer = 0) As String
    	'2009-Aug-28
    	'Apply the forecast naming algorithm to the period number
    	
    	Dim strWorking As String
    	
    	strWorking = IIf(intFlag = 0, "", "Forecast ")
    	strWorking = strWorking & intPeriod - 1 & "+"
    	strWorking = strWorking & 12 - (intPeriod - 1)
    	
    	DCFORECAST = strWorking
    	
    End Function
    Here is my attempt at getting this into T-SQL:
    Code:
    CREATE FUNCTION dbo.DCFORECAST
    
    	(@PeriodNo AS int
    	@TitleFlag AS int)  
    
    RETURNS VarChar(15) AS  
    
    BEGIN 
    
    DECLARE
    	@WorkString AS varchar(15)
    
    SELECT
    	@WorkString = 	CASE @TitleFlag
    				WHEN 0
    				THEN ""
    				ELSE 'Forecast '
    			END
    SELECT 
    	@WorkString = @WorkString + CAST(@PeriodNo - 1 AS varchar(2)) + '+'
    
    SELECT 
    	@WorkString = @WorkString + CAST(12 - (@PeriodNo - 1) AS varchar(2))
    
    RETURN @WorkString
    
    END
    I know that the VBA one works, because it's in use. When I tried to save the SQL function, the following error was displayed:
    Error 170: Line 4: Incorrect syntax near '@TitleFlag'
    Must declare the variable '@TitleFlag'.
    Must declare the variable '@PeriodNo'.
    Must declare the variable '@PeriodNo'.
    A RETURN statement with a return value cannot be used in this context.

    I'm fairly sure that I have declared all the variables! Can anyone tell me what I'm doing wrong?
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  2. #2
    Join Date
    Jun 2005
    Posts
    319
    missing a comma, using double quotes instead of single quotes

    Code:
    CREATE FUNCTION dbo.DCFORECAST
    
    	(@PeriodNo AS int
    	,@TitleFlag AS int)  
    
    RETURNS VarChar(15) AS  
    
    BEGIN 
    
    DECLARE
    	@WorkString AS varchar(15)
    
    SELECT
    	@WorkString = 	CASE @TitleFlag
    				WHEN 0
    				THEN ''
    				ELSE 'Forecast '
    			END
    SELECT 
    	@WorkString = @WorkString + CAST(@PeriodNo - 1 AS varchar(2)) + '+'
    
    SELECT 
    	@WorkString = @WorkString + CAST(12 - (@PeriodNo - 1) AS varchar(2))
    
    RETURN @WorkString
    
    END

  3. #3
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    That's brilliant - thank you!
    10% of magic is knowing something that no-one else does. The rest is misdirection.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •