Dynamic SQL in a function - SQL Server Forums

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

Transact-SQL (2000)

Dynamic SQL in a function

Author

Topic

1fred
Posting Yak Master

158 Posts

Posted - 2005-02-18 : 14:10:37

I must use dynamic SQL in a user define function. Can it be done?

Here is what I would like to do:

Create FUNCTION dbo.fct_kickoff_invit_date (
	@intCampaignID INT 
	,@intNbPart int
) RETURNS datetime
AS
BEGIN
	DECLARE @dteInvit datetime
	declare @sql varchar(1000)
	set @sql='
	select 
		max(d.datecreation) 
	from (
		select 
			top @intNbPart datecreation
		from 
			efo_participant p
		where 
			campaign_id = @intCampaignid
			and creation_channel_id = 7
		) d
	having 
		count(datecreation) >=@intNbPart
	'
	
	exec  @dteInvit =(@sql)
	RETURN @dteInvit
END

I need to do dynamic SQL for using the top X good rows. Else I'll have to do a Function for each possibilities...

Thank you.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-02-18 : 14:27:55

no, you cannot.

trust me I have tried.

I don't think you *need* dynamic SQL for this. And why are you using 'top' without an 'Order by ....'? that is not good practice. Show a sample of what you are trying to do, and we might be able to offer a different solution.

Corey

"If the only tool you have is a hammer, the whole world looks like a nail." - Mark Twain

1fred
Posting Yak Master

158 Posts

Posted - 2005-02-18 : 14:53:51

You are right, I forgot the Order by.

What I'm trying to do is to include in a select statement a field that will give me the date when the 10th email was sent to that person.

I have a campaign table joining a participant table. Every entry in the participant table is an email sent by the campaign manager.

So for every campaign I want to retrieve the date when was sent the X email. I need this to be dynamic because it is going to be in a report that is going to be use by the sales team.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-02-18 : 15:03:52

The part in blue is the important part. The rest is set-up and testing


Declare @sentEmails table (sentDate datetime, userId int)

Insert Into @sentEmails Select getdate(), 1 waitfor delay '00:00:00.050'
Insert Into @sentEmails Select getdate(), 1 waitfor delay '00:00:00.050'
Insert Into @sentEmails Select getdate(), 1 waitfor delay '00:00:00.050'
Insert Into @sentEmails Select getdate(), 1 waitfor delay '00:00:00.050'
Insert Into @sentEmails Select getdate(), 1 waitfor delay '00:00:00.050'
Insert Into @sentEmails Select getdate(), 1 waitfor delay '00:00:00.050'
Insert Into @sentEmails Select getdate(), 1 waitfor delay '00:00:00.050'
Insert Into @sentEmails Select getdate(), 1 waitfor delay '00:00:00.050'
Insert Into @sentEmails Select getdate(), 1 waitfor delay '00:00:00.050'
Insert Into @sentEmails Select getdate(), 1 waitfor delay '00:00:00.050'
Insert Into @sentEmails Select getdate(), 1 waitfor delay '00:00:00.050'
Insert Into @sentEmails Select getdate(), 1 waitfor delay '00:00:00.050'
Insert Into @sentEmails Select getdate(), 1 waitfor delay '00:00:00.050'
Insert Into @sentEmails Select getdate(), 1 waitfor delay '00:00:00.050'
Insert Into @sentEmails Select getdate(), 1 waitfor delay '00:00:00.050'


Select max(sentDate) From (Select top 10 * From @sentEmails Where UserId = 1 Order By sentDate) A


Declare @sentCnt int
Set @sentCnt = 10

Select 
	sentDate
From @sentEmails A
Where @sentCnt = (Select count(*) From @sentEmails Where sentDate <= A.sentDate)

Corey

"If the only tool you have is a hammer, the whole world looks like a nail." - Mark Twain

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-02-19 : 20:03:40

Your dynamic SQL Statement would not work in any case, inside or outside a function. The local variables that are referenced in the dynamic SQL would not be available, because they are not declared inside the execute.

Also,

exec  @dteInvit =(@sql)

would not populate @dteInvit, since there is no return statement inside the execute.

quote:
Originally posted by 1fred

I must use dynamic SQL in a user define function. Can it be done?

Here is what I would like to do:

Create FUNCTION dbo.fct_kickoff_invit_date (
@intCampaignID INT
,@intNbPart int
) RETURNS datetime
AS
BEGIN
DECLARE @dteInvit datetime
declare @sql varchar(1000)
set @sql='
select
max(d.datecreation)
from (
select
top @intNbPart datecreation
from
efo_participant p
where
campaign_id = @intCampaignid
and creation_channel_id = 7
) d
having
count(datecreation) >=@intNbPart
'

exec @dteInvit =(@sql)
RETURN @dteInvit
END

I need to do dynamic SQL for using the top X good rows. Else I'll have to do a Function for each possibilities...

Thank you.

Codo Ergo Sum

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources