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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2003-12-08 : 08:33:03
|
| Neil writes "Here's my question.create function myfunction (@xyz Varchar(1024), @abc varchar(50))returns @MyTable (RowCol int IDENTITY(1,1) PRIMARY KEY, colA varchar(14) )Declare @tsql varchar(2000)select @tsql = 'create view vw_searchsfr as select columnA (a column) from ATABLE (a table) where columnX >= @xyz and columnY = @abc exec (@tsql)insert into @MyTableselect columnAfrom vw_searchsfrThe problem is I have an error message that says "Invalid use of 'EXECUTE' within a function". Is there anyway around this? Your help is greately appreciated." |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-12-08 : 08:36:36
|
| User-defined functions cannot make permanent changes to a database, nor can they execute dynamic SQL. Creating a view would count as a permanent change. And even if your function did work, it would fail the second time you ran it since it would try to create a view that already existed.In any case, you don't need a view:create function myfunction (@xyz Varchar(1024), @abc varchar(50))returns @MyTable (RowCol int IDENTITY(1,1) PRIMARY KEY, colA varchar(14) )Declare @tsql varchar(2000)insert into @MyTableselect acolumn from ATABLE where columnX >= @xyz and columnY = @abc |
 |
|
|
|
|
|
Complex Query