sql server 2000, executing dynamic sql within a function 2

[tempo1]

Hi everyone,
I have a procedure within whice i execute a function as follows:

SELECT
 'bbb',
 DBO.statusecond ('%aaa%', 
   '20070801') 
 COMMIT

and a function that runs dynamic sql

exec(@dynamic)

.
When it comes to running that dynamic sql i get the following error message:

Server: Msg 443, Level 16, State 2, Procedure statusecond, Line 31
Invalid use of 'EXECUTE' within a function.

. Why is that ? Must i not run dynamic sql from a function ?
What do i do instead ?
Thanks

 

[SQLDenis]

a function can not call dynamic sql
what are you trying to do? Maybe you can put it inside a proc

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[tempo1]

Hi Denis
I'd like to build a table, based on another table. My table's columns should display months (24 months for 2006-7). Ther are 4 rows for various states of a customer. I'd like for each value to insert:

 INSERT INTO myTable
(row-col) SELECT myFunction(status,date)

which is:

/*EXEC statusecond 'status1', '20070801'*/
ALTER FUNCTION statusecond (@stat_param VARCHAR(50), @date_param SMALLDATETIME)
RETURNS INTEGER
AS
BEGIN 
'
SELECT phone1
FROM
contact1
GROUP BY
phone1
HAVING
MAX(department) LIKE ' + 
''''+@stat_param+''''+
'
AND YEAR(MAX(ext3))='+CAST(@year_stat as varchar(20)) +
'
AND MONTH(MAX(ext3))='+CAST(@month_stat as varchar(20)) 

exec(@dynamic)
RETURN @@rowcount
END

Can you think of a better way ?
Thanks

 

[tempo1]

I made a mistake. This is "Statusecond" not "myFunction" sorry.

 

[SQLDenis]

why do you need dynamic SQL????


also create a date variable instead of horsing around with yaer and month, this will perform much faster since it is sargable

example

declare @date datetime
select @date = CAST(@year_stat as varchar(20))  + right('00' + CAST(@month_stat as varchar(20)),2) + '01'

SELECT phone1
FROM
contact1
GROUP BY
phone1
HAVING
MAX(department) LIKE @stat_param
AND ext3 >= @date
AND ext3 < dateadd(m,1,@date)

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[SQLSister]

Dennis is correct. You do understand why you should not use dynamic SQL if you don't need to? Why it is a bad coding practice to use use dynamic SQL casually? I think you need to read this link

http://www.sommarskog.se/dynamic_sql.html

"NOTHING is more important in a database than integrity." ESquared

 

[tempo1]

Hi,
This is before i read the link you sent SQLSister and before i tested the code you sent me Denis.
I dislike the use of Dynamic SQL in my code but i always do it when there is a parameter involove in my query. I gave a glimpse at your code Denis and maybe i can use it instead. In that case i'll be very grateful to you.