CODEWITHSUNDEEP
sqlsql-serversql-server-2005function
Dusk
Dusk

Reputation: 2201

User defined function returning null in sql server

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?

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

Upvotes: 4

Views: 9378

Answers (1)

Madhivanan
Madhivanan

Reputation: 13700

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

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

Upvotes: 9

Related Questions