如何使代码动态化 [英] How Can I Make Code Dynamic

问题描述

我有4个表格，我想让这个查询适用于我更改表格的所有内容。



顺便说一下，存储过程查询工作正常但是只有当我把桌子弄脏了。



以下是我的代码。我已经评论了动态代码，因为它没有工作



如果专家编辑它会很高兴:)

I have abt 4 tables and i wanna make this query apply for all when i change the table.

By the way may stored procedure query is working perfectly but only if i make the table static.

Below is my code. I've commented the dynamic code out because it wasn't working

Will be glad if the experts edit it :)

ALTER PROC [dbo].[spTest]
(
	@Selectstatement VARCHAR (255) --Show ALL--
	--@Table_name  sysname
)
AS
BEGIN
		--DECLARE @Dynamictbl nvarchar(255)
	-- IF Conditions--

	IF @Selectstatement = 'ALL'
		BEGIN
		--SET @Dynamictbl = ('SELECT * FROM ' + @Table_name + ' WHERE [Geschäftsbereich]=''AB''')
			(SELECT * 
				FROM tbl201102  
				WHERE [Geschäftsbereich]='AB')
				ORDER BY [Projectname] ASC
		RETURN
		END

So this is how i went abt it but wenn i execute notthing happens

< br $>

ALTER PROC [dbo].[spTest]
(
	@Selectstatement VARCHAR (255) --Show ALL--
	@Table_name  sysname
)
AS
BEGIN
		DECLARE @Dynamictbl nvarchar(255)
	-- IF Conditions--

	IF @Selectstatement = 'ALL'
		BEGIN
	SET @Dynamictbl = ('SELECT * FROM ' + @Table_name + ' WHERE [Geschäftsbereich]=''AB''')
			
		RETURN
		END
END
GO

EXEC spTest @Selectstatement = 'ALL', @Table_name = 'tbl201409'

推荐答案

当然。您组成动态查询字符串（假设结果在语法上是正确的），但您不执行它。请点击此处查看：在存储过程中构建动态SQL [ ^ ]， http://www.mssqltips.com/sqlservertip/ 1160 / execute-dynamic-sql-commands-in-sql-server / [ ^ ]

Of course. You compose the dynamic query string (let's suppose the result is syntactically correct), but you don't execute it. Check here for example: Building Dynamic SQL In a Stored Procedure[^], http://www.mssqltips.com/sqlservertip/1160/execute-dynamic-sql-commands-in-sql-server/[^]

ALTER PROC [dbo].[spTest]
(
    @Selectstatement VARCHAR (255) --Show ALL--
    @Table_name  sysname
)
AS
BEGIN
        DECLARE @Dynamictbl nvarchar(255)
    -- IF Conditions--

    IF @Selectstatement = 'ALL'
        BEGIN
    SET @Dynamictbl = ('SELECT * FROM ' + @Table_name + ' WHERE [Geschäftsbereich]=''AB''')
        EXECUTE @Dynamictbl
        RETURN
        END
END
GO

您可以按程序组合查询脚本，并通过它作为存储过程的参数，并在存储过程中通过关键字EXEC（示例：exec（'select * from mytable'））执行它。但是，您必须了解，当您调用存储过程时，会创建一个新会话，并且当您在存储过程中通过关键字EXEC执行脚本时，会创建另一个会话以执行。因此，如果要在存储过程中编写任何复杂的脚本（例如，在存储过程中，您必须通过动态脚本查询记录集，并对记录集执行其他操作），则应使用全局临时表（表名以SQL Server的##）开头但不是普通的临时表（表名以＃开头），它可以共享到不同的会话（只能在同一个会话中访问普通的临时表）。所以。在这种情况下，我总是写下面的脚本：

You can combine your query script by program, and pass it as then parameter of the stored procedure, and execute it by keyword "EXEC"(Sample: exec('select * from mytable')) in the stored procedure. But you have to understand, when you called a stored procedure, a new session was created, and when you execute a script by keyword "EXEC" in stored procedure, another session was created for executing. So, if you want write any complex script in the stored procedure(for example, in stored procedure, you have to query a record set by a dynamic script, and do something others to the record set), you should use global temp table(table name start with ##) of SQL Server but not normal temp table(table name start with #), it can share to different session(you can access normal a temp table in same session only). So. In this case, I always write like following script:

Declare @cStatemtn nvarchar(max)
Declare @cFieldValue1 nvarchar(100)

Set @cStatement = 'select * from mytable into ##tmpTbl where field1 = ' + @cFieldValue1

if not (select object_id('tempdb.. ##tmpTbl ')) is null drop table ##tmpTbl
Exec(@cStatement)

Select [ID], [Name], sum(score) from ##tmpTbl
Group by ID, [ID], [Name]
if not (select object_id('tempdb.. ##tmpTbl ')) is null drop table ##tmpTbl

这篇关于如何使代码动态化的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！