0

Whenever I call a stored procedure that contains more than a single select statement I get no rows in the resultset although running the same code outside the SP returns the expected result. This has started happening by the time a database backup from a production environment was installed on the development server.

The example below returns no rows when run in Enterprise Manager 2008: 

exec dbo.USP_S_CustomerLimit '00000053-0000-1148-5300-000000000000'

If I however execute all the code in that procedure by itself (like select it and press "run") I get the expected amount of result rows:

SELECT * FROM CustomerLimit WHERE Department =
(SELECT Department FROM Customer WHERE CustomerID = CONVERT(BINARY(16), CONVERT(UNIQUEIDENTIFIER, '00000053-0000-1148-5300-000000000000')))

I have also tried this variant of the code which unfortunately behaves in exactly the same way:

SELECT CustomerLimit.* FROM CustomerLimit
JOIN Customer ON Customer.Department = CustomerLimit.Department
WHERE Customer.CustomerID = CONVERT(BINARY(16), CONVERT(UNIQUEIDENTIFIER, '00000053-0000-1148-5300-000000000000'))

I feel it's some sort of right issue but I don't know where to check. I am db_owner on the server and I also belong to a group that is db_owner in the database in question. What could be the cause of this problem? If I create a testprocedure with just one select, f.e Select * from Customer then I get the correct result when executing that procedure but if I write f.e Select * from Customer where CustomerID in (select CustomerID from Customer) then I get no rows.. :(

Vadim Kotov
  • 8,084
  • 8
  • 48
  • 62
user1381873
  • 1
  • 2
  • **What** database system, and which version?? **SQL** is just the Structured Query Language - a language used by many database systems - SQL is **NOT** a database product... stuff like this is very often vendor-specific - so we really need to know what database system you're using.... – marc_s May 08 '12 at 11:36

1 Answers1

0

Check whether you have rights/permission on all these three tables. Check the sys.objects for the listing of these tables.

Romil Kumar Jain
  • 20,239
  • 9
  • 63
  • 92