Expressions Anyone? - 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

Expressions Anyone?

Author

Topic

edb2012
Starting Member

19 Posts

Posted - 2012-01-31 : 17:10:53

Hi everyone,
I want to thank everyone who is helping me with this wild & crazy Stored Procedure I have.

I was wondering if I set an Output Integer and want to identify it from a count as an Expr1 how can I connect the two.

I was trying

Set IsEnrolled = Expr1 but this gives me an Invalid Column Name for it.

Can anyone help me with this?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-01-31 : 17:14:01

Show us the code as your post is confusing.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

edb2012
Starting Member

19 Posts

Posted - 2012-01-31 : 17:17:28

Here it is

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

as

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

Set @IsEnrolled = Expr1

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

return @IsEnrolled

GO

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-01-31 : 17:24:06

Here you go:

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

as

SELECT @IsEnrolled = COUNT(*)
FROM dbo.747 t1 LEFT OUTER JOIN
dbo.Login 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 login
(DoctorID, AccountNumber, Passcode, Email)
select CUSTNMBR, USERDEF1, @Passcode, @Email
from login where USERDEF1 = @AccountNumber
end

GO

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

edb2012
Starting Member

19 Posts

Posted - 2012-01-31 : 17:58:59

It didn't work.

I need to grab a value which returns 1 from a select statement. Unfortunately, my integer @IsEnrolled does not want to grab the return value from the count. My view returns it as an expression but the stored procedure does not connect the integer to the select count statement.

So I tried to create a view and this does not work either. The count works as an expression but not like I did below.

SELECT 1 = COUNT(*)
FROM dbo.747 t1 LEFT OUTER JOIN
dbo.Login t2 ON RTRIM(t1.CUSTNMBR) = t2.DoctorID
WHERE (RTRIM(t1.USERDEF1) = @AccountNumber) AND (LEFT(t1.PHONE1, 10) = @PhoneNum) AND (RTRIM(t2.Email) IS NULL)

edb2012
Starting Member

19 Posts

Posted - 2012-01-31 : 18:01:07

This Returns a 1 in my view in SQL

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-31 : 19:14:38

put a select @IsEnrolled after count expression and check what it returns

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

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-01-31 : 19:21:04

I am so confused what you are saying. If COUNT(*) returns a 1, then @IsEnrolled contains the value 1.

What does this return:
SELECT COUNT(*)
FROM dbo.747 t1 LEFT OUTER JOIN
dbo.Login t2 ON RTRIM(t1.CUSTNMBR) = t2.DoctorID
WHERE (RTRIM(t1.USERDEF1) = @AccountNumber) AND (LEFT(t1.PHONE1, 10) = @PhoneNum) AND (RTRIM(t2.Email) IS NULL)

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

edb2012
Starting Member

19 Posts

Posted - 2012-01-31 : 19:35:45

Hi Tara,
Thank you for your input.
The select statement works fine in a SQL View. The expression column returns a 1 value. However, because I am attaching an ASP web page to this select statement, I addressed an integer which should grab the return value of 1. The problem I am faced is connecting the Integer to the select statement. Once the value is returned, then I dump the data into another table and return a value to my ASP page.

I have made two Stored procedures now. One to grab a value from a view and the other to create a set value in a stored procedure so I can see the value of the integer found in the Stored Procedure. What spins my head is connecting the two together. The ASP page brings back the value of 0 and not 1.

I am sorry this is confusing. I have spent the past two days trying to marry these two functions and we are still in divorce court.

LOL

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-31 : 19:55:48

quote:
Originally posted by edb2012

Hi Tara,
Thank you for your input.
The select statement works fine in a SQL View. The expression column returns a 1 value. However, because I am attaching an ASP web page to this select statement, I addressed an integer which should grab the return value of 1. The problem I am faced is connecting the Integer to the select statement. Once the value is returned, then I dump the data into another table and return a value to my ASP page.

I have made two Stored procedures now. One to grab a value from a view and the other to create a set value in a stored procedure so I can see the value of the integer found in the Stored Procedure. What spins my head is connecting the two together. The ASP page brings back the value of 0 and not 1.

I am sorry this is confusing. I have spent the past two days trying to marry these two functions and we are still in divorce court.

LOL

then that sounds like issue is with returning value to ASP. try this and see if you're getting value outside


DECLARE @Ret int

EXEC dbo.tdlproc_WeirdPlanet @AccountNumber =your value,
@PhoneNum  =your value,
@Email  =your value,
@Passcode  =your value,
@Ret OUT

SELECT @Ret

if this is working try posting asp code in asp forums and fixing it as issue then will be definitely at the asp side

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

edb2012
Starting Member

19 Posts

Posted - 2012-02-01 : 10:21:05

Actually I made a stored procedure just to grab a value in it. It looks like this ...

CREATE procedure dbo.WildPlanet2
@AccountNumber varchar(50),
@PhoneNum varchar(21),
@Email varchar(100),
@Passcode varchar(50),
@IsEnrolled Integer Output

as

set @IsEnrolled = "5"

return @IsEnrolled
GO

I made a response write into my asp code. I can change to any number on this stored procedure and it returns a value. My problem has to do with the connecting of the two statements. I have a view that works and returns a value of 1. So here is what I did ... pleas see below ....

CREATE procedure dbo.WildPlanet2
@AccountNumber varchar(50),
@PhoneNum varchar(21),
@Email varchar(100),
@Passcode varchar(50),
@IsEnrolled Integer Output

as

set @IsEnrolled = (SELECT COUNT(*)
FROM dbo.747 t1 LEFT OUTER JOIN
dbo.Login t2 ON RTRIM(t1.CUSTNMBR) = t2.DoctorID
WHERE (RTRIM(t1.USERDEF1) = @AccountNumber) AND (LEFT(t1.PHONE1, 10) = @PhoneNum) AND (RTRIM(t2.Email) IS NULL))

return @IsEnrolled
GO

The value I get back from this is a 0 but it really should be a 1 based on the view that works. Just one of these crazy stored procedures that shows no errors and returns a value not correct.

I haven't an idea I have done yet. I have replaced values in the view to prove the return works. Let me try this in the stored procedure.

edb2012
Starting Member

19 Posts

Posted - 2012-02-01 : 10:40:34

WOW! I got it to work. What happened is the call for the @ Values in the Stored procedure did not follow the calls to the parameters in the ASP. The datatypes were correct but the lines were mixed up.

THIS HERE ...

.Parameters.Append objCommand.CreateParameter("@AccountNumber", adChar, adParamInput, 50, session("accountnumber"))
.Parameters.Append objCommand.CreateParameter("@PhoneNum", adChar, adParamInput, 21, session("PhoneNum"))
.Parameters.Append objCommand.CreateParameter("@Passcode", adChar, adParamInput, 50, session("passcode"))
.Parameters.Append objCommand.CreateParameter("@Email", adChar, adParamInput, 100, session("emailaddress"))
.Parameters.Append objCommand.CreateParameter("@IsEnrolled", adInteger, adParamOutput)

HAD TO FOLLOW THIS AT THE STORED PROCEDURE LINE BY LINE.

@AccountNumber varchar(50),
@PhoneNum varchar(21),
@Passcode varchar(50),
@Email varchar(100),
@IsEnrolled Int OUTPUT

Thank you thank you thank you everyone.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-01 : 12:57:38

ok...glad that you sorted it out finally

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources