Power By 专否社区

Assigning a local variable from a table using a join statement

Go To StackoverFlow.com

1

I'm working with SQL, and I can't seem to figure this out for the life of me.

I have a local variable in my stored procedure called @curType. I have two tables, DTXR and DP. DP contains the columns type and programID. DTXR contains the columns programID and QEI. The stored procedure is passed the QEI, and I need to get the type from the table DP and assign it to the local variable @curType.

So, I currently have 

select @curType = [Type] From DP d 
       Join DTXR x on d.ProgramId = x.ProgramID
       where x.QEI = @p_QEI.

@p_QEI is the variable passed into the stored procedure.

The problem I'm running in to is this doesn't seem to set @curType. It works if I manually set the program id like this:

select @curType = [Type] from DP Where DP.ProgramId = 120

But the join statement seems to be setting @curType to null.

2012-04-04 18:26
by CurtisHx
Are you sure there is only one record min DP that matches the record on dtxr? I believe the value of @curType will be the first record that it encounters which may not be the record that you expect it to be - Jeff Hornby 2012-04-04 18:30

2

Actually, this should work. I would check to make sure that the following even returns anything at all (and if it does, what is the first result back?):

   select [Type] From DP d 
   Join DTXR x on d.ProgramId = x.ProgramID
   where dtxr.QEI = @p_QEI

That should be the problem, as here is a fiddle proving that a join does nothing different

2012-04-04 18:29
by Justin Pihony
That's one of the first things I checked and I get exactly what I expect. The data is there. The select statement works. But assigning that to the local variable doesn't seem to play nice - CurtisHx 2012-04-04 18:34
@CurtisHx I just updated my answer with a SQLFiddle that proves that it has to somehow be the data. A join does not mess up setting a variable... - Justin Pihony 2012-04-04 18:39
You're right. It doesn't. Back to the drawing board - CurtisHx 2012-04-04 18:53
Wow. I feel like an idiot. I was passing bad data to the stored procedure. sigh. 6 hours trying to track down the problem, and it was a problem with my app. I'm going to go cry now - CurtisHx 2012-04-04 20:23

0

I'm not sure if your code should works because of WHERE clause. IMO line:

   where dtxr.QEI = @p_QEI

should looks like:

   where x.QEI = @p_QEI

My second hint, please check @p_QEI variable, does it contain the proper value?

2012-04-04 19:07
by Radek
oops. That would be a typo - CurtisHx 2012-04-04 19:17
Ads