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 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy