CODEWITHSUNDEEP
sql-server-2008stored-procedures
Aada
Aada

Reputation: 1640

Execute stored procedure with one parameter as an input

I am using stored procedure. My stored procedure is 

Create PROCEDURE [dbo].[SP_HouseHoldMembers]
(
@StudentID varchar
)
AS
BEGIN
SELECT MemberID,FirstName+''+LastName,Age,Sex,Education
FROM Student SD 
WHERE SD.StudentID=@StudentID
END

I execute it like this: 

EXEC [dbo].[SP_HouseHoldMembers] @StudentID='123'

BUT ITS NOT SHOWING THE RESULT AND WHEN I execute it simply by using simple query taking this 123 as an input then it gives the desired result.

Please someone tell where i am going wrong

Upvotes: 1

Views: 26232

Answers (2)

marc_s
marc_s

Reputation: 754468

You've defined your parameter to be varchar - that means: variable length string of 1 character length!!

You need to give that varchar a explicit length!

Create PROCEDURE [dbo].[SP_HouseHoldMembers]
(@StudentID varchar(20) )
AS
BEGIN
   SELECT MemberID,FirstName + '' + LastName, Age, Sex, Education
   FROM Student SD 
    WHERE SD.StudentID = @StudentID
END

Upvotes: 2

Alex K.
Alex K.

Reputation: 175766

Your using this parameter:

@StudentID varchar

This is a 1 charactrer string, so passing '123' is truncated to '1'

Instead; 

@StudentID int

or

@StudentID varchar(<insert appropriate size>)

Upvotes: 6

Related Questions