Hello all I have a function:
ALTER FUNCTION [dbo].[getContentURL]
(
@VID int,
@accountID int
)
RETURNS VARCHAR(500)
AS
BEGIN
DECLARE @cTable varchar(50) = '[CONTENT_CONNECT].[dbo].CONTENT_' + CONVERT(varchar(5),@accountID)
DECLARE @vTable varchar(50) = '[CONTENT_CONNECT].[dbo].VERSION_' + CONVERT(varchar(5),@accountID)
DECLARE @sql VARCHAR(max) = ''
DECLARE @RETURN VARCHAR(500)
DECLARE @ParmDefinition VARCHAR(500)
SET @sql = '
DECLARE @CompanyID int
DECLARE @RETURN VARCHAR(500)
DECLARE @ProjectNumber int
DECLARE @ContentID int
DECLARE @VersionNumber int
DECLARE @FileName varchar(400)
select @VersionNumber= V.Number,@ContentID= V.ContentID,@ProjectNumber=P.Number,@FileName=C.Name,@CompanyID=P.CompanyID
from ' + @vTable + ' V
inner join' + @cTable + ' C on V.ContentID=C.ID
inner join Project P on C.ProjectID=P.ID where V.VID='+convert(varchar,@VID)+'
select ''/''+Convert(varchar,@CompanyID)+''/''+Convert(varchar,@ProjectNumber)+''/''+Convert(varchar,@ContentID)+''/''+Convert(varchar,@VersionNumber)+''/''+@FileName'
SET @ParmDefinition = N'@RETURN VARCHAR(500) OUTPUT'
exec sp_executesql @sql, @ParmDefinition, @RETURN OUTPUT
set @RETURN=(select @RETURN)
RETURN @RETURN
END
but when I execute this function
select dbo.getContentURL(177,1)
this gives error:
The name 'select --------------------' is not a valid identifier.
how to return this executed value? can any one help me please?
Best Answer
I can't say that I understand what it is that You are trying to do...
The thing is, just like You have already been told, that You can't use Dynamic SQL in a Function.
Using a Stored Procedure will solve the problem You encountered:
(I did not try to find another logic to retrieving the same data, which is possible, I just converted Your Function to a Stored Procedure)
If this does not meet Your needs please post what exactly You are trying to do.
Good Luck,
Roi