【.NET】拼一段SQL语句

Question

我想在.NET里面拼一段SQL语句
意思是这样的：
搜索A表是否存在
不存在的话返回
存在的话判断列的数量是否>8列
如果不大于8列则自动增加10列日期列（这个日期列指的是即日起10日的日期列，列名可以取即日起10日的日期，比方说2009-3-13、2009-3-14、2009-3-15....）谢谢

Answer 1

//你看下我写的一个类似你说的SQL语句，先判断，再选择执行的。
declare @InnerCode int
declare @Status int
declare @KeyIn varchar(32)
declare @EditName varchar(32)
declare @Seq int
DECLARE @TBName varchar(40) 
DECLARE @RC int
set @InnerCode=106001693
set @KeyIn='king'
set @EditName='king'
set @TBName='A'
if exists(select top 1 * from A where INNER_CODE=@InnerCode and ISVALID=1)
begin
delete from A where [INNER_CODE]=@InnerCode
exec PubDB..getseq  @TBName,@seq output
insert into A(
[SEQ]
      ,[CTIME]
      ,[MTIME]
      ,[ISVALID]
      ,[INNER_CODE]
      ,[ISIN]
      ,[INDX_CODE]
      ,[INDX_ID]
      ,[INDX_CNAME]
      ,[INDX_ENAME]
      ,[INDX_SNAME]
      ,[PY_SNAME]
      ,[PUB_DATE]
      ,[PUB_ORGNAME]
      ,[PUB_ORG]
      ,[AUTH_ORGNAME]
      ,[AUTH_ORG]
      ,[BASE_DATE]
      ,[BASE_POINT]
      ,[SEC_NUM]
      ,[SEC_CLS_CODE]
      ,[SEC_CLS]
      ,[SAMP_CLS]
      ,[SAMP_MKT_CLS]
      ,[ORIENT_CLS_CODE]
      ,[ORIENT_CLS]
      ,[ORG_CLS]
      ,[SAMP_SCOPE]
      ,[SAMP_STD]
      ,[SAMP_METH]
      ,[WHG_METH_CODE]
      ,[WHG_METH]
      ,[CALCU_METH]
      ,[ADJ_FREQ_CODE]
      ,[ADJ_FREQ]
      ,[ADJ_METH]
      ,[MAINTAIN_STATUS]
      ,[USE_STATUS]
      ,[END_DATE]
      ,[INDX_INTRO]
      ,[IS_MKT]
      ,[IS_SAMPE]
      ,[Status]
      ,[KeyIn]
      ,[EditName]
)select @seq,[CTIME]=getdate(),[MTIME]=getdate(),[ISVALID]=1,[INNER_CODE]
      ,[ISIN]
      ,[INDX_CODE]
      ,[INDX_ID]
      ,[INDX_CNAME]
      ,[INDX_ENAME]
      ,[INDX_SNAME]
      ,[PY_SNAME]
      ,[PUB_DATE]
      ,[PUB_ORGNAME]
      ,[PUB_ORG]
      ,[AUTH_ORGNAME]
      ,[AUTH_ORG]
      ,[BASE_DATE]
      ,[BASE_POINT]
      ,[SEC_NUM]
      ,[SEC_CLS_CODE]
      ,[SEC_CLS]
      ,[SAMP_CLS]
      ,[SAMP_MKT_CLS]
      ,[ORIENT_CLS_CODE]
      ,[ORIENT_CLS]
      ,[ORG_CLS]
      ,[SAMP_SCOPE]
      ,[SAMP_STD]
      ,[SAMP_METH]
      ,[WHG_METH_CODE]
      ,[WHG_METH]
      ,[CALCU_METH]
      ,[ADJ_FREQ_CODE]
      ,[ADJ_FREQ]
      ,[ADJ_METH]
      ,[MAINTAIN_STATUS]
      ,[USE_STATUS]
      ,[END_DATE]
      ,[INDX_INTRO]
      ,[IS_MKT]
      ,[IS_SAMPE],[Status]=2,[KeyIn]=@KeyIn,[EditName]=@EditName from A_Statu where INNER_CODE=@InnerCodeend
else
begin
exec PubDB..getseq  @TBName,@seq output
insert into A(
[SEQ]
      ,[CTIME]
      ,[MTIME]
      ,[ISVALID]
      ,[INNER_CODE]
      ,[ISIN]
      ,[INDX_CODE]
      ,[INDX_ID]
      ,[INDX_CNAME]
      ,[INDX_ENAME]
      ,[INDX_SNAME]
      ,[PY_SNAME]
      ,[PUB_DATE]
      ,[PUB_ORGNAME]
      ,[PUB_ORG]
      ,[AUTH_ORGNAME]
      ,[AUTH_ORG]
      ,[BASE_DATE]
      ,[BASE_POINT]
      ,[SEC_NUM]
      ,[SEC_CLS_CODE]
      ,[SEC_CLS]
      ,[SAMP_CLS]
      ,[SAMP_MKT_CLS]
      ,[ORIENT_CLS_CODE]
      ,[ORIENT_CLS]
      ,[ORG_CLS]
      ,[SAMP_SCOPE]
      ,[SAMP_STD]
      ,[SAMP_METH]
      ,[WHG_METH_CODE]
      ,[WHG_METH]
      ,[CALCU_METH]
      ,[ADJ_FREQ_CODE]
      ,[ADJ_FREQ]
      ,[ADJ_METH]
      ,[MAINTAIN_STATUS]
      ,[USE_STATUS]
      ,[END_DATE]
      ,[INDX_INTRO]
      ,[IS_MKT]
      ,[IS_SAMPE]
      ,[Status]
      ,[KeyIn]
      ,[EditName]
)select @seq,[CTIME]=getdate(),[MTIME]=getdate(),[ISVALID]=1,[INNER_CODE]
      ,[ISIN]
      ,[INDX_CODE]
      ,[INDX_ID]
      ,[INDX_CNAME]
      ,[INDX_ENAME]
      ,[INDX_SNAME]
      ,[PY_SNAME]
      ,[PUB_DATE]
      ,[PUB_ORGNAME]
      ,[PUB_ORG]
      ,[AUTH_ORGNAME]
      ,[AUTH_ORG]
      ,[BASE_DATE]
      ,[BASE_POINT]
      ,[SEC_NUM]
      ,[SEC_CLS_CODE]
      ,[SEC_CLS]
      ,[SAMP_CLS]
      ,[SAMP_MKT_CLS]
      ,[ORIENT_CLS_CODE]
      ,[ORIENT_CLS]
      ,[ORG_CLS]
      ,[SAMP_SCOPE]
      ,[SAMP_STD]
      ,[SAMP_METH]
      ,[WHG_METH_CODE]
      ,[WHG_METH]
      ,[CALCU_METH]
      ,[ADJ_FREQ_CODE]
      ,[ADJ_FREQ]
      ,[ADJ_METH]
      ,[MAINTAIN_STATUS]
      ,[USE_STATUS]
      ,[END_DATE]
      ,[INDX_INTRO]
      ,[IS_MKT]
      ,[IS_SAMPE],[Status]=2,[KeyIn]=@KeyIn,[EditName]=@EditName from A_Statu where INNER_CODE=@InnerCodeend

Answer 2

为什么要在.net下拼语句啊,用存储过程不行吗?

Answer 3

存储过程我帮你写出来了,你自己慢慢拼SQL吧..哈哈set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
goALTER procedure [dbo].[pr_Test]
as
begin
if exists (select * from sysobjects where name = 'test2' and type = 'u')
begin
declare @cols int
select @cols = count(1) from sysobjects o 
inner join syscolumns c on o.id = c.id
where o.type = 'u' and o.name='a' if(@cols < 8)
begin
declare @i int
set @i=0
while @i<10
begin
declare @sql nvarchar(4000)
set @sql = 'alter table test2 add [' + convert(nvarchar(10),dateadd(day,@i,getdate()),120) +'] nvarchar(10)'
exec(@sql)
set @i = @i+1
end
end
select 1
end
else
begin
select 0
endend
select * from test2id          2009-03-13 2009-03-14 2009-03-15 2009-03-16 2009-03-17 2009-03-18 2009-03-19 2009-03-20 2009-03-21 2009-03-22
----------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------(0 row(s) affected)