Ignoring any possible problems with the connection from the asp.net front-end; the RETURN following the END in the stored procedure will give you a syntax error. So won't run.
If you're using Query Analyser/SQL Server Management Studio then highlighting a block of SQL and hitting <ctrl-f5> will give you a syntax check on that block. Can save a lot of time.
Just tried this and it works fine on SQL2005....
create table users
(
ID int identity
constraint PK_users primary key (ID),
[name] nvarchar(50) not null
constraint DF_username default('')
)
insert into [users] ([name]) values('sheila')
select * from users
create procedure adduser( @name nvarchar(50) )
as
begin
insert into [users] ([name]) values(@name)
return
end
exec adduser 'jim'
select * from users