December 8, 2009 at 2:48 am
Hi,
Am trying to build a function which gets a parameter and returns a integer.Though i may not have kept the correct example over here, but i want to buld the dynnamic query inside my user defined function and this dynamic query will be returning an integer which has to be
eventually return back.
Something like ,
SELECT C1,fn_sample(C1) FROM <tname>
You may tell me that,
select 'select count(*) from '+name from sysobjects where type = 'U' and name in ('T1','2','t3','sample')
I put up a sample example over to demonstarte that but it is throwing me the below ERROR msgs.
Can anyone tell me a workaround for this?
Also, would like to know the reason behind the ERROR msg
"Invalid use of side-effecting or time-dependent operator in 'DROP OBJECT' within a function."
Any suggestion would be greatly appreciated.
Thanks in Advance!
create function fn_sample(@tname varchar(200))
returns int
as
begin
declare @cnt int
declare @sql varchar(200)
if(exists(select name from tempdb..sysobjects where name like '#temp_tbl%'))
drop table #temp_tbl
create table #temp_tbl
(cnt int)
set @sql='
declare @i int
select @i = count(*) from '+@tname+'
select @i '
print @sql
insert into #temp_tbl(cnt) exec (@sql)
select @cnt= cnt from #temp_tbl
select @cnt
return(@cnt)
end
--!!!! ERROR !!!
/*
Msg 2772, Level 16, State 1, Procedure fn_sample, Line 10
Cannot access temporary tables from within a function.
Msg 2772, Level 16, State 1, Procedure fn_sample, Line 11
Cannot access temporary tables from within a function.
Msg 2772, Level 16, State 1, Procedure fn_sample, Line 17
Cannot access temporary tables from within a function.
Msg 2772, Level 16, State 1, Procedure fn_sample, Line 19
Cannot access temporary tables from within a function.
*/
So,instead of temp table i have used ordinal table
create function fn_sample(@tname varchar(200))
returns int
as
begin
declare @cnt int
declare @sql varchar(200)
if(exists(select name from sysobjects where name like 'temp_tbl%'))
drop table temp_tbl
create table temp_tbl
(cnt int)
set @sql='
declare @i int
select @i = count(*) from '+@tname+'
select @i '
insert into temp_tbl(cnt) exec (@sql)
select @cnt= cnt from temp_tbl
return(@cnt)
end
/*
Msg 443, Level 16, State 14, Procedure fn_sample, Line 9
Invalid use of side-effecting or time-dependent operator in 'DROP OBJECT' within a function.
Msg 443, Level 16, State 15, Procedure fn_sample, Line 10
Invalid use of side-effecting or time-dependent operator in 'CREATE TABLE' within a function.
Msg 443, Level 16, State 14, Procedure fn_sample, Line 17
Invalid use of side-effecting or time-dependent operator in 'INSERT EXEC' within a function.
*/
December 8, 2009 at 3:01 am
Functions may not have side effects. They may not change the state of the database at all. That means no creating or dropping objects. No inserts, updates or deletes. You're not allowed to access temp tables, though you can declare and use table variables.
EXEC (SQL String) is not allowed because, at creation time there's no way to tell whether or not the contents of that will cause changes. Hence it is simply forbidden. There is no way to use dynamic SQL within a function.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 8, 2009 at 3:23 am
Thanks Gila! That was really good.
Any work aroud for the above requirement, if i have to do so?
December 8, 2009 at 3:30 am
By workaround do you mean any way to cause side effects from within a finction? If so, no. It is a hard-and-fast requirement. You cannot make any changes to the DB from within a function. That also means absolutely no dynamic SQL.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 8, 2009 at 6:07 am
Okay.
If i want to implement the same logic, can we do it in any other alternate way.
December 8, 2009 at 6:07 am
Okay.
If i want to implement the same logic, can we do it in any other alternate way?
December 8, 2009 at 6:52 am
Have you tried using a stored procedure?
Failing to plan is Planning to fail
December 8, 2009 at 8:44 am
mahesh.vsp (12/8/2009)
Okay.If i want to implement the same logic, can we do it in any other alternate way.
Either:
1) Use a stored procedure instead, Or...
2) Create a View that UNIONs together every table=@Tname that could be passed and then select from it.
I'd recommend (1).
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 11, 2009 at 11:00 pm
Hi Barry,
I did'nt understand the second method you were mentioning i.e. using a View.
Can you give me an example of how it is going to be implemented????
Thank you.
December 12, 2009 at 3:04 am
mahesh.vsp (12/11/2009)
Hi Barry,I did'nt understand the second method you were mentioning i.e. using a View.
Can you give me an example of how it is going to be implemented????
Thank you.
Like this:
CREATE VIEW AllTables AS
SELECT 'Table1' AS TableName, YourIntCol1 AS IntCol, YourMatchCol1 AS MatchCol FROM Table1
UNION ALL SELECT 'Table2', YourIntCol2, YourMatchCol2 FROM Table2
UNION ALL SELECT 'Table3', YourIntCol3, YourMatchCol3 FROM Table3
UNION ALL ...
-- NOTE: Limit of 255 tables
GO
CREATE FUNCTION dbo.FindTableAndRow(@TblNam as Varchar(55), @MatchVal AS VARCHAR(32))
RETURNS INT AS
BEGIN
DECLARE @ret INT
SELECT @ret = IntCol
FROM AllTables
WHERE TableName = @TblNam
AND MatchCol = @MatchVal
RETURN @ret
END
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 12, 2009 at 3:14 am
Good performance not included. 😀
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 12, 2009 at 9:44 am
GilaMonster (12/12/2009)
Good performance not included. 😀
Heh, definitely not.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 12, 2009 at 11:28 am
Thanks Barry.
New thing i came to know. We can try with stored procedure with OUTPUT parameters. Right?
December 12, 2009 at 2:34 pm
Yes.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply