SQL Server Stored Procedure - not returning expected data

Question

I have this stored procedure that doesn't return the record i want and that record exists:

ALTER PROCEDURE [dbo].[Student_SearchByName]
@FName NVarchar
AS
BEGIN
   SELECT 
       StudentID, FName, LName, Student.BirthDate, 
       DATEDIFF(hour, BirthDate, GETDATE()) / 8766 As 'Age' 
   FROM
       Student 
   WHERE 
       Student.FName = @FName
END

I fill the parameter in Visual Studio and also tried:

SELECT 
    StudentID, FName, LName, Student.BirthDate,
    DATEDIFF(hour, BirthDate, GETDATE()) / 8766 As 'Age' 
FROM
    Student 
WHERE
    Student.FName = 'Name'

and it worked, but when I use the parameter I get no result!

marc_s · Accepted Answer · 2013-11-26 13:13:10Z

2

If you specify this:

ALTER PROCEDURE [dbo].[Student_SearchByName]
    @FName NVarchar

then your @FName is exactly ONE character long - you porbably don't want that.

You should always specify an explicit length for any (N)VARCHAR variables and parameters!

Use something like this:

ALTER PROCEDURE [dbo].[Student_SearchByName]
   @FName NVarchar(100)

or whatever makes sense for you.

answered Nov 26, 2013 at 13:13

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

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

Comments

kiks73 · Accepted Answer · 2013-11-26 13:13:33Z

2

You need to specify the length of the NVarchar, otherwise SQL Server truncates to the first char. So it become:

ALTER PROCEDURE [dbo].[Student_SearchByName]
// Student.FName always less than 500 characters
@FName NVarchar(500)
AS
BEGIN
    SELECT StudentID,FName,LName,Student.BirthDate,DATEDIFF(hour,BirthDate,GETDATE())/8766      As 'Age' 
    from Student where Student.FName=@FName
END

answered Nov 26, 2013 at 13:13

kiks73

3,8383 gold badges30 silver badges56 bronze badges

Collectives™ on Stack Overflow

SQL Server Stored Procedure - not returning expected data

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related