Using a UDF to derive a singular vaule 2

[NuJoizey]

ok, what am i doing wrong here? this sproc is choking on my UDF called ReturnCompanyID which eats a parameter called @CompanyLegalName - I get the error Invalid object name 'dbo.ReturnCompanyID'.

ALTER PROCEDURE [dbo].[usp_AddNewProducer]
	-- Add the parameters for the stored procedure here
	@ProducerName varchar(150),	
	@Website varchar (150),
	@Add1 varchar,
	@Add2 varchar,
	@City varchar,
	@StateID int,
	@CountryID int,
	@PostalCode varchar,
	@Phone varchar,
	@Fax varchar

AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	INSERT INTO COMPANY
	  (CompanyTypeID, CompanyLegalName, CompanyRefName, CompanyWebsite)
	VALUES
	  ('2', @ProducerName, @ProducerName, @Website)

	INSERT INTO COMPANY_BRANCH
		(CompanyID, StateID, CountryID, Address1, Address2, City, PostalCode, Phone, Fax, Active)
	VALUES
		[COLOR=red](dbo.ReturnCompanyID(@ProducerName)[/color], @StateID, @CountryID, @Add1, @Add2, @City, @PostalCode, @Phone, @Fax, 'True')

END

RETURN

...and here is my UDF

ALTER FUNCTION [dbo].[COLOR=red][ReturnCompanyID][/color]
(
	-- Add the parameters for the function here
	@ProducerName varchar(255)
)
RETURNS TABLE
AS
	
RETURN (SELECT CompanyID FROM COMPANY WHERE CompanyLegalName=@ProducerName)

 

[gmmastros]

I think the problem is with the UDF.

You said the UDF derives a singular value. This is also known as a scalar. The problem is that your UDF Returns TABLE.

Instead, you should use something like this...

Create FUNCTION [dbo].[ReturnCompanyID]
(
    -- Add the parameters for the function here
    @ProducerName varchar(255)
)
RETURNS Int
AS
  Begin  
    RETURN (SELECT CompanyID FROM COMPANY WHERE CompanyLegalName=@ProducerName)
  End

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[NuJoizey]

Hi gmmastros - i tried that. When i test the sql code it doesn't like it, the error is:

Msg 102, Level 15, State 31, Procedure ReturnCompanyID, Line 30
Incorrect syntax near 'RETURN'.

 

[NuJoizey]

oops. no you're right. I forgot the BEGIN and END

 

[alexjones]

Is CompanyID in your UDF select

RETURN (SELECT CompanyID FROM COMPANY WHERE CompanyLegalName=@ProducerName)

an integer or some other standard datatype? If it is, you'll want to return that datatype from your UDF, rather than a table.

 

[NuJoizey]

ok, here is my UDF:

ALTER FUNCTION [dbo].[ReturnCompanyID]
(
	-- Add the parameters for the function here
	--<@Param1, sysname, @p1> <Data_Type_For_Param1, , int>
	@ProducerName varchar(255)
)
RETURNS bigint
AS
BEGIN
	
	
RETURN (SELECT CompanyID FROM COMPANY WHERE CompanyLegalName=@ProducerName)

END

and when i test it, it seems to be fine, but when I run it, I now get:

Msg 2010, Level 16, State 1, Procedure ReturnCompanyID, Line 30
Cannot perform alter on 'dbo.ReturnCompanyID' because it is an incompatible object type.

I checked the table, and CompanyID is of type bigint. so now what?

 

[gmmastros]

Have you tried dropping it, and then creating it?

[tt][blue]Drop Function dbo.ReturnCompanyId[/blue][/tt]

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[NuJoizey]

that did it.

thank goodness for you folks.