Can I set the default schema for a stored procedure?

Question

I am working on the next update for StackQL.

One thing I want to do is the ability to request multiple issues. Therefore, when I downloaded the October data, for example, I did not delete the old September database. He is still there. In fact, you can even query it by including the database name as follows:

select top 10 * from SO_Sept09..Posts 

This will be even more important as they begin to provide data for ServerFault and SuperUser.

But I do not like to have a whole bunch of databases to support this. I would rather put all the data in one database and split each separate set into its own schema. But to make this possible, I need to be able to set the default schema as part of the stored procedure that executes the request, based on the parameter passed to the stored procedure, which tells it which database the user selected from the drop-down list in the future for display on the toolbar.

Queries in StackQL are ultimately simply passed to the exec() function as follows:

 exec(@QueryText) 

Is there anything I can do in the stored procedure or add a QueryText (ala USE [DatabaseName] ) to the string to set the standard schema used in the query?

Answer 1

There are several ways to do this in different places here, but not quite. The way to do this is:

• Create a unique login and user for each scheme

• Make these users the owners of every other scheme.

• Sets each such default scheme for the user that belongs to them.

• Use the EXECUTE ('sql commands') AS USER = 'schema-owner' syntax to execute your SQL commands in the context of this default schema.

The following script demonstrates this:

 --====== Create the Login for the User: CREATE LOGIN [UserTest1] WITH PASSWORD='whatever', DEFAULT_DATABASE=[TestUsers], DEFAULT_LANGUAGE=[us_english] GO --====== Make a User for the Login: CREATE USER [UserTest1] FOR LOGIN [UserTest1] GO --====== Make a Schema owned by the User and default to it: -- (I assume that you already have the schemas) CREATE SCHEMA [UserTest1] AUTHORIZATION [UserTest1] GO ALTER USER [UserTest1] WITH DEFAULT_SCHEMA=[UserTest1] GO --====== Make a sProc in dbo CREATE PROCEDURE [dbo].[TestSchema_Exec] AS SELECT 'executing in schema [dbo]' GO --====== Make a similar sProc in New Schema CREATE PROCEDURE [UserTest1].[TestSchema_Exec] AS SELECT 'executing in schema [UserTest1]' GO --========= Demonstrate that we can switch Default Schemas: EXEC('TestSchema_Exec') EXEC('TestSchema_Exec') AS USER = 'UserTest1' 

Answer 2

Besides changing @QueryText itself, the only thing I can imagine is the user's default scheme:

 ALTER USER SO_Sept09_Reader WITH DEFAULT_SCHEMA = SO_Sept09 

... and then connect as a different user for each circuit you want to use. Hacking hacks.

But if your query is dynamically constructed anyway (and I'm sure you know why this is often not a good idea), it would be easier to just add a schema placeholder to the query text and pass the schema name along with the request to the replacement function.

Answer 3

Another possibility is to generate copies of each SP in each schema, the name of the unmodified table in SP refers to tables in the same schema.

Note that this does not work with dynamic SQL inside such an SP:

 CREATE PROCEDURE schema_a.SP @somesql AS varchar(MAX) AS BEGIN EXEC ( @somesql ) END CREATE PROCEDURE schema_b.SP @somesql AS varchar(MAX) AS BEGIN EXEC ( @somesql ) END 

It will not work because the schema binding is lost inside EXEC.

So far it is:

 CREATE PROCEDURE schema_a.SP AS BEGIN SELECT * FROM tbl -- Will use schema_a.tbl first END CREATE PROCEDURE schema_b.SP AS BEGIN SELECT * FROM tbl -- Will use schema_b.tbl first END 

works great.

Similarly:

 EXEC ( 'EXEC schema_a.SP' ) 

obviously will work fine.

Answer 4

Tired, but: can you combine the data of different schemes into one view and add a column that calls the name of the scheme?

 CREATE VIEW AllPosts AS SELECT Data1, Data2, 'Sept09' AS Partition FROM SO_Sept09..Posts UNION ALL SELECT Data1, Data2, 'Oct09' AS Partition FROM SO_Oct09..Posts ... SELECT * FROM AllPosts WHERE Partition = 'Sept09' SELECT * FROM dbo.AllPosts('Sept09') -- if use table-valued function instead 

Answer 5

Well, I have a new way to do this, which might work a little better for me. This is a variation of my comment on Michael Petrotte:

But this gives me an idea, perhaps to have multiple users and select a connection string on the fly.

I will only make one user to execute these requests, but I will change the connection string "on the fly" to indicate the correct source directory.