SQL批处理查询执行问题 [英] SQL Batch Query Execution Problem

问题描述

Daer Friends，



我在C＃.Net 2.0中从前端运行SQL批处理文件，我在检查列是否存在于临时表中或不。



如果不存在则创建它。但在任何情况下条件都不 true 。



不知道我在写临时表中列的检查条件错误或者我应该尝试不同的方式。

Daer Friends,

I am running a batch file in SQL from front end in C#.Net 2.0 where i am checking that whether the column exists in temporary table or not.

If not exists then create it. But the condition doesn't true in any case.

Don't know i am writing the check condition for column in temporary table wrong or i should try a different way around.

SET @DQuery = 'IF NOT EXISTS (SELECT col.name FROM tempdb.sys.objects obj INNER JOIN tempdb.sys.columns col ON obj.object_id = col.object_id WHERE col.name = ''child_FG'+ CAST(@INTCOUNT AS VARCHAR) +''' AND obj.name LIKE ''#tData%'')'
SET @DQuery = @DQuery + ' BEGIN ' 
SET @DQuery = @DQuery + ' ALTER TABLE #tData ADD child_FG'+ CAST(@INTCOUNT AS VARCHAR) +' varchar(26) ' 
SET @DQuery = @DQuery + ' ALTER TABLE #tData ADD child_CKT'+ CAST(@INTCOUNT AS VARCHAR) +' decimal(18,4) ' 
SET @DQuery = @DQuery + ' END' 
strSQL += " execute(@DQuery)";
SET @DQuery = ' UPDATE #tData SET #tData.child_FG'+ CAST(@INTCOUNT AS VARCHAR) +' = '''+ @CHILDFG +''' WHERE #tData.[fg Code] = '''+ @InnerBOMID +''''
execute(@DQuery)
SET @DQuery = ' UPDATE #tData SET child_CKT'+ CAST(@INTCOUNT AS VARCHAR) +' = '+ CAST(@CHILD_CKT AS VARCHAR) +' WHERE #tData.[fg Code] = '''+ @InnerBOMID +''''";
execute(@DQuery)

这里我得到错误： -



a）。 无效的列名称child_FG1。

b）。 无效的列名称child_FG2。





上述查询在游标中运行。



如果我在SQL Server 2008中运行这个，那么我得到结果但是在SQL server 2005中上面提到的查询抛出错误（如上所述）。



亲切的帮助。



关于

Here i get the error as:-

a). "Invalid Column Name child_FG1".
b). "Invalid Column Name child_FG2".


The above mentioned queries are running within a cursor.

If i am running this in SQL Server 2008 then i get the result but in SQL server 2005 the above mentioned query throws error (as mentioned).

Kindy help.

Regards

推荐答案

解决方案我发现： -



我正在用临时数据库检查表中的表名，因为表可能在不同的会话中创建，所以我创建了歧义，所以我将查询更改为

Solution i found:-

I was checking the table in temporary database with table name which was creating ambiguity as the table might be created in a different session so i changed the query to

SET @DQuery = 'IF NOT EXISTS (SELECT col.name FROM tempdb.sys.objects obj INNER JOIN tempdb.sys.columns col ON obj.object_id = col.object_id WHERE col.name = ''child_FG'+ CAST(@INTCOUNT AS VARCHAR) +''' 
AND OBJECT_ID(''tempdb..#tdata'') = obj.object_id)'
<pre lang="SQL">

它解决了我的问题，因为任何表（临时或物理）的对象ID都是唯一的。



感谢您的朋友们。

and it solved my problem as the object id for any table (temporary or physical) will be unique.

Thanks for your time friends.

这篇关于SQL批处理查询执行问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！