在sql server中返回null的用户定义函数

I've created a user defined scalar function in sql server 2005, and I want it to return me the id based on the passing parameter name. But the following function always returns me null, even the passing name already exists in table. Could anyone please tell me the reason?

我在sql server 2005中创建了一个用户定义的标量函数，我希望它根据传递的参数名返回id。但是，下面的函数总是返回null，甚至在表中已经存在传递的名称。谁能告诉我原因吗?

create function IsNameExist(@Name varchar)
returns int
As
Begin
Declare @Id int
Select @Id = ProductId from [Product] where ProductName = @Name
return @Id
End

1 个解决方案

#1

Please note that you did not specify the length for the function parameter datatype. So by default in this case it becomes 1 and your query inside the function fails. Try this and see

请注意，您没有指定函数参数数据类型的长度。在这种情况下，默认情况下，它变成1，你的查询在函数中失败。试试这个,看看

create function 
IsNameExist(@Name varchar(100)) 
returns int As 
Begin 
Declare @Id int 
Select @Id = ProductId from [Product] where ProductName = @Name 
return @Id 
End

Also refer this post http://beyondrelational.com/blogs/madhivanan/archive/2007/12/04/column-length-and-data-length.aspx

也可以参考这篇文章http://beyondrelational.com/blogs/madhivanan/archive/2007/12/04/column-length- data-length.aspx

#1