动态sql查询java_使用动态sql查询更新表

是的，可以在SQL Server 2005中引入synonym mechanism的帮助 . 因此，您可以在设置过程中根据 ProjectSettings 表中的数据创建同义词，并且可以在函数中使用它 . 您的代码看起来像这样：

UPDATE: 函数代码在这里被注释，因为它仍然包含动态SQL，它在功能上不起作用，正如Kurt在他的评论中所说的那样 . 新版本的功能低于此代码 .

-- Creating synonym for TXTTable table

-- somewhere in code when processing current settings

-- Suppose your synonym name is 'TextLibrary'

--

-- Drop previously created synonym

IF EXISTS (SELECT * FROM sys.synonyms WHERE name = N'TextLibrary')

DROP SYNONYM TextLibrary

-- Creating synonym using dynamic SQL

-- Local variables

DECLARE @TextLibraryDatabaseName varchar(1000)

DECLARE @nvcSqlQuery varchar(1000)

-- get the report language database name

SELECT @TextLibraryDatabaseName = TextLibraryDatabaseName FROM ProjectSettings

SET @nvcSqlQuery = 'CREATE SYNONYM TextLibrary FOR [' + @TextLibraryDatabaseName + '].dbo.TXTTable'

EXEC(@nvcSqlQuery)

-- Synonym created

/* UPDATE: This code is commented but left for discussion consistency

-- Function code

CREATE FUNCTION [dbo].[GetTextFromLib]

(

@TextID int,

@LanguageColumn Varchar(5)

)

RETURNS varchar(255)

AS

BEGIN

-- return variables

DECLARE @ResultVar varchar(255)

-- Local variables

DECLARE @nvcSqlQuery varchar(1000)

SET @nvcSqlQuery = 'SELECT @ResultVar =' + @LanguageColumn + ' FROM TextLibrary WHERE TEXTID = ' + cast(@TextID as varchar(30))

EXEC(@nvcSqlQuery)

-- Return the result of the function

RETURN @ResultVar

END

*/

UPDATE 这是解决问题的又一次尝试 . 现在它使用了一些XML技巧：

-- Function code

CREATE FUNCTION [dbo].[GetTextFromLib]

(

@TextID int,

@LanguageColumn Varchar(5)

)

RETURNS varchar(255)

AS

BEGIN

-- return variables

DECLARE @ResultVar varchar(255)

-- Local variables

DECLARE @XmlVar XML

-- Select required record into XML variable

-- XML has each table column value in element with corresponding name

SELECT @XmlVar = ( SELECT * FROM TextLibrary

WHERE TEXTID = @TextID

FOR XML RAW, ELEMENTS )

-- Select value of required element from XML

SELECT @ResultVar = Element.value('(.)[1]', 'varchar(255)')

FROM @XmlVar.nodes('/row/*') AS T(Element)

WHERE Element.value('local-name(.)', 'varchar(50)') = @LanguageColumn

-- Return the result of the function

RETURN @ResultVar

END

希望这可以帮助 .