You have to be more specific...
Which login? Sql Server's? Your asp.net application?
What are you going to send to your stored procedure? If you're going to include the password, will it be sent in plain text? etc. etc. etc.
Read your question, and imagine you're some stranger trying to understand it.
Anyway, I suppose you're looking for something else, but here's what I can do with the provided information:
CREATE PROCEDURE [dbo].[CheckLogin]
@UserName NVARCHAR(128), @Password NVARCHAR(512), IsValid BIT OUTPUT
AS
BEGIN
SET @IsValid = SELECT COUNT(User.UserName) FROM [dbo].[User] WHERE User.UserName = @UserName AND User.UserPassword = @Password
END
Hope it helps