Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 stored procedure reference diffrent schema

Author  Topic 

vamsidhar.tangutoori
Starting Member

19 Posts

Posted - 2013-07-22 : 12:24:03
Hi,
I am trying to build a stored procedure reference two diffrent tables with same name but diffrent schemas based on the login_user or who calls the procedure. I have developed the following procedure but still ot able to get it
The table test_table exists two times within the system
User1.test_Table

User2.TestTable

if i am user1 calling the procedure should reefernce user1.test_table and vice versa for user 2. Can anyone please help me

alter procedure dbo.test_proc (@pUsername varchar(150))
with Execute as caller
as
Begin

Select System_User
Select * from Test_Table
End

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-22 : 12:29:08
depends on whats the default schema set for the user. When you do not reference the schema name in select it will try to retrieve table from default schema of the user.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

vamsidhar.tangutoori
Starting Member

19 Posts

Posted - 2013-07-22 : 12:41:23

Hi Visakh,
The default schema for the users is diffrent
User1 defaul schema user1

user2 default schema user2

but still when i execute it the procedure throws error invalid object name test table


quote:
Originally posted by visakh16

depends on whats the default schema set for the user. When you do not reference the schema name in select it will try to retrieve table from default schema of the user.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs


Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-22 : 13:02:37
That may be because you executed it for second user. In that case table name is different as per your posting (User2.TestTable as against Test_Table used in the query)




------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

vamsidhar.tangutoori
Starting Member

19 Posts

Posted - 2013-07-22 : 13:06:20
User_2 is the schema can we have a way so that procedure will select the schema based on the user the table name is still Test_Table but diifrent schemas

quote:
Originally posted by visakh16

That may be because you executed it for second user. In that case table name is different as per your posting (User2.TestTable as against Test_Table used in the query)




------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs


Go to Top of Page
   

- Advertisement -