September 1, 2006 at 2:41 am
I need to pass a table name and id to a function and return a row count
I need to use EXEC or SP_EXECUTESQL to run dynamic SQL
It wont work in functions. Following is my function
alter FUNCTION [dbo].[GetRowCount] (@TblName NVARCHAR(25) , @Itemid INT)
RETURNS INT
AS BEGIN
DECLARE @RowCnt INT
set @RowCnt = 0
DECLARE @Sqlstring nvarchar(2000)
set @Sqlstring = 'SELECT @RowCnt = COUNT(*) FROM ['+ @TblName +'] WHERE Itemid = '+ convert(varchar(10),@Itemid)
EXEC @Sqlstring
RETURN @RowCnt
END
while executing this I get the following error ....
"Only functions and extended stored procedures can be executed from within a function." and "Incorrect syntax near the keyword 'EXEC' "
does anyone have any ideas of this ?
Thanks.
vidhya
September 1, 2006 at 4:28 am
Vidhya
Can you not use a stored procedure instead of a function?
John
September 1, 2006 at 5:10 am
Hi John,
My requirement is like that. I need that functionality inside Function. If possible pls tell me to proceed further.
Vidhya
September 1, 2006 at 6:40 am
You should read BOL to understand the limitations and uses of various objects in SQL Server. You might be able to do this through a dot net function although I'd advise against it.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
September 2, 2006 at 11:53 am
You cannot do it using functions in sql server.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply