Dynamic SQL to query an Adventureworks table

Question

I am trying to see a list of tables from Adventureworks DB from "Person" schema in Sql Server 2008. I developed teh following SP, but after running it as follows it gives me error "Incorrect syntax near ')'". Do you know how I can revise this SP or exec statement?

CREATE PROCEDURE [getTableNames]
@SchemaName VARCHAR(50)
AS

BEGIN
SET NOCOUNT ON;
SET @SchemaName = 'PERSON'
DECLARE @cmd AS VARCHAR(max)
SET @SchemaName = RTRIM(@SchemaName)
SET @cmd = N'SELECT TABLE_NAME ' + 
'FROM information_schema.Tables ' +
'WHERE TABLE_TYPE = ''BASE TABLE'' AND TABLE_SCHEMA = @SchemaName'
END

exec sp_executesql getTableNames, N'@SchemaName NVARCHAR(50), @SchemaName'

Answer 1

You don't actually need to use dynamic SQL here, plus your sproc isn't quite right as you're not executing the @cmd statement. Just use:

CREATE PROCEDURE [getTableNames]
@SchemaName VARCHAR(50)
AS
BEGIN
    SET NOCOUNT ON
    SELECT TABLE_NAME 
    FROM information_schema.Tables
    WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_SCHEMA = @SchemaName
END

EXECUTE getTableNames 'PERSON'

Answer 2

You don't need dynamic SQL:

select * from sys.tables
where type_desc = 'BASE TABLE' and schema_id = schema_id(@SchemaName)