December 5, 2005 at 5:48 am
I am working in SQL Server 2000 Environment, What I need to execute a sp_executesql statement in order to fetch the records in a temporary sting , Then I manupulate the string and get the desired results .It worked fine standalone but when I execute the same line of code in user Defined i get a error Only functions and extended stored procedures can be executed from within a function. I want to know is there any way to get this thing working , Due to performance issues I am unable to use cursors and Temporary tables
December 5, 2005 at 6:43 am
Can you provide what it is that you are trying to do with EXECUTESQL so we can try and help diagram a solution???
Have you looked at TABLE functions? This accept a parameter and can return recordsets thereby eliminating DYNAMIC SQL...
Good Hunting!
AJ Ahrens
webmaster@kritter.net
December 6, 2005 at 3:46 am
I am using this line of code in UDF , is there any alternative method to replace sp_executesql , instead of cursors and temporary Tables
SET
@sql = 'select @AuthorID1 = @AuthorID1 + cast(AuthorID as varchar)+ '','' from Authors where PublicID = ' + cast(@PublicID as varchar(50)) +' order by OrderNo'
EXEC
sp_executesql @sql, N' @AuthorID1 nvarchar(100) output', @AuthorID OUTPUT
/*After that I am Getting all the authors name in a String Like that
568778,5457754
*/
If @AuthorID is not null and ltrim(rtrim(@AuthorID)) <> ''
BEGIN
while charindex(',',@AuthorID) >0
BEGIN
/*Here I do some Processing on data*/
end
end
December 6, 2005 at 6:47 am
Hi Haris,
Why not just use another udf to return the value, this works on the pubs database to return a list of the author id
IF EXISTS (SELECT *
FROM sysobjects
WHERE name = N'ConcatenateList')
DROP FUNCTION ConcatenateList
GO
CREATE FUNCTION ConcatenateList
(@state varchar(50) )
RETURNS varchar(4000)
AS
BEGIN
declare @x varchar(4000)
set @x = ''
select @x = @x + au_id from authors where state = 'CA'
RETURN @x
-- eg.
-- DECLARE @sum AS int
-- SELECT @sum = @p1 + @P2
-- RETURN @sum
END
GO
-- =============================================
-- Example to execute function
-- =============================================
SELECT dbo.ConcatenateList
('CA')
GO
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply