0

In the following, I would expect the first SELECT of the new column [Bit01] to be successful however it is not. Is there any way of avoiding the use of the ALTER statement to get it to work?

I have not been anything to find anything that confirms or denies my findings on the relevant MSDN page: https://msdn.microsoft.com/en-us/library/ms181647(v=sql.110).aspx

-- Shell table
CREATE TABLE [tblRecords]
(
    Id INT NOT NULL PRIMARY KEY CLUSTERED
);
GO

-- Inline TVF
CREATE FUNCTION [ufnAllRecords]()
RETURNS TABLE
AS
RETURN
(
    SELECT *
    FROM [tblRecords]
);
GO

-- Add a column
ALTER TABLE [tblRecords]
ADD [Bit01] BIT NULL;
GO

-- Recompile the function
EXECUTE sp_recompile N'ufnAllRecords';
GO

-- Attempt to select the new column
SELECT [Bit01]
FROM [ufnAllRecords]();
GO

-- Column does not exist and so it errors...
-- However it works after ALTER'ing it
ALTER FUNCTION [ufnAllRecords]()
RETURNS TABLE
AS
RETURN
(
    SELECT *
    FROM [tblRecords]
);
GO

-- Now works
SELECT [Bit01]
FROM [ufnAllRecords]();
GO

-- Cleanup
DROP FUNCTION [ufnAllRecords]; 
GO
DROP TABLE [tblRecords];
GO

1 Answer 1

2

Using SELECT * in production code is evil, for a number of reasons.

If you absolutely must do this, the procedure you're looking for is sp_refreshsqlmodule, which works much like the built in sp_refreshview, rather than sp_recompile.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.