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 17Invalid 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]GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO--//============================================================|--// 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_CreateTableGOCREATE PROCEDURE usp_CreateTable-- Argument @InName VARCHAR(64) = 'Test' -- Pass in a table nameAS-- Local variablesDECLARE @Cmd VARCHAR(2048)DECLARE @TabName VARCHAR(64)DECLARE @ByDName VARCHAR(64)SET @TabName = @InNameSET @ByDName = 'PK_By' + @InName + 'DName'-- Drop Table if it existsSET @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 tableSET @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]GOCREATE FUNCTION usp_Insert( @TabName VARCHAR(64), @InName VARCHAR(32))RETURNS INT -- UIDASBEGIN-- Local variablesDECLARE @cmd VARCHAR(512)DECLARE @uid INTSET @uid = 0-- Build the Insert CommandSET @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 failsSELECT @uid=@@IDENTITYRETURN(@uid)ENDGO |
|
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 |
|
|
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...? |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-01-25 : 19:50:30
|
Hi KenAtEMC. Welcome to SQLTeam.There are few error spotted1. sys.objects should be sysobjectsIF EXISTS (SELECT * FROM sysobjects 2. object_id. sysobjects does not have object_id column. It is idWHERE 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 |
|
|
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. |
|
|
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 |
|
|
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 xtypeBy 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. |
|
|
TimS
Posting Yak Master
198 Posts |
Posted - 2006-01-26 : 13:34:32
|
Then why does the below not workCREATE FUNCTION dbo.usp_test ()RETURNS INT --ASBEGINDECLARE @rslt INTexec @rslt = sp_refreshview 'dbo.track'return @rsltENDSELECT dbo.usp_test()Edit: You were right the below worksALTER FUNCTION dbo.usp_test ()RETURNS INT --ASBEGINDECLARE @rslt INTexec @rslt = sp_MSgetversionreturn @rsltENDSELECT dbo.usp_test()Tim S |
|
|
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 dynamicALTER FUNCTION [dbo].[getIPLocation]( -- Add the parameters for the function here @IP nvarchar(15))RETURNS intASBEGIN -- Declare the return variable hereDECLARE @Sec1 int, @Sec2 int, @Sec3 int, @CountryCode int, @CountryName nvarchar(max), @TableName nvarchar(10) -- FUNCTIONSET @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 ip4left join [HostIP].[dbo].[countries] cou on ip4.country=cou.idleft join [HostIP].[dbo].[citybycountry] cit on ip4.city=cit.cityWHERE b='+@Sec2+' AND c='+@Sec3+''); RETURN @CountryCodeENDThanks |
|
|
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 1980blog: http://weblogs.sqlteam.com/mladenp |
|
|
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 ip4left join [HostIP].[dbo].[countries] cou on ip4.country=cou.idleft join [HostIP].[dbo].[citybycountry] cit on ip4.city=cit.cityWHERE b='+@Sec2+' AND c='+@Sec3+''EXEC sp_executesql @DynamicSql |
|
|
evilDBA
Posting Yak Master
155 Posts |
Posted - 2007-12-07 : 13:36:54
|
the only workaround is in 2005you 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. :) |
|
|
|