现在有一个函数,如下:
CREATE FUNCTION dbo.search_se(@id nvarchar(10))
RETURNS nvarchar(300)
AS
BEGINDECLARE @re nvarchar(3000)
SET @re=''
SELECT @re=@re+ '/'+ CAST(Bs as varchar)
FROM tableName
WHERE Id=@Id
return STUFF(@re,1,1,'')
END
现在tableName需要改变且该函数在多个存储过程中都调用。经过查资料发现函数内不能使用动态SQL。现在该怎么办?
CREATE FUNCTION dbo.search_se(@id nvarchar(10))
RETURNS nvarchar(300)
AS
BEGINDECLARE @re nvarchar(3000)
SET @re=''
SELECT @re=@re+ '/'+ CAST(Bs as varchar)
FROM tableName
WHERE Id=@Id
return STUFF(@re,1,1,'')
END
现在tableName需要改变且该函数在多个存储过程中都调用。经过查资料发现函数内不能使用动态SQL。现在该怎么办?
CREATE proc search_se
@id nvarchar(10),
@re nvarchar(3000) output
ASSET @re=''
SELECT @re=@re+ '/'+ CAST(Bs as nvarchar)
FROM tableName
WHERE Id=@Id
@id nvarchar(10),
@tablename varchar(20),
@re nvarchar(3000) output
AS
declare @sql nvarchar(4000)
SET @re='' set @sql='SELECT @re=@re+ ''/''+ CAST(Bs as nvarchar) FROM '+@tableName+' WHERE s='+ltrim(@Id)
exec sp_executesql @sql,N'@re nvarchar(3000) output',@re output