Last post Jun 30, 2011 11:45 PM by Ashutosh Pathak
Jun 10, 2011 07:59 PM|chingfong2001|LINK
I am trying to do a dynamic sql passing some variables into a function which will return a table and I get an error:
Only functions and extended stored procedures can be executed from within a function.
The function is a multi statement table valued.
If you could point me to the right direction, I will really appreciated.
@ServiceDateField as nvarchar(40) which is passed in as a parameter. It contains the fieldname in a table.
set @dteToday = CAST(FLOOR(CAST(getdate() AS float)) AS smallDATETIME)
SELECT @sql =
N'Insert into @tempTable ' +
N' Select Lastname, FirstName, ' + convert(nvarchar(40), @ServiceDateField) + ' from table1 ' +
N' Where ' + convert(nvarchar(40), @ServiceDateField) + ' < @DteToday'
SELECT @params = N'@DteToday smalldatetime '
EXEC sp_executesql @sql, @params, @DteToday
Jun 11, 2011 01:08 AM|shashankgwl|LINK
this is crystal clear error message , no execs are allowed inside a stored function.
Jun 13, 2011 04:41 AM|Ashutosh Pathak|LINK
you can not have a EXEC command inside a sql function, neither you can use dynamic quaries
Jun 13, 2011 12:55 PM|chingfong2001|LINK
Hi Mr Ashutosh,
This is a good article about dynamic queries and I am able to make passing parameter in a scaler function working.
I have issues on the table valued functions with passing parameters.
Tks n rgds/ching
Jun 13, 2011 12:56 PM|chingfong2001|LINK
the article: http://www.sommarskog.se/dynamic_sql.html
Jun 13, 2011 04:06 PM|chingfong2001|LINK
I use a stored procedure to do the same and it works fine.
Jun 15, 2011 06:20 AM|Ashutosh Pathak|LINK
my apologies if i made a wrong statement ching, but can you please tell how you've made a dynamic query run inside a sql function, because as far as i know, we cannnot use the EXEC inside sql function, can you please post the code that is working you said,
so that i can get the understanding. Thanks.
Jun 15, 2011 02:18 PM|chingfong2001|LINK
This is a helpful link on how to pass in the parameters in the functions or sp.
I have two sp. sp 1 and sp2. sp1 will call sp2 and passing in parameters.
Inside sp2, I am useing the code similar to the codes in
SELECT @sql =
N' SELECT @cnt = COUNT(*) FROM dbo.' + quotename(@tbl) +
N' WHERE LastUpdated BETWEEN @fromdate AND ' +
N' coalesce(@todate, ''99991231'')'
SELECT @params = N'@fromdate datetime, ' +
N'@todate datetime = NULL, ' +
N'@cnt int OUTPUT'
EXEC sp_executesql @sql, @params, '20060101', @cnt = @count OUTPUT
to handle the parameters.
on sp1, I use exec sp2(p1, p2, p3.....)
The same code above and I just put it in a store procedure instead of a table valued function.
Hope this will help.
Jun 15, 2011 11:22 PM|Ashutosh Pathak|LINK
Ching! i wanted to know actually how to use dynamic queries in SQL Functions not in Stored Procedures. thanks :)
Jun 30, 2011 06:53 PM|chingfong2001|LINK
I am unable to get it working on functions as function has limitation but the same works on sp. rgds/ching
Jun 30, 2011 11:45 PM|Ashutosh Pathak|LINK
this is what i was posting from my last few posts, but as you were keep on saying, i thought there may be a way, okay doesnt matter. thanks by the way :)