Is there anyway to create a view within a Function

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Is there anyway to create a view within a Function

Author

Topic

taowang
Starting Member

6 Posts

Posted - 2007-12-05 : 00:43:28

Hi, Is there anyway to create a view within a Function? The code is as below. I execute the code between "BEGIN" and "END". SQL Analyzer report error that said

'CREATE VIEW' must be the first statement in a query batch.

I could make the variable constant in SELECT statement, but I'm wondering if there is a way to make CREATE VIEW as part of code piece.

CREATE Function GetCommonFailurs()
AS
BEGIN
IF OBJECT_ID(N'CommonFailures') IS NOT NULL
DROP VIEW CommonFailures
DECLARE @Run1Result as char(4), @Run2Result as char(4);
SET @Run1Result='Fail';
SET @Run2Result='Fail';
CREATE VIEW CommonFailures
AS
SELECT Run1Failures.RunID as Run1ID,
Run2Failures.RunID as Run2ID,
@Run1Result as 'Run1Result',
@Run2Result as 'Run2Result',
Run1Failures.SmartyDOTXMLFilePath as Run1SmartyFilePath,
Run2Failures.SmartyDOTXMLFilePath as Run2SmartyFilePath,
Run1Failures.SDET as SDET,
Run1Failures.CommandLine as CommandLine,
Run1Failures.OutputFilePath as OutputFilePath
FROM Run1Failures
INNER JOIN Run2Failures
ON Run1Failures.TestID = Run2Failures.TestID
END

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2007-12-05 : 00:59:25

You can not create the Function, with view embedded in it. This is not possible in SQL SERVER.

Chirag

http://www.chirikworld.com

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2007-12-05 : 00:59:52

Why would you want to?!?

taowang
Starting Member

6 Posts

Posted - 2007-12-05 : 01:25:34

As my code shows, my CREATE VIEW logic (SELECT clause) could have some variable. Is there anyway to pass in dynamic information into VIEW creation code?

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-12-05 : 01:52:31

your code is weird. you shouldn't be creating permanent database objects at runtime.

perhaps what you want is a parameterized view? the way you do that in sql server is with a table-valued function. have a look at table-valued functions in BOL.

elsasoft.org

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources