User Defined Function Return value

Question

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?

• Martin Smith: You can't use dynamic SQL in functions and would need to use EXEC() even if you could. But it doesn't look like it needs it anyway. What is ` --------------------` supposed to be? Also how do you intend to use this? scalar UDFS that do data access can be performance killers.
• M.S.Nayak: ya within '-------------' im creating dynamic tablename depending on @VID so... Can I have any altenate solution for this?
• Martin Smith: Dynamic SQL is not permitted in functions so you won't be able to do this. Maybe you could use a View partitioned on @VID please explain more about your situation.
• M.S.Nayak: I have edited my question, here is my actual Function..

Answer 1

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)

CREATE PROC [dbo].[usp_getContentURL] 
(@VID INT, @accountID INT) 
AS 
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) = '' 
SET @sql = ' 
DECLARE @CompanyID int
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'
EXEC (@sql)
GO

If this does not meet Your needs please post what exactly You are trying to do.
Good Luck,
Roi

Answer 2

you can't use dynamic SQL in functions, for best understanding to solve your problem take a look here

Answer 3

CREATE PROC [dbo].[usp_getContentURL] 
(@VID INT, @accountID INT) 
AS 
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) = '' 
SET @sql = ' 
DECLARE @CompanyID int
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'
EXEC (@sql)
GO

• Erik Reasonable Rates Darling: How is this different from the accepted answer?
• Martin Smith: I diffed them just in case there was some difference and they are identical