SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 stored procedure reference diffrent schema
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

vamsidhar.tangutoori
Starting Member

19 Posts

Posted - 07/22/2013 :  12:24:03  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 07/22/2013 :  12:29:08  Show Profile  Reply with Quote
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 - 07/22/2013 :  12:41:23  Show Profile  Reply with Quote

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

India
52325 Posts

Posted - 07/22/2013 :  13:02:37  Show Profile  Reply with Quote
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 - 07/22/2013 :  13:06:20  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000