May 8, 2008 at 2:55 am
Hi all, mister
I want create a function but I get this error: Error Invalid use of
side-effecting or time-dependent operator in 'EXECUTE STRING' within a
function
Using EXECUTE or EXEC , I get this error:
-- Error Invalid use of side-effecting or time-dependent operator in 'EXECUTE STRING' within a function
--EXECUTE(@sql)
If I use EXEC sp_executesql @sql, I get this error:
Only functions and extended stored procedures can be executed from within a function.
I think in a function, cannot use temp tables, or calling exec or store
procedures.
Which is the best solution for my issue ? develop store procedure ??
thanks in advance, regards.
CREATE FUNCTION fnObtenerTablaMaestra ()
RETURNS @T Table ( Descripcion VARCHAR(20) NOT NULL, CIF VARCHAR(8) NULL )
AS
BEGIN
DECLARE @cmd nvarchar(max)
DECLARE @sql nvarchar(max)
DECLARE @nexoUNION NVARCHAR(max)
DECLARE @params nvarchar(max)
DECLARE @NombreTabla VARCHAR(MAX)
DECLARE @Descripcion VARCHAR(MAX)
DECLARE @CIF VARCHAR(MAX)
-- Cannot access temporary tables from within a function.
-- IF EXISTS (SELECT TABLE_NAME FROM tempdb.INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE '#tmpTable%')
-- DROP TABLE #tmpTable
--CREATE TABLE #tmpTable ( Descripcion VARCHAR(20) NOT NULL, CIF VARCHAR(8) NULL)
SET @nexoUNION = NULL
DECLARE c1 CURSOR for
SELECT [CD_NOMBRE_TABLA], [DS_CAMPO_DESCRIPCION], [DS_CAMPO_CIF] FROM [TABLA_MAESTRA]
OPEN c1
FETCH c1 INTO @NombreTabla, @Descripcion, @CIF
--FETCH NEXT FROM c1 INTO @NombreTabla, @Descripcion, @CIF
WHILE @@FETCH_STATUS >= 0
--WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @sql =
'INSERT INTO #tmpTable
N'''+ @NombreTabla + '''
N'''+ @Descripcion + '''
N'''+ @CIF + ''''
SELECT @sql =
'SELECT ' + @Descripcion + ', '+ @CIF + ' FROM ' + @NombreTabla
IF @nexoUNION IS NULL
BEGIN
SET @nexoUNION = 'UNION'
END
ELSE
SET @sql = @nexoUNION + ' ' + @sql
--EXECUTE (@sql)
--Exec(@sql)
FETCH c1 INTO @NombreTabla, @Descripcion, @CIF
--FETCH NEXT FROM c1 INTO @NombreTabla, @Descripcion, @CIF
END
CLOSE c1
DEALLOCATE c1
--SET @sql = 'SELECT Descripcion, CIF FROM #tmpTable'
-- Error Invalid use of side-effecting or time-dependent operator in 'EXECUTE STRING' within a function
--EXECUTE(@sql)
EXEC sp_executesql @sql
RETURN
END
GO
SELECT * FROM [dbo].fnObtenerTablaMaestra ()
May 8, 2008 at 3:25 am
You can't execute a store procedure inside the function!
- Zahran -
May 8, 2008 at 3:28 am
And not EXEC o EXECUTE.
Better develop store procedure, isn't ?
May 8, 2008 at 5:44 pm
What are you trying to do?
Concatenate some values to a string?
Then search this forum and/or script section of this site for concatenate function(s), chose the one you like the most.
And never use construction like this:
IF EXISTS (SELECT TABLE_NAME FROM tempdb.INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE '#tmpTable%')
DROP TABLE #tmpTable
CREATE TABLE #tmpTable ( Descripcion VARCHAR(20) NOT NULL, CIF VARCHAR(8) NULL )
Open a new window in QA and run:
CREATE TABLE #tmpTable ( Descripcion VARCHAR(20) NOT NULL, CIF VARCHAR(8) NULL )
Then don't close it, open another one and run this:
IF EXISTS (SELECT TABLE_NAME FROM tempdb.INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE '#tmpTable%')
DROP TABLE #tmpTable
What's happened?
Did your sheck work?
Yeah, right. 😉
Use this instead:
IF Object_ID('tempdb..#tmpTable') IS NOT NULL
DROP TABLE #tmpTable
_____________
Code for TallyGenerator
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply