0

I created a sql function to verify that a year is a valid number. Now I am creating a function to make sure if param1 is non-null, param2 must also be non-null.

For whatever reason the dbo.fun_chk_year works great, but dbo.fun_chk_req throws:

Cannot find either column 'dbo' or the user-defined function or aggregate 'dbo.fun_chk_req' or the name is ambiguous

----EDITED-----

Sorry guys, I tried to omit some of the code that seemed irrelevant to the problem and made changes I shouldn't have in an attempt to simplify the question. Here's a better explanation:

This is run first (it's in a separate query document). It executes seemingly without any issues.

CREATE FUNCTION dbo.fun_chk_year (@year smallint)
RETURNS tinyint
AS
BEGIN
    IF (@year>1000 AND @year<=9999)
        return 1;
    return 0;
END
GO

CREATE FUNCTION [dbo].[fun_chk_req](@v1 sql_variant,@v2 sql_variant)
RETURNS tinyint
AS
BEGIN
    IF (@v1 IS NULL OR (@v2 IS NOT NULL AND @v1 IS NOT NULL))
        return 1;
    return 0;
END
GO

This is run second. This is where I get the error.

CREATE TABLE [dbo].[repair](
[ID] [int] IDENTITY(1,1) NOT NULL,
[year] [smallint] NULL 
    CONSTRAINT year_cnstr CHECK (dbo.fun_chk_year(year)=1),
[year_completed] [smallint] NULL
    CONSTRAINT comp_cnstr CHECK (dbo.fun_chk_year(year_completed)=1),
CONSTRAINT yr_and_comp_cnstr CHECK ([dbo].[fun_chk_req](year_completed,year)=1),
PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
user2825184
  • 1
  • 1
  • I think the semi-colons in your function may be a problem. Try removing them both. – Matt Jul 02 '15 at 13:15
  • and there are to many opening brackets for the closing brackets – Shane_Yo Jul 02 '15 at 13:15
  • Just tested the semi-colons. They seem to work fine. – Matt Jul 02 '15 at 13:17
  • Try using [dbo]..[fun_chk_req] with 2 dots in stead of 1, or just remove the [dbo]. – Luc Jul 02 '15 at 13:22
  • Make sure you create the function before you create the column constraint that uses the function. The order is important; It's not clear what you have executed. Your alter table statement is not valid syntax... it looks like a CREATE TABLE statement but just the word CREATE was changed to ALTER. – Brian Pressler Jul 02 '15 at 13:26
  • you didn't close your create table most likely - probably causing the syntax issue – Shane_Yo Jul 02 '15 at 13:29

2 Answers2

2

First define the function (which i changed) and after that use it in your table defintion.

By the way, you use a function fun_chk_year which you don't provide in your example. I hope it exists in the end.

Additionally you have forgotten to close the last bracket on your CREATE TABLE.

Try this instead:

/*Function*/
ALTER FUNCTION [dbo].[fun_chk_req](@v1 sql_variant,@v2 sql_variant)
RETURNS tinyint
AS
BEGIN
    IF (@v1 IS NULL OR (@v2 IS NOT NULL AND @v1 IS NOT NULL))
        return 1;

    return 0;
END
GO

/*Table with function call*/
CREATE TABLE [dbo].[repair](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [year] [smallint] NULL 
        CONSTRAINT year_cnstr CHECK (dbo.fun_chk_year(year)=1),
    [year_completed] [smallint] NULL
        CONSTRAINT comp_cnstr CHECK (dbo.fun_chk_year(year_completed)=1),
    CONSTRAINT yr_and_comp_cnstr CHECK ([dbo].[fun_chk_req](year_completed,year)=1)
)

I tried this (using a dummy function for chk_year) and it worked for me.

Ionic
  • 3,884
  • 1
  • 12
  • 33
  • This didn't seem to work [See edited original post] It still returns the same error. – user2825184 Jul 02 '15 at 14:01
  • Definitely not. I executed it and I had the table and the function working. Are you sure you copied the right code and complete? – Ionic Jul 02 '15 at 17:51
  • Just an idea. Don't you try to create a new table? Do you try to alter an existing table? If this is true, please give some feedback. because in this case your code is totally invalid. – Ionic Jul 02 '15 at 19:27
0

Turns out I was creating the function in the master database [DOH!] Switched it to the correct database and it works fine.

Thank you everyone!

user2825184
  • 1
  • 1