Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Can I set a default schema for within a stored procedure?

I'm working on the next update for StackQL.

One thing I want to do is have the ability to query over several releases. So when I loaded the October data, for example, I didn't delete the old September database. It's still out there. In fact, you can even still query it by including the database name like this:

select top 10 * from SO_Sept09..Posts

This will be even more important as they start providing data for ServerFault and SuperUser.

But I don't like having a whole bunch of databases out there to support this. I'd much rather put all the data in the same database and separate each distinct set into it's own schema. But to make this possible, I need to be able to set a default schema as part of the stored procedure that runs the query, based on a parameter passed to the stored procedure that tells it which database the user selected from a future drop down list to appear in the tool bar.

Queries at StackQL are eventually just passed to the exec() function like this:

exec(@QueryText)

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

like image 741
Joel Coehoorn Avatar asked Oct 15 '09 00:10

Joel Coehoorn


3 Answers

There are pieces of how to do this in various places here, but not altogether. The way to do this is:

  1. Make a unique login & user for each schema

  2. Make these users the owners of each different schema.

  3. Set each such user's default schema to be the schema that they own.

  4. Use the syntax EXECUTE ('sql commands') AS USER = 'schema-owner' to execute your SQL commands in the context of that 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'
like image 164
RBarryYoung Avatar answered Nov 10 '22 02:11

RBarryYoung


Other than modifying @QueryText itself, the only thing I can think of is a user's default schema:

ALTER USER SO_Sept09_Reader WITH DEFAULT_SCHEMA = SO_Sept09

...and then connect as a different user for each schema you want to use. Hackity hack.

But if your query is dynamically constructed anyway (and I'm sure you know why that's often not a great idea), it might be easiest to just add a schema placeholder to the query text, and pass the schema name along with the query to a replacement function.

like image 44
Michael Petrotta Avatar answered Nov 10 '22 02:11

Michael Petrotta


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

Note this doesn't 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

Won't work, because the schema affinity is lost inside the EXEC.

While this:

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 fine.

Similarly:

EXEC ( 'EXEC schema_a.SP' )

would obviously work fine.

like image 29
Cade Roux Avatar answered Nov 10 '22 03:11

Cade Roux