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.