Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 2 down vote favorite

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?

share|improve this question
1  
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. – Martin Smith Sep 10 '12 at 11:32
ya within '-------------' im creating dynamic tablename depending on @VID so... Can I have any altenate solution for this? – M.S.Nayak Sep 10 '12 at 11:52
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. – Martin Smith Sep 10 '12 at 11:53
I have edited my question, here is my actual Function.. – M.S.Nayak Sep 10 '12 at 12:14

2 Answers

up vote 1 down vote accepted

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

share|improve this answer
up vote 1 down vote

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.