Facing error "Default scheme does not exist." when executing a runtime request inside sp using exec ()

Question

I executed a runtime request inside sp and excluded the request in sp using exec (), but when creating sp I get an error

The default schema does not exist. 

SP:

 CREATE PROCEDURE MySP @tableName varchar(100) AS BEGIN SET NOCOUNT ON; declare @selectQuery varchar(MAX) set @selectQuery = 'select * from ' + @tableName exec(@selectQuery) end 

kind help

Answer 1

Use CREATE PROCEDURE dbo.MySP

The user with whom you are logged in must have a non-existing default scheme.

DEFAULT_SCHEMA can be configured on a schema that does not currently exist in the database.

You should also use quotename(@tableName) and the sysname parameter type, not varchar(100) , to avoid SQL injection or just errors from non-standard object names.

Answer 2

You can change the default scheme:

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

and then avoid including [dbo]. IN THE CREATION PROCEDURE

Answer 3

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 that SQL Server did not match the dbo by default. Perhaps try qualifying the circuit for a saved Proc.

eg

Create Procedure dbo.MySP