CODEWITHSUNDEEP
sql-server-2008stored-proceduressql-server-2000sql-server-2012
Dewitt Harrison
Dewitt Harrison

Reputation: 51

SELECT returns different result when used in a stored procedure

We are starting the upgrade from SQL Server 2000 to SQL Server 2008 then SQL Server 2012 (we cannot go straight from 2000 to 2012).

One of the stored procedures in SQL Server 2000 sets the value of @ErrorMsg to the name column from the sys.objects view where name is a given value. For these purposes here let’s say I am looking for the name zz_BICWS_PersonInfo_Search_1000000000000000000 which does exist in the sys.objects view.

The procedure works in SQL Server 2000 and if I run the following code in SQL Server 2012 it will return the correct name but does not in the stored procedure in SQL Server 2012.

declare @ErrorMsg varchar(1000)

set @ErrorMsg = 'xxxxxxx'

select @ErrorMsg = name
    from bic.dbo.sysobjects 
    Where name = 'zz_BICWS_PersonInfo_Search_10000000000000000000'

print 'test ' + @ErrorMsg

These same lines exist in a stored procedure, except the print line, @ErrorMsg is output to the app calling the procedure. But it returns the initial value xxxxxx using a message box in the app to display the returned value.

But if I change the where clause to 

Where name Like ‘zz_BICWS_PersonInfo_Search_%’

(there are about 40 rows that match this) then I will get the name of the first row found.

Upvotes: 0

Views: 922

Answers (2)

Dewitt Harrison
Dewitt Harrison

Reputation: 51

Thanks to another forum I recieved the answer. I am posting it here for anyone else that might hit the same issue.

There is an important difference between SQL 2000 and later versions. In SQL 2000 all metadata was visible to everyone, and there was no way to change it.

In SQL 2005 and later, you can only see metadata if you have permission to. Whence your SELECT statements hits no rows when it is executed by a plain user from your application. But when you run the SELECT in SSMS connected as sa, or at least with db_owner rights, everything works.

If you rely on metadata being visible you need to grant the permission VIEW DEFINITION to the appropriate users.

GRANT VIEW DEFINITION TO appusers

will grant VIEW DEFINITION on all objects in the database to the role appusers. This statement grants the permission on all objects in the dbo schema:

GRANT VIEW DEFINITION ON SCHEMA::dbo TO appusers

The permission VIEW DEFINITION is implied on an object if you have SELECT or EXECUTE permission on it.

Upvotes: 1

Nick Vaccaro
Nick Vaccaro

Reputation: 5504

I'm having a bit of trouble finding the question in there, but I think the problem is that you are trying to jam 40 rows into a single variable.

Try dropping the @ErrorMsg variable, and just running this:

select name
from bic.dbo.sysobjects 
Where name = 'zz_BICWS_PersonInfo_Search_10000000000000000000'

Upvotes: 0

Related Questions