asked on

SQL error Invalid column name

I have the following stored procedure

CREATE PROCEDURE [GEN].[ColumnsList]
      -- Add the parameters for the stored procedure here
(
@DatabaseName nvarchar(255),
@TableName nvarchar(255)
)
AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;

-- Insert statements for procedure here

exec ('Select COLUMN_NAME, DATA_TYPE from Ardentia_Healthware_5_Release.INFORMATION_SCHEMA.COLUMNS where TABLE_CATALOG ='+ @TableName)
END

Which when I execute using the following

EXEC @return_value = [GEN].[ColumnsList]
@DatabaseName = N'SSO',
@TableName = N'SSO_Config'

I get the following error

Msg 207, Level 16, State 1, Line 1
Invalid column name 'SSO_Config'.
(1 row(s) affected)

I am not sure how to overcome this error.

can anybody help?

I am using SQL 2005

SOLUTION

BrandonGalderisi

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Aneesh

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Aneesh

are u sure about this TABLE_CATALOG =@TableName
should n't it be TABLE_NAME = @TableName

ASKER CERTIFIED SOLUTION

BrandonGalderisi

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Chris Luttrell

The problem is that we are not sure of your desired usage of @DatabaseName, if it is truely the database then Brandon is right above. If you are thinking Database is a specific Schema within the Ardentia_Healthware_5_Release database then try the code below.

CREATE PROCEDURE [GEN].[ColumnsList]
      -- Add the parameters for the stored procedure here
(
@DatabaseName nvarchar(255),
@TableName nvarchar(255)
)
AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;
 
    -- Insert statements for procedure here
 
 
Select COLUMN_NAME, DATA_TYPE from Ardentia_Healthware_5_Release.INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA = @DatabaseName AND TABLE_CATALOG = @TableName
END

Open in new window

SOLUTION

Chris Luttrell

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Mr_Shaw

ASKER

thanks