User defined function returning null in sql server

Question

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

can you give an example of the statement were you're using this UDF? — rabudde
– rabudde, Commented Jul 25, 2011 at 11:18
Note that @Name will truncate @ 30 chars as your varchar is not declared with a maximum size — Alex K.
– Alex K., Commented Jul 25, 2011 at 11:24
Try with create function IsNameExist(@Name varchar(200)) as Alex's suggestion. — ypercubeᵀᴹ
– ypercubeᵀᴹ, Commented Jul 25, 2011 at 11:28

Madhivanan · Accepted Answer · 2011-07-25 11:32:38Z

9

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

answered Jul 25, 2011 at 11:32

Madhivanan

13.7k1 gold badge28 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

User defined function returning null in sql server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related