Dynamic SQL in retrun table procedures

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

General SQL Server Forums

New to SQL Server Programming

Dynamic SQL in retrun table procedures

Author

Topic

awatts
Starting Member

2 Posts

Posted - 2007-05-22 : 08:14:28

I'm having problems with dynamic sql within a funtion that returns a table. Here's an example, it compiles but on running gives me an error allong the lines that calling sub procedures is not permitted. I'm assuming the problem is inserting into teh return table, can it be done? should I declare @MyTable as a parameter using the sp_executesql call? or have I completely missed something?

Regards,

Andrew

Example routine, not that far from what my actual code looks like.


CREATE FUNCTION GetMyRecords 
(
	@date DateTime
)
RETURNS 
 @MyTable TABLE 
(
	RECORDID BigInt
)
AS
BEGIN
	DECLARE @SQL Varchar(1000)
	DECLARE @dayOfWeek int
	DECLARE @fld varchar(10)
	SET @dayOfWeek = DatePart( dw, @date )

	if( @dayOfWeek = 1 )
		@fld = "SUNDAY"
	if( @dayOfWeek = 2 )
		@fld = "MONDAY"
	etc...
	
	Set @SQL = N'Insert into @MyTable Select MyTestTable.ID from MyTestTable Where MyTestTable.' + @fld + ' > 0 '
	Exec( @SQL )
	RETURN 
END
GO

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-22 : 08:20:58

I am surprised it even compiles. Using Dynamic sql inside functions is not permitted.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

awatts
Starting Member

2 Posts

Posted - 2007-05-22 : 08:32:24

That's probably why it doesn't work.

Thanks,

Andrew

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-22 : 08:37:06

as you have limited number of condition (7 only), just use IF ... THEN to do it.


IF @dayofWeek = 1
BEGIN
    Insert into @MyTable select ID from MyTestTable Where Sunday > 0
END
IF @dayofWeek = 2
BEGIN
   . . . .
END
. . .

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-05-22 : 10:38:12

Also make sure that the table is normalised
http://www.datamodel.org/NormalizationRules.html

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources