scope? some sp_ prcs use db_name() called from, others not?

Question

Post reply

scope? some sp_ prcs use db_name() called from, others not?

Lowell

SSC Guru

Points: 323518
More actions
June 2, 2006 at 12:36 pm

#115444

If i have a procedure i call from multiple db's, i create a proc that starts with "sp_" and stick it in the master database. i do not make it a system proc(you know, allowing system updates, set system marking), just make it follow the naming conventions.
here's a couple of examples:
--for users who are too lazy to type "SELECT * FROM"
CREATE procedure sp_show
--USAGE: sp_show gmact
@TblName varchar(128)
--WITH ENCRYPTION
As
Begin
exec('Select * from ' + @TblName)
End
CREATE procedure sp_find
@findcolumn varchar(50)
as
begin
set nocount on
select sysobjects.name as TableFound,syscolumns.name as ColumnFound
from sysobjects
   inner join syscolumns on sysobjects.id=syscolumns.id
   where sysobjects.xtype='U'
and (syscolumns.name like '%' + @findcolumn +'%'
   or sysobjects.name like '%' + @findcolumn +'%' )
order by   TableFound,ColumnFound
end
call either of these from any database, and it uses sysobjects from the database you call it from to find the information, as expected.
so now i try the function below, and the second select statement raises an error because the table SECUSER doesn't exist in master; but the first statement was being used to check it's existance;
can anyone tell me why the second exists statement does not use the current database that the proc is being called from to find the SECUSER table?
CREATE PROCEDURE sp_togglepw
AS
DECLARE @PW VARCHAR(100)
IF EXISTS(SELECT NAME FROM SYSOBJECTS WHERE NAME='SECUSER' AND XTYPE='U')
    BEGIN
      IF EXISTS(SELECT PASSWORD FROM SECUSER WHERE PASSWORD='1549C5C96E2BEE4868E51190E7CD4082BE3325CB94DAA65703C1438E571ED532')
        BEGIN
          UPDATE SECUSER SET PASSWORD= 'ORWfnh3zll29OQ2yYyqx7HVt8aa8J1sonR+MH9Rcq2V7gD6U'
          PRINT 'Admin password set for NEW security'
        END
      ELSE
        BEGIN
          UPDATE SECUSER SET PASSWORD='1549C5C96E2BEE4868E51190E7CD4082BE3325CB94DAA65703C1438E571ED532'
          PRINT 'Admin password set to for OLD security'
        END
    END
ELSE
    PRINT db_name() + ' does not contain the SECUSER table.'
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 4 posts - 1 through 3 (of 3 total)

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

stax68 SSChampion Points: 11711 More actions · Answer 1

I don't know (yet). How do you know it doesn't? I can't reproduce it.

Tim Wilkinson

"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant

Lowell SSC Guru Points: 323518 More actions · Answer 2

Thanks Tim;

it's wierd, when i run the the statement in question i get an error stating that SECUSER (which is a table in the database i called the proc from) does not exist; but the query above it did not fail when it checked for it's existance; I'll paste the error and run the execution plan; that'll be of more help diagnosing this i think.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

stax68 SSChampion Points: 11711 More actions · Answer 3

Try owner(schema) qualifying the table name. And yes, post the error and plan if that doesn't work.

Tim Wilkinson

"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant