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
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Using EXEC inside a function

Author  Topic 

KenAtEMC
Starting Member

2 Posts

Posted - 2006-01-25 : 15:53:54
I need some help!

I get an error when I try to install a stored procedure (function) and I don't understand what it means. (I'm a SQL newbie so be nice).

Msg 443, Level 16, State 14, Procedure usp_Insert, Line 17
Invalid use of side-effecting or time-dependent operator

Here is the code that creates a table (it's name is passed in as an argument) and the function.

I can make this work if I use a procedure but since all I want to do is insert a row and return the IDENTITY I though a function would work. I'm thinking that using a function in this way is wrong.

Anyway here is the code:

USE [labdata]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--//============================================================|
--// Name: usp_CreateTable |
--//============================================================|
IF EXISTS (SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].usp_CreateTable') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].usp_CreateTable
GO

CREATE PROCEDURE usp_CreateTable
-- Argument
@InName VARCHAR(64) = 'Test' -- Pass in a table name
AS
-- Local variables
DECLARE @Cmd VARCHAR(2048)
DECLARE @TabName VARCHAR(64)
DECLARE @ByDName VARCHAR(64)

SET @TabName = @InName
SET @ByDName = 'PK_By' + @InName + 'DName'

-- Drop Table if it exists
SET @Cmd = 'IF EXISTS (SELECT * FROM sys.objects ' +
'WHERE object_id = OBJECT_ID(N''[' + @TabName + ']'') ' +
'AND type in (N''U'')) ' +
'DROP TABLE [' + @TabName + ']'
EXEC (@Cmd)

-- Create the table
SET @Cmd = 'CREATE TABLE [' + @TabName + '] (' +
'[UID] [INT] IDENTITY(1,1) NOT NULL,' +
'[DName] [VARCHAR](32) NOT NULL, ' +
'CONSTRAINT [' + @ByDName + '] PRIMARY KEY CLUSTERED ' +
'(DName ASC) WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ' +
') ON [PRIMARY]'
EXEC (@Cmd)
GO

--//============================================================|
--// Name: usp_Insert |
--//============================================================|
IF EXISTS (SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[usp_Insert]')
AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[usp_Insert]
GO

CREATE FUNCTION usp_Insert(
@TabName VARCHAR(64),
@InName VARCHAR(32))
RETURNS INT -- UID
AS
BEGIN
-- Local variables
DECLARE @cmd VARCHAR(512)
DECLARE @uid INT
SET @uid = 0

-- Build the Insert Command
SET @cmd = 'INSERT INTO [' + @TabName + '] ' +
'([Sortable],[Subnet],[SubnetMask]) ' +
'VALUES (''' + @InName + ''')'

-- EXEC below causes error message:
--Msg 443, Level 16, State 14, Procedure usp_Insert, Line 17
--Invalid use of side-effecting or time-dependent operator in 'EXECUTE STRING' within a function.

EXEC (@cmd)

-- I need to get the @@IDENTITY value into @uid for the return. Any Ideas?
-- I tried this: but it fails
SELECT @uid=@@IDENTITY

RETURN(@uid)
END
GO


TimS
Posting Yak Master

198 Posts

Posted - 2006-01-25 : 16:11:11
You can only use EXEC in a function to call extended stored procedures.

Tim S
Go to Top of Page

cshah1
Constraint Violating Yak Guru

347 Posts

Posted - 2006-01-25 : 17:11:16
Does that mean you can create a dynamic SQL and use sp_executesql inside the UDF...?
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-25 : 19:50:30
Hi KenAtEMC. Welcome to SQLTeam.

There are few error spotted
1. sys.objects should be sysobjects
IF EXISTS (SELECT * FROM sysobjects


2. object_id. sysobjects does not have object_id column. It is id
WHERE id = OBJECT_ID(N'[dbo].usp_CreateTable') AND type in (N'P', N'PC'))


3. EXEC (@cmd)
You can't use exec() in a function

----------------------------------
'KH'

Happy Chinese New Year
Go to Top of Page

KenAtEMC
Starting Member

2 Posts

Posted - 2006-01-26 : 07:02:18
Thanks for the help. I suspected I was not doing it right. As far as the comments from khtan on

"IF EXISTS (SELECT * FROM sysobjects" and

"WHERE id = OBJECT_ID(N'[dbo].usp_CreateTable') AND type in (N'P', N'PC'))",

these were created using the SQL2005 Management Studio. I right clicked on the function name under the object explorer and selected 'Script function as' and then 'DROP TO' and used the code it generated.

Thanks again for the help.

Go to Top of Page

TimS
Posting Yak Master

198 Posts

Posted - 2006-01-26 : 08:55:16
quote:
Originally posted by cshah1

Does that mean you can create a dynamic SQL and use sp_executesql inside the UDF...?



NO, I think the extended stored procedure must start with xp not sp.

Tim S
Go to Top of Page

cshah1
Constraint Violating Yak Guru

347 Posts

Posted - 2006-01-26 : 11:50:40

Tim,
That is not true. I had the same notion as you do and It was a while after that I found out.

sp_executesql is an extended stored procedure. look into sysobjects table and look for xtype

By definition you are not allowed to change the state of the database within your UDF. so you are not allowed to construct and execute a dynamic sql using sp_executesql extended stored procedure.
Go to Top of Page

TimS
Posting Yak Master

198 Posts

Posted - 2006-01-26 : 13:34:32
Then why does the below not work

CREATE FUNCTION dbo.usp_test ()
RETURNS INT --
AS
BEGIN
DECLARE @rslt INT
exec @rslt = sp_refreshview 'dbo.track'
return @rslt
END

SELECT dbo.usp_test()

Edit: You were right the below works

ALTER FUNCTION dbo.usp_test ()
RETURNS INT --
AS
BEGIN
DECLARE @rslt INT
exec @rslt = sp_MSgetversion
return @rslt
END

SELECT dbo.usp_test()

Tim S
Go to Top of Page

joblenis
Starting Member

29 Posts

Posted - 2007-07-06 : 12:18:01
Sorry to bring back from the dead, I created a function that works great but wont in a stored proc because you cant use exec in a stored proc. Is there another way I could do this? The reason I have a EXEC statement because the tablename is dynamic

ALTER FUNCTION [dbo].[getIPLocation]
(
-- Add the parameters for the function here
@IP nvarchar(15)
)
RETURNS int
AS
BEGIN
-- Declare the return variable here
DECLARE @Sec1 int,
@Sec2 int,
@Sec3 int,
@CountryCode int,
@CountryName nvarchar(max),
@TableName nvarchar(10)

-- FUNCTION
SET @Sec1=PARSENAME(@IP, 4)
SET @Sec2=PARSENAME(@IP, 3)
SET @Sec3=PARSENAME(@IP, 2)
SET @TableName ='ip4_' + cast(@Sec1 as nvarchar)

EXEC('SELECT ip4.country as "Country Code",
code as "Abrv",
cou.name as "Country",
cit.state as "State",
cit.name as "City"
FROM ' + @TableName + ' ip4
--FROM ip4_61 ip4
left join [HostIP].[dbo].[countries] cou on ip4.country=cou.id
left join [HostIP].[dbo].[citybycountry] cit on ip4.city=cit.city
WHERE b='+@Sec2+' AND c='+@Sec3+'');


RETURN @CountryCode

END



Thanks
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-07-06 : 12:35:18
you can't use dynmaic sql in a function. period.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
Go to Top of Page

mauriciofl
Starting Member

1 Post

Posted - 2007-12-07 : 11:54:55
DECLARE @DynamicSql nvarchar(1000)
SET @DynamicSql = 'SELECT ip4.country as "Country Code",
code as "Abrv",
cou.name as "Country",
cit.state as "State",
cit.name as "City"
FROM ' + @TableName + ' ip4
--FROM ip4_61 ip4
left join [HostIP].[dbo].[countries] cou on ip4.country=cou.id
left join [HostIP].[dbo].[citybycountry] cit on ip4.city=cit.city
WHERE b='+@Sec2+' AND c='+@Sec3+''

EXEC sp_executesql @DynamicSql
Go to Top of Page

evilDBA
Posting Yak Master

155 Posts

Posted - 2007-12-07 : 13:36:54
the only workaround is in 2005
you can call a CLR function inside the UDF function, if you trick SQL server into thinking that that CLR has not side effects and is deterministic. But SQL server can not verify it, so, you can do all sorts of dirty things inside your .Net code. :)
Go to Top of Page
   

- Advertisement -