Dynamic SQL within a function

Hello,
I need to create a view on a database based on tables from other databases.
I'm creating a function to lookup the data in the other dbs and use the
function on the view. This is the function.
CREATE FUNCTION CTI_InventoryView ()
RETURNS @.CTI_Inventory TABLE
(INTERID VARCHAR(5), CMPNYNAM VARCHAR(65),
ITEMNMBR VARCHAR(31), ITEMDESC VARCHAR(101),
LOCNCODE VARCHAR(11), RCRDTYPE SMALLINT,
PRIMVNDR VARCHAR(15), LSORDQTY NUMERIC(19,5),
LRCPTQTY NUMERIC(19,5), LSTORDDT DATETIME,
LSORDVND VARCHAR(15), LSRCPTDT DATETIME,
QTYRQSTN NUMERIC(19,5), QTYONORD NUMERIC(19,5),
QTYBKORD NUMERIC(19,5), QTY_Drop_Shipped NUMERIC(19,5),
QTYINUSE NUMERIC(19,5), QTYINSVC NUMERIC(19,5),
QTYRTRND NUMERIC(19,5), QTYDMGED NUMERIC(19,5),
QTYONHND NUMERIC(19,5), ATYALLOC NUMERIC(19,5),
QTYAVAIL NUMERIC(19,5), QTYCOMTD NUMERIC(19,5),
QTYSOLD NUMERIC(19,5))
AS
BEGIN
DECLARE
@.INTERID VARCHAR(5),
@.COMPANYNAME VARCHAR(65),
@.SQL NVARCHAR(1000)
DECLARE CTI_Companies CURSOR FOR
SELECT INTERID, CMPNYNAM
FROM DYNAMICS..SY01500
OPEN CTI_Companies
FETCH NEXT FROM CTI_Companies
INTO @.INTERID, @.COMPANYNAME
WHILE @.@.FETCH_STATUS = 0
BEGIN
SELECT @.SQL = 'USE ' + @.INTERID + 'SELECT ' + ''''+ @.InterID + '''' + ', '
+ '''' + @.CompanyName + ''''
SELECT @.SQL = @.SQL + 'IV00102.ITEMNMBR, ITEMDESC, IV00102.LOCNCODE,
RCRDTYPE, PRIMVNDR, '
SELECT @.SQL = @.SQL + 'LSORDQTY, LRCPTQTY, LSTORDDT, LSORDVND, LSRCPTDT,
QTYRQSTN, QTYONORD, QTYBKORD, '
SELECT @.SQL = @.SQL + 'QTY_Drop_Shipped, QTYINUSE, QTYINSVC, QTYRTRND,
QTYDMGED, QTYONHND, ATYALLOC, '
SELECT @.SQL = @.SQL + 'QTYONHND - ATYALLOC, QTYCOMTD, QTYSOLD '
SELECT @.SQL = @.SQL + 'FROM IV00102 JOIN IV00101 ON IV00102.ITEMNMBR =
IV00101.ITEMNMBR '
INSERT INTO @.CTI_Inventory
EXECUTE sp_executesql @.SQL
FETCH NEXT FROM CTI_Companies
INTO @.INTERID, @.COMPANYNAME
END
CLOSE CTI_Companies
DEALLOCATE CTI_Companies
RETURN
END
GO
I found that I cannot call the sp_executesql stored procedure inside the
function. I receive this error
Msg 443, Level 16, State 14, Procedure CTI_InventoryView, Line 40
Invalid use of side-effecting or time-dependent operator in 'INSERT EXEC'
within a function.
I'm running out of ideas to do this, any ideas how to do this?,
Thanks,
Sandra.Sandra Parra (sparra@.citrinetech.com) writes:
> I need to create a view on a database based on tables from other
> databases.
> I'm creating a function to lookup the data in the other dbs and use the
> function on the view. This is the function.
>...
> I found that I cannot call the sp_executesql stored procedure inside the
> function. I receive this error
> Msg 443, Level 16, State 14, Procedure CTI_InventoryView, Line 40
> Invalid use of side-effecting or time-dependent operator in 'INSERT EXEC'
> within a function.
> I'm running out of ideas to do this, any ideas how to do this?,
Why are there so many databases, and why do need a view over all them?
I ask this question to possibly be able to give a better response.
From what I see here, I can think of two ways:
1) The number of databases is so dynamic, that you need a iterate over
all databases for every query. In such case, write a stored procedure
and get data into a temp table.
2) The number of databases is static enough, so you can set up a
job that defines a view every night, so that run can run queries
on this view during the day.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx