CODEWITHSUNDEEP
sql-serveruser-defined-functionscommon-table-expression
Zack P
Zack P

Reputation: 41

SQL Server Function returning data in wrong column

I came across a User Defined Function (dbo.AnnualMetrics_ByCat(@StartYear, @EndYear)) that takes in year range params and contains about 5 CTEs chained together with a query that selects data from them. The function was coded to return a table like this:

MonthYear | CATID | Classification | GeneralClass | TotalUsedVolume | TotalUnusedVolume
--------- | ----- | -------------- | ------------ | --------------- | -----------------
2/1/2010  |  AGR  |  Agricultural  |  NON-RES     |  429.615        | 138.543

When the function is called... 

SELECT * FROM dbo.AnnualMetrics_ByCat(2006,2015) OPTION(MAXRECURSION 200)

...it returns a table like this (where "GeneralClass" column is missing, column values are shifted to the right, etc.): 

MonthYear | CATID | Classification | TotalUsedVolume | TotalUnusedVolume
--------- | ----- | -------------- | --------------- | -----------------
2/1/2010  |  AGR  |  Agricultural  |  NON-RES        |  429.615

When the query syntax is cut/paste into an SSMS window and run manually, the correct table structure and values are returned.

My question is, does anyone know what may have caused this? Dropping the function and recreating it using the original CREATE script resolved the issue, however I am still curious about the origin of the problem.

Upvotes: 3

Views: 1465

Answers (1)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67291

Try this:

USE master;
GO
CREATE DATABASE TestDB;
GO
USE TestDB;
GO
CREATE TABLE t(x INT, y INT,z INT);
INSERT INTO t VALUES(1,2,3),(4,5,6);
GO
CREATE VIEW dbo.vw AS SELECT * FROM t;
GO
SELECT * FROM dbo.vw; --everything fine here
GO
ALTER TABLE t ADD NewColumn VARCHAR(100) NOT NULL DEFAULT('blah');
GO
SELECT * FROM dbo.vw; --the new column doesn't show up???
GO
ALTER TABLE t DROP COLUMN y;
GO
SELECT * FROM dbo.vw; --huh?!?!
GO
USE master;
DROP DATABASE TestDb;

The final result

x   y   z
1   3   blah
4   6   blah

Why are z's values in y and z, which was INT, is suddenly "blah"?

The problem is the SELECT *. Without a specific column list your view picks the existing column one after the other without looking...

You must refresh/recreate the view.

UPDATE

I was not clear in this point The problem is the SELECT *!

What I really meant: That is the problem, use column names instead of the *!

(Thx Ben J. Boyle)

Upvotes: 1

Related Questions