Facing error of "The default schema does not exist." when executing runtime query inside sp using exec()

Question

i have made a runtime query inside a sp and am exceuting the query within the sp using exec(), but when creating the sp i am getting the error

The default schema does not exist.

The SP is:

CREATE PROCEDURE MySP
    @tableName varchar(100)

AS
BEGIN   
    SET NOCOUNT ON;

declare @selectQuery varchar(MAX)

set @selectQuery = 'select * from ' + @tableName

exec(@selectQuery)

end

kindly help

Answer 1

Use CREATE PROCEDURE dbo.MySP

The user you are logged in as must have a non existent default schema.

DEFAULT_SCHEMA can be set to a schema that does not currently exist in the database.

Also you should use quotename(@tableName) and a parameter type of sysname rather than varchar(100) to avoid SQL injection or just errors from non standard object names.

Answer 2

You could change your default schema:

ALTER USER [YOURDOMAIN\HotTester] WITH DEFAULT_SCHEMA=[dbo]
GO

then avoiding having to include [dbo]. in the CREATE PROCEDURE

Answer 3

It is probably because the default schema associated with the User creating the SP no longer exists or the user no longer has access to the schema.

Although, I thought SQL Server defaulted to the dbo schema. Maybe try to qualify the schema for the Stored Proc.

e.g

Create Procedure dbo.MySP