Stored Procedure not working - 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

Stored Procedure not working

Author

Topic

edb2012
Starting Member

19 Posts

Posted - 2012-01-30 : 14:06:28

Hi Everyone,
I have this situation where my Stored procedure is not returning a value of 1. Instead it is 0.

I made a view for my select statement, which had fixed parameters and it returned a value of 1. Then I dropped it into my Stored Procedure and received no errors when clicked the check button.

However, when I go to my asp code which I placed a response write on the asp page, it returns a 0 instead of a 1. I also made response.writes into the other fields too and all of them had values as well.

Here is my Stored Procedure ...

CREATE procedure dbo.tdlproc_ProcessEnrollment3
@AccountNumber varchar(50),
@PhoneNum varchar(21),
@Email varchar(100),
@Passcode varchar(50),
@IsEnrolled tinyint OUTPUT

as
select @IsEnrolled = (SELECT COUNT(*) AS Expr1
FROM dbo.COMPANYINFO t1 LEFT OUTER JOIN
dbo.LOGININFO t2 ON RTRIM(t1.CUSTNMBR) = t2.DoctorID
WHERE (RTRIM(t1.USERDEF1) = @AccountNumber) AND (LEFT(t1.PHONE1, 10) = @PhoneNum) AND (RTRIM(t2.Email) IS NULL))

if @IsEnrolled = 1
begin
insert into tdlDoctorWebLogin
(DoctorID, AccountNumber, Passcode, Email)
select CUSTNMBR, USERDEF1, @Passcode, @Email
from RM00101 where USERDEF1 = @AccountNumber
end

return @IsEnrolled
GO

If anyone can help me, I would greatly appreciate it :)

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-01-30 : 14:14:23

If you run the stored procedure from a SSMS window, is it returning 1?

Also, I see that you have @IsEnrolled as a stored proc parameter and the return value. Depending on which of these you are trying to read in your ASP.Net code, you would need to declare the parameter with ParameterDirection = ReturnValue or ParameterDirection = InputOutput (or Output).

edb2012
Starting Member

19 Posts

Posted - 2012-01-30 : 14:26:00

Thank you for the input Sunitabeck. Basically I am using IsEnrolled as a TINYINT.

My SQL View comes up with a value of 1 which tells me it recognizes the database. However, It brings back a 0 value in my asp page as a return.

Here is what my asp looks like.

Set objCommand = Server.CreateObject("ADODB.Command")
With objCommand
.ActiveConnection = objConnection
.CommandText = "tdlproc_ProcessEnrollment3"
.CommandType = adCmdStoredProc
.Parameters.Append objCommand.CreateParameter("@AccountNumber", adChar, adParamInput, 50, session("accountnumber"))
.Parameters.Append objCommand.CreateParameter("@Email", adChar, adParamInput, 100, session("emailaddress"))
.Parameters.Append objCommand.CreateParameter("@Passcode", adChar, adParamInput, 50, session("passcode"))
.Parameters.Append objCommand.CreateParameter("@PhoneNum", adChar, adParamInput, 21, session("PhoneNum"))
.Parameters.Append objCommand.CreateParameter("@IsEnrolled", adInteger, adParamOutput, 1)
.Execute
End With

Response.write(objCommand.Parameters("@IsEnrolled").Value)

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-01-30 : 14:55:15

I don't have the environment to test this, but try using adSmallInt or adUnsignedTinyInt instead of adInteger (not sure if those are the exact enum names for the data types). Alternatively, change the stored proc to make @IsEnrolled int.

edb2012
Starting Member

19 Posts

Posted - 2012-01-30 : 17:01:38

Have debugged this problem down to the @IsEnrolled. Somehow the value of 1 which is initially identified in the ASP code interferes with addressing the select statement of the Integer. I am still working on this and will come up with a result to share :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-30 : 17:03:14

this will give you full list of enum values

http://www.w3schools.com/ado/met_comm_createparameter.asp#datatypeenum

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources