Query isn't working with Where clause !

Question

Query isn't working with Where clause !

ankitwaitshere

Old Hand

Points: 325
More actions
July 22, 2008 at 10:03 pm

#189981

Hello,
I have run into a wierd situation. I am calling a stored proc, say X. In proc X, I am getting the data from a staging table and putting it into a cursor. After getting the variables from the cursor, I am calling another Stored Proc Y inside that cursor.
Now inside Y, there is a validation check like this:
IF NOT EXISTS (
SELECT
*
FROM
productowner
WHERE
PROD_ID = @prod_id
AND OWNER_ID = @owner_id
// These are the proc Y parameters, whose value passed from the cursor in proc X
)
BEGIN
exec system_RaiseError 60081, "Invalid owner for product"
return (1)
END
Problem: The above statement gets failed and returns Error. (Btw, I've checked the table and record exists in the dbo.productowner table)
Now - in order to trouble shoot, I have placed a statement before above IF as:
Select 'PROD->', @prod_id, 'OWNER->', @owner_id -- this works and gives correct values
But somehow the query,
Select * from productowner where PROD_ID = @prod_id and OWNER_ID = @owner_id -- doesn't return any record, when I have this statement keptin the proc and call - exe dbo.X
Now when I just keep the query with hardcoded value in the stored proc as:
Select * from productowner where OWNER_ID = 101 -- and call : exe dbo.X it still fails
However, if I run the above query (OWNER_ID = 101) in the same window, it gives me bunch of rows, but somehow it's not working inside the stored proc dbo.Y 🙁
...and yeah, I have checked the permission on all the involved DB objects and Owner is "dbo".
Can someone please help me troubleshoot, what could be the cause?
Appreciate the help/suggestions. Thanks a lot!

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

IN_Sandeep SSCertifiable Points: 6259 More actions · Answer 1

Ankit,

As far query concern your sql query is correct, what I feel just modify the comment line by using "--" instead of "//" and Comment the return statements too by using "--".

just use this query:

IF NOT EXISTS (
SELECT
*
FROM
productowner
WHERE
PROD_ID = @prod_id
AND OWNER_ID = @owner_id
-- These are the proc Y parameters, whose value passed from the cursor in proc X
)
BEGIN
exec system_RaiseError 60081, "Invalid owner for product"
-- return (1)
END

hope it will work for you now...

Let me know, If you have any concern..

Cheers!

Sandy.

--

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 2

If I understand the question, you mean that you have a proc, Y. Within that proc it makes a call to another proc X. Within the proc X you're attempting to refer to parameters that were set in proc Y?

Is that correct? If so, that's you're problem. You can't refer to parameters outside of any proc, regardless of where that proc is called. So, in order to get the procs from Y to X, you'll need to pass them as paramters down to that next proc.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

ankitwaitshere Old Hand Points: 325 More actions · Answer 3

No - Its like thta:

I am making a call to Proc X. Inside Proc X I have a cursor, with which, I get all the necessary parameters to be passed to Y and make a call to Y. Thats it.

But somehow Y was returning an error. When I debugged that, I found this wierd situation.

Any advice...

ankitwaitshere Old Hand Points: 325 More actions · Answer 4

Sandy,

Appreciate your reply. But it was just a type while writing the code here in forum.

I dont have that comment in code. it was just to explain here in forum.

Any suggestions would be appreciated....

thanks.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 5

Two things:

1) what is the error message

2) Please show us the whole listing of Proc

y.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

ankitwaitshere Old Hand Points: 325 More actions · Answer 6

Thanks a lot guys!

The code already had the "delete" statement inside a transaction. So actually I had selects inside of the transaction - till the time transaction was running - and wasn't showing coz transaction was rolled back after the error.