problem using sp_ExecuteSQL (sqlserver 2005)

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

Database Design and Application Architecture

problem using sp_ExecuteSQL (sqlserver 2005)

Author

Topic

sqlquest
Starting Member

2 Posts

Posted - 2009-05-22 : 14:52:56

The following code runs with error. But when I try to use the function, it seems that function is not created at all.

set nocount on
if not exists (select 'x' where cast(serverproperty('productversion')as varchar(20)) like '8%')
begin
DECLARE @SQLString nvarchar(1000);
DECLARE @Return NVARCHAR(MAX);
if exists (select 'X' from information_schema.routines
where routine_name = 'xmlassem' and
routine_type = 'function')
begin
SET @SQLString = N'drop function dbo.xmlassem';
exec sp_executesql @SQLString
end

SET @SQLString=N'CREATE FUNCTION dbo.xmlassem (@taskid INT)
RETURNS NVARCHAR(MAX) AS
BEGIN
SELECT '''+@Return+''' = coalesce(@Return)+'',''+xmldata
FROM xmldata_segments
WHERE eventmgrtaskid = @taskid
ORDER BY eventmgrtaskid, xmldataid
RETURN @Return
END'
exec sp_executesql @SQLString
end
go

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2009-05-22 : 19:33:53

You're trying to do something weird with the declaration of the @Return variable, so the @SQLString is NULL when you run the sp_executesql. You need to declare @Return in the function, so try this instead:

set nocount on
if not exists (select 'x' where cast(serverproperty('productversion')as varchar(20)) like '8%')
begin
DECLARE @SQLString nvarchar(1000);
if exists (select 'X' from information_schema.routines
where routine_name = 'xmlassem' and
routine_type = 'function')
begin
SET @SQLString = N'drop function dbo.xmlassem';
exec sp_executesql @SQLString
end


SET @SQLString=N'CREATE FUNCTION dbo.xmlassem (@taskid INT)
RETURNS NVARCHAR(MAX) AS
BEGIN
DECLARE @Return NVARCHAR(MAX);
SELECT @Return = coalesce(@Return + '','' + cast(xmldata AS nvarchar(max))
    , cast(xmldata AS nvarchar(max)))
FROM xmldata_segments
WHERE eventmgrtaskid = @taskid
ORDER BY eventmgrtaskid, xmldataid
RETURN @Return
END'
exec sp_executesql @SQLString
end
go

sqlquest
Starting Member

2 Posts

Posted - 2009-05-22 : 20:51:36

Thanks a lot! It works.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-23 : 14:44:34

can we know the need of creating function dynamically?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources