Reputation: 2276

SQL Server 2005 I am not able to read from a table

Please suppose that in SQL Server 2005, if you launch the following query:

SELECT CHICKEN_CODE FROM ALL_CHICKENS WHERE MY_PARAMETER = 'N123123123';

you obtain:

as result.

Now, I would like to write a function that, given a value for MY_PARAMETER, yields the corresponding value of CHICKEN_CODE, found in the table ALL_CHICKENS.

I have written the following stored function in SQL Server 2005:

ALTER FUNCTION [dbo].[determines_chicken_code]
(
    @input_parameter                         VARCHAR 
)
RETURNS varchar
AS 
BEGIN
    DECLARE @myresult varchar

    SELECT @myresult  =  CHICKEN_CODE
    FROM dbo.ALL_CHICKENS
    WHERE MY_PARAMETER = @input_parameter

    RETURN @myresult
END

But if I launch the following query:

SELECT DBO.determines_chicken_code('N123123123')

it yields:

NULL

Why?

Thank you in advance for your kind cooperation.

Upvotes: 0

Answers (4)

Asif

Reputation: 2677

Specify a length for @input_parameter, @myresult as by default varchar lengh is 1.

Upvotes: 1

Romil Kumar Jain

Reputation: 20765

As per other PS, You can store only one char in the @myresult because you have not specified any length, bcoz 1 char length is default for Varchar datatype.

Why we are getting NUll, not the first char:

If there are multiple records are filtered on basis of Where clause in ALL_CHICKENS table then the value of CHICKEN_CODE column is picked up from last row in ALL_CHICKENS table.

It seems that the last row has null value in CHICKEN_CODE column.

Upvotes: 1