asked on

Syntax Error on UDF

I created a User Defined function that uses an ID, in this case StateProvinceID (INT) to the return the States TAXRATE, STATEPROVINCECODE and COUNTRYREGIONCODE,
having Syntax error at the very start of the query,

Msg 156, Level 15, State 1, Procedure StateProvinceLogin, Line 16 [Batch Start Line 217]
Incorrect syntax near the keyword 'SELECT'.

                        CREATE FUNCTION      StateProvinceLogin
                                                ( @StateProvinceID int )

                        RETURNS TABLE AS RETURN

                                    (      SELECT                  a.TaxRate, b.StateProvinceCode, b.CountryRegionCode
                                          FROM                  AdventureWorks2016.Sales.SalesTaxRate a
                                          INNER JOIN            AdventureWorks2016.Person.StateProvince b
                                          ON                        a.StateProvinceID = b.StateProvinceID
                                          WHERE                  @StateProvinceID = TaxRate AND @StateProvinceID = StateProvinceCode
                                          AND                        @StateProvinceID = CountryRegionCode
                                     )
                                     -- To Verify

                                    SELECT * FROM StateProvinceLogin('5')

slightwv (䄆 Netminder)

My MySQL is rusty but per the docs:
https://dev.mysql.com/doc/refman/8.0/en/create-function-udf.html

Functions cannot return a TABLE.

Try making it a procedure

CREATE PROCEDURE StateProvinceLogin(p_StateProvinceID int )
BEGIN
	SELECT a.TaxRate, b.StateProvinceCode, b.CountryRegionCode
	FROM AdventureWorks2016.Sales.SalesTaxRate a
		INNER JOIN AdventureWorks2016.Person.StateProvince b
			ON a.StateProvinceID = b.StateProvinceID
	WHERE p_StateProvinceID = TaxRate AND  p_StateProvinceID = StateProvinceCode
			AND p_StateProvinceID = CountryRegionCode;
END

Open in new window

Pavel Celba

I would guess the AdventureWorks2016 database is not available for MySQL so we are in the SQL Server world... and the error message seems to be generated by MS SQL Server.

So the main problem is you are trying to execute the function creation script together with the testing SELECT command.

Simply add the GO command before the line containing the comment (-- To Verify) or execute the first part (the function creation) and then you may test it by issuing the SELECT command which better in your case.

You should also review the WHERE clause in the function. The @StateProvinceID seems to be used incorrectly as it is integer but you are using it to compare to CountryRegionCode and TaxRate which is rather suspicious.

Fred

ASKER

CREATE FUNCTION            StateProvinceLogin
                                                      ( @StateProvinceID Varchar(5) )

                        RETURNS TABLE AS RETURN

                                    (      SELECT                  a.TaxRate, b.StateProvinceCode, b.CountryRegionCode
                                          FROM                  AdventureWorks2016.Sales.SalesTaxRate a
                                          INNER JOIN            AdventureWorks2016.Person.StateProvince b
                                          ON                        a.StateProvinceID = b.StateProvinceID
                                          WHERE                  @StateProvinceID = TaxRate AND @StateProvinceID = StateProvinceCode
                                          AND                        @StateProvinceID = CountryRegionCode
                                     )
                                     -- To Verify
                              GO
                                    SELECT * FROM StateProvinceLogin('TX')
I used your advice but I can't figure out how to use the function since the rate is in money
Msg 293, Level 16, State 0, Line 228
Cannot convert char value to smallmoney. The char value has incorrect syntax.

slightwv (䄆 Netminder)

Is this MySQL or SQL Server? The correct Topic Area will help lead to better advice. It is currently MySQL which is why I originally gave you a MySQL response.

If TaxRate is money, then what is this supposed to do " @StateProvinceID = TaxRate "?

You are trying to compare 'TX' to money.

Fred

ASKER

this is SQL server , I was trying to create a Funtion whose variable that returns 3 things TaxRate (money) CountryRegionCode (Varchar) and StateProvince Code e,g TX..

slightwv (䄆 Netminder)

>>I was trying to create a Funtion whose variable that returns 3 things TaxRate (money) CountryRegionCode (Varchar) and StateProvince Code e,g TX..

Sorry but I don't understand what that is trying to tell me.

Are you looking for a function that accepts a single parameter that will look for that value in any one of three columns?

Fred

ASKER

Let me create a table that uses just one variable to return those 3 columns first, and it take it from there

Sharath S

You are passing StateProvinceID and trying to match it to TaxRate, StateProvinceCode and CountryRegionCode. I don't know your data but seems to be wrong. try this.

 CREATE FUNCTION      StateProvinceLogin
                                                ( @StateProvinceID int )
                        
                        RETURNS TABLE AS RETURN

                                    (      SELECT                  a.TaxRate, b.StateProvinceCode, b.CountryRegionCode
                                          FROM                  AdventureWorks2016.Sales.SalesTaxRate a
                                          INNER JOIN            AdventureWorks2016.Person.StateProvince b
                                          ON                        a.StateProvinceID = b.StateProvinceID
                                          WHERE                 a.StateProvinceID =  @StateProvinceID
                                     )
GO

SELECT * FROM StateProvinceLogin(5)
GO

Open in new window

Pavel Celba

I am not using AdventureWorks2016 database but looking at the original function it contains several highly unclear parts:
- the function name contains "Login" but the function is surely not intended to do some login
- the WHERE clause contains a few unexplained parts (I've pointed this out in my first post)
- you are passing string to the integer function parameter
- the function definition does not specify Schema
- format the SQL for better readability

Sharath provided a slightly updated function but you should do much more:
1) Try to understand the AdventureWorks data model - this should help to define proper function parameters and correct WHERE clause
2) Don't create the function as the first step - test the query itself with constants in place of your variables in SSMS
3) Once the query works as expected replace the constants by variables and test the query again
4) Create the function and test it

You may start with the following query:

SELECT a.TaxRate, b.StateProvinceCode, b.CountryRegionCode
  FROM AdventureWorks2016.Sales.SalesTaxRate a
 INNER JOIN AdventureWorks2016.Person.StateProvince b ON a.StateProvinceID = b.StateProvinceID
 WHERE a.StateProvinceID = 5

Open in new window

Then you may create function which is just better formatted variant of the previous example:

CREATE FUNCTION dbo.StateProvinceData ( @StateProvinceID int )
RETURNS TABLE 
AS 
RETURN
 (SELECT a.TaxRate, b.StateProvinceCode, b.CountryRegionCode
    FROM AdventureWorks2016.Sales.SalesTaxRate a
   INNER JOIN AdventureWorks2016.Person.StateProvince b ON a.StateProvinceID = b.StateProvinceID
   WHERE a.StateProvinceID = @StateProvinceID
 ) 
GO

-- Test the function
SELECT * FROM dbo.StateProvinceData(5)

Open in new window

Remember AdventureWorks uses schemas almost everywhere. I've used dbo which is good for beginning but you may decide about a different schema.

ASKER CERTIFIED SOLUTION

Pavel Celba

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial