Sql-server – Error on simple Select statement: Subquery returned more than 1 value

selectsql-server-2008-r2

When I query a table 

select *
from tableName

it returns error msg

Msg 512, Level 16, State 1, Line 2
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, = ,>, >= or when the subquery is used as an expression. The statement has been terminated.

How is this possible I can get this error on simple selection of a table?

Table Definition:

CREATE TABLE [dbo].[TableName](
    [Column1] [int] NOT NULL,
    [Column2] [int] NOT NULL,
    [Column3] [int] NOT NULL,
    [Column4] [bit] NULL,
    [Column5] [numeric](15, 2) NULL,
    [Column6] [numeric](15, 2) NULL,
    [Column7] [datetime] NULL,
    [Column8] [datetime] NULL,
    [Column9] [varchar](5) NULL,
    [Column10] [varchar](5) NULL,
    [Column11] [varchar](15) NULL,
    [Column12] [datetime] NULL,
    [Column13] [varchar](5) NULL,
    [Column14] [varchar](17) NULL,
    [Column15] [varchar](256) NULL,
    [Column16] [numeric](18, 2) NULL,
    [Column17] [tinyint] NULL,
    [Column18]  AS ([dbo].[udf_ChargePointer]([Column1],[Column17])),
 CONSTRAINT [PK_TableName] PRIMARY KEY CLUSTERED 
(
    [Column1] ASC,
    [Column2] ASC,
    [Column3] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].TableName  WITH CHECK ADD  CONSTRAINT [CK_TableName] 
CHECK  ((case when len([Column11])>(2) AND isnumeric(replace([Column11],',','A'))=(1) then (1) else (0) end=(0)))
GO

ALTER TABLE [dbo].TableName ADD  DEFAULT ('01/01/1900') FOR [Column12]
GO

ALTER TABLE [dbo].TableName ADD  DEFAULT ('') FOR [Column13]
GO

Best Answer

That particular error happens when you are using a subquery in such a way that only one row can be used but more than one row is returned. For example using a subquery to return a column value.

Since this error only occurs when you have subqueries it is very likely that your tableName is actually a view. You can easily check this by running the following command.

SELECT OBJECTPROPERTYEX(OBJECT_ID('dbname.schema.tableName'),'BaseType')

If it returns a V then it's a view. You can then get the text of the view by using the following:

EXEC sp_helptext TableName
Related Question