Problem with stored procedure - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Problem with stored procedure

Author

Topic

zoom14151
Starting Member

34 Posts

Posted - 2006-02-20 : 11:12:09

I'm new to stored procedures and I'm having a problem. I have a login.aspx page that works fine, but I need to retrieve the last name of the UserName that logs in the web site. I wrote this stored procedure that I though would work but get an error. Here is the procedure.

ALTER PROCEDURE dbo.Analyst_name

@Username varchar (20),
@@Analyst varchar(50) output

AS

SET @@Analyst = (Select Lname from UserList where User_Username = @Username)

return @@Analyst

THis is the error that I get back.

Syntax error converting the varchar value 'Jones' to a column of data type int.

I check the procedure in Query Analyzer and it gives error number 245.

JoeNak
Constraint Violating Yak Guru

292 Posts

Posted - 2006-02-20 : 11:18:36

Remove the "return" line. You've already declared @@Analyst as an output param.

The return statement only allows you to return an INT value (see BOL).

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2006-02-20 : 11:42:52

consider:


ALTER PROCEDURE dbo.Analyst_name

@Username varchar (20),
@@Analyst varchar(50) output
AS
Select @@Analyst =Lname from UserList where User_Username = @Username
return @@Error
go

This sets your @@Analyst, and allows you to check for errors by using the exec @rc=Analyst_name when executing the SP.

CiaO

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

zoom14151
Starting Member

34 Posts

Posted - 2006-02-20 : 11:59:14

I tried it and it gave error message Error converting data type varchar to int

zoom14151
Starting Member

34 Posts

Posted - 2006-02-20 : 12:04:04

I ran profiler and this is the procedure

declare @P1 int
set @P1=NULL
exec dbo.[Analyst_name] @@Analyst = @P1 output, @Username = 'a'
select @P1

zoom14151
Starting Member

34 Posts

Posted - 2006-02-20 : 12:33:38

Sorry, one more thing, I ran the stored procrdure in query analyzer debugger and if I set @@Analyst is null the procedure runs fine.