wtf, a simple tsql if isn't right... 2

[evilmousse]

What follows is a simple stored procedure that's
not behaving as I expected.

----begin sp--------

ALTER PROCEDURE procTest (
@p_loan_number varchar(10)
) AS
SET NOCOUNT ON

IF (SELECT dbo.t_loan.loan_number FROM dbo.t_loan WHERE dbo.t_loan.loan_number = @p_loan_number) is null BEGIN
select dbo.t_mando.trade_id from dbo.t_mando
END
ELSE BEGIN
select dbo.t_loan.loan_number from dbo.t_loan
END

SET NOCOUNT OFF

----end sp----

now, when i enter junk data into the dialog prompt
for the parameter, i get the appropriate trade_id
column. however, when i enter an existing loan_number,
instead of getting the loan_number column, i get
a trade_id column, filled completely with "#Name?".

i've gotta be missing something simple.

-g

 

[SonOfEmidec1100]

The problem is you are selecting the dbo.t_mando.trade_id, maybe it should be something like this I've also tidied up the sp

CREATE PROCEDURE procTest ( @p_loan_number varchar(10)) AS

IF EXISTS (SELECT dbo.t_loan.loan_number FROM dbo.t_loan WHERE dbo.t_loan.loan_number = @p_loan_number)
select dbo.t_mando.loan_number from dbo.t_mando
ELSE
select dbo.t_loan.loan_number from dbo.t_loan

Hope this will help you.

 

[plantj]

Does the code work outside of a procedure? ie does this run

declare @p_loan_number varchar(10)
select @p_loan_number='89' --try a valid number here

SET NOCOUNT ON

IF (SELECT dbo.t_loan.loan_number FROM dbo.t_loan
WHERE dbo.t_loan.loan_number = @p_loan_number) is null BEGIN
select dbo.t_mando.trade_id from dbo.t_mando
END
ELSE BEGIN
select dbo.t_loan.loan_number from dbo.t_loan
END

 

[pascalsql]

Try this:

ALTER PROCEDURE procTest (
@p_loan_number varchar(10)
) AS
begin
SET NOCOUNT ON

if exists (SELECT 1
FROM dbo.t_loan
WHERE dbo.t_loan.loan_number = @p_loan_number
)
then select dbo.t_loan.loan_number from dbo.t_loan
else select dbo.t_mando.trade_id from dbo.t_mando
end

BTW, do you expect only one line (ie a valid number, loan or trade) or many?
You may consider an output parameter or a function.

 

[evilmousse]

sonofem:

I feel stupid for forgetting about the 'exists'
keyword.. still, this doesn't quite get me
what I want. i amended only one line of what
you posted to be:
'select dbo.t_mando.trade_id from dbo.t_mando'
(i want to prove i can select one thing on one
condition, and something totally different on
another, not just a loan_number either way)
Your method yields a blank column named trade_id
when garbage is entered, and a filled trade_id
column when a valid loannumber is entered.
I want to get a filled loan_number column in the
first case.

plantj:

The point of this testquery is to prove I can
use a parameter to test the existence of
a loan in the loan table, then do one thing
or another based on that condition.
statically setting the loan_number to test
is no good.

pasc:

once i removed the word 'then' from your
sql to get it to work, it yielded the same
results as sonofem's.


thank you all for your help so far, but
i'm still not getting the right results.
i've used integer params before to this
effect (if in=1 select one thing, else select another)
but for some reason using a subquery to test
for the existence of a loan in the loan table
kills my ability to then choose from running
queries that return totally different data.

-g

 

[SQLBill]

evilmousse,

Did you run plantj's suggestion? The point of running that query is to see what will be returned when you aren't using a variable or a procedure.

Point being:

If plantj's query returns what you are expecting to see, then it's a problem in your query - either the use of the parameter or something else.

If plantj's query doesn't return what you are expecting, it's either something wrong with the query itself OR with the DATA.

So, running that query will help narrow down where the issue may be.

-SQLBill

 

[JamesLean]

Let me check I've understood your logic:

If any row exists in the t_loan table that has a loan_number equal to the supplied parameter then you want to return the full list of loan_numbers from t_loan.

Otherwise you want to return the full list of trade_ids from t_mando?

If so then this query is correct:

CREATE PROC myproc
  @p varchar(10)
AS

IF EXISTS (SELECT * FROM t_loan WHERE loan_number = @p)
  SELECT loan_number
  FROM t_loan
ELSE
  SELECT trade_id
  FROM t_mando

--James

 

[evilmousse]

sqlbill:
no, you're right, i didn't.
I gave it a try now, and the results are the same.
i get a blank column of the other case's select
statement's field, instead of the column i asked
for that condition to return.


James:
you have the logic correct, that's exactly what
i want. However, the code you posted is pretty much what
i already have so far, and the results are incorrect.
i get a blank column called 'loan_number' when the
loan is not in the loan table, instead of a
filled 'trade_id' column.

try it out yourself, just what i select in
each case is unimportant, i'm just trying to
prove i can return totally different queries
based on a parameter equaling at least one
loan# in the loan table.

-g

(thanks again for the help)

 

[JamesLean]

Sorry, it works perfectly for me (as I thought it would).

declare @p varchar(10)
set @p = '752522'

IF EXISTS (SELECT * FROM account WHERE accode = @p)
  SELECT accode
  FROM account
ELSE
  SELECT org_id
  FROM org

I get a list of accodes if I enter one that exists, and a list of org_ids if it doesn't exist.
Are you just running this from Query Analyzer rather than through any other application?

--James

 

[evilmousse]

!!!!!

No, actually, I've been doing all this in my access
ADP. I ran the code in the query analyzer and it
succeeded in returning the right values!

..so (why aren't i surprised) this is something
about the access front-end....

so now i've got the exact same stored procedure
returning correctly in query analyzer, and incorrectly
in the access ADP. -_-;;;

hmmm.. i was just using this to test the outer
if i'll later use to do either an update or an
insert... i guess i have it right and it's just
access being funny... i hate that.

-g