Error? Operand type clash: uniqueidentifier is incompatible...

[sonname]

I have a function where I return a table and I get the error "Operand type clash: uniqueidentifier is incompatible with int." when I try to create the function. For example

Create function myFunction(
@parameterInt int
)
Returns @retTable
TABLE(
invoiceNumber int,
other varchar(32)
)
AS
BEGIN
INSERT INTO @retTable
SELECT invoiceNumber, other
from invoices where id = @parameterInt

RETURN
END
go

What is causing this error? Thanks in advance.

 

[gmmastros]

run this, and post the results back here.

Select Data_Type, Column_Name
From   Information_Schema.Columns
Where  Table_Name = 'Invoices'
       And Column_Name In ('InvoiceNumber','Other')

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLBill]

It sounds like @retTable already exists and one column (either invoiceNumber or other) is maybe IDENTITY and you are trying to insert a value into it.

-SQLBill

Posting advice: FAQ481-4875

 

[sonname]

George I ran that query and I get an int and a varchar as results as datatypes for those 2 columns.

 

[gmmastros]

Run it again, but this time...

Select Data_Type, Column_Name
From   Information_Schema.Columns
Where  Table_Name = 'Invoices'
       And Column_Name = 'Id'

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[sonname]

SQLBill--I don't understand how @retTable could already exist since this is not a permanent table, but just a variable?

 

[sonname]

George--I ran it again and I get an int as a datatype, this just doesn't make sense to me.

 

[gmmastros]

Maybe the 'real' problem is the way you are calling it. Try this...

Select * from dbo.myFunction([!]Some int that should return data[/!])

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom