我又有一个c#中sql语句转换成存储过程出现了问题。希望帮忙解决。请帮我看看对否??仍然是当ChoiceResult 为1,2,3时候,分别生成3条记录。
表news的字段如下:
UserID int
Title nchar
Subtitle nchar
Content nchar
Author nchar
Flag char
NDate datetime
Delflag char
Topflag char
NewskindID int
请帮我看看存储过程对吗?
CREATE PROCEDURE AddNews
@ChoiceResult varchar(100),
@Title varchar(200),
@Subtitle varchar(300),
@Content varchar(400),
@UserIDD int,
@Author varchar(100),
@Flag char,
@NDate datetime
as
begin
declare @sql nvarchar(1000)
set @sql=N' insert into News select '+cast(@UserIDD as nvarchar)+N' UserID,Title,Subtitle,Content,Author,Flag,NDate,NewskindID from Kindnews where NewskindID in ('+@ChoiceResult+N')'
--print @sql
exec(@sql)
end
GO
报错说
插入错误: 列名或所提供值的数目与表定义不匹配。列名 'Title' 无效。列名 'Subtitle' 无效。列名 'Content' 无效。列名 'Author' 无效。列名 'Flag' 无效。列名 'NDate' 无效。
表news的字段如下:
UserID int
Title nchar
Subtitle nchar
Content nchar
Author nchar
Flag char
NDate datetime
Delflag char
Topflag char
NewskindID int
请帮我看看存储过程对吗?
CREATE PROCEDURE AddNews
@ChoiceResult varchar(100),
@Title varchar(200),
@Subtitle varchar(300),
@Content varchar(400),
@UserIDD int,
@Author varchar(100),
@Flag char,
@NDate datetime
as
begin
declare @sql nvarchar(1000)
set @sql=N' insert into News select '+cast(@UserIDD as nvarchar)+N' UserID,Title,Subtitle,Content,Author,Flag,NDate,NewskindID from Kindnews where NewskindID in ('+@ChoiceResult+N')'
--print @sql
exec(@sql)
end
GO
报错说
插入错误: 列名或所提供值的数目与表定义不匹配。列名 'Title' 无效。列名 'Subtitle' 无效。列名 'Content' 无效。列名 'Author' 无效。列名 'Flag' 无效。列名 'NDate' 无效。
我不太会写存储过程。所以希望帮忙!555
cast(@UserIDD as nvarchar) + ' UserID,''' +
@Title + ''' Title,' +
@Subtitle +''' Subtitle,''' +
@Content + ''' Content, '''+
@Author + ''' Author,''' +
@Flag + ''' Flag,''' +
cast(@NDate as nvarchar) +''' NDate,'+
' NewskindID from Kindnews where NewskindID in ('+@ChoiceResult+N')'
set @sql=' insert into News select '+
cast(@UserIDD as nvarchar) + ' UserID,''' +
@Title + ''' Title,''' +
@Subtitle +''' Subtitle,''' +
@Content + ''' Content, '''+
@Author + ''' Author,''' +
@Flag + ''' Flag,''' +
cast(@NDate as nvarchar) +''' NDate,'+
' NewskindID from Kindnews where NewskindID in ('+@ChoiceResult+N')'
insert into News
select 2 UserID,
'a' Title,
'd' Subtitle,
'c:\aa\' Content,
'dddd' Author,
'0' Flag,
'05 7 2005 12:00AM' NDate,
NewskindID from Kindnews
where NewskindID in (1,2)
@RETURN_VALUE = 0
drop table [dbo].[News]
GOCREATE TABLE [dbo].[News] (
[NewsID] [int] IDENTITY (1, 1) NOT NULL ,
[UserID] [int] NULL ,
[Title] [nchar] (200) COLLATE Chinese_PRC_CI_AS NULL ,
[Subtitle] [nchar] (300) COLLATE Chinese_PRC_CI_AS NULL ,
[Content] [nchar] (16) COLLATE Chinese_PRC_CI_AS NULL ,
[Author] [nchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[Flag] [char] (1) COLLATE Chinese_PRC_CI_AS NULL ,
[NDate] [datetime] NULL ,
[Delflag] [char] (1) COLLATE Chinese_PRC_CI_AS NULL ,
[Topflag] [char] (1) COLLATE Chinese_PRC_CI_AS NULL ,
[NewskindID] [int] NULL
) ON [PRIMARY]
GO
drop table [dbo].[Kindnews]
GOCREATE TABLE [dbo].[Kindnews] (
[NewskindID] [int] IDENTITY (1, 1) NOT NULL ,
[NodeCode] [varchar] (200) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Text] [varchar] (200) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO
几个flag为什么不用smallint或者tinyint?
drop table [dbo].[News]
GOCREATE TABLE [dbo].[News] (
[NewsID] [int] IDENTITY (1, 1) NOT NULL ,
[UserID] [int] NULL ,
[Title] [nchar] (200) COLLATE Chinese_PRC_CI_AS NULL ,
[Subtitle] [nchar] (300) COLLATE Chinese_PRC_CI_AS NULL ,
[Content] [nchar] (16) COLLATE Chinese_PRC_CI_AS NULL ,
[Author] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[Flag] [tinyint] NULL ,
[NDate] [datetime] NULL ,
[Delflag] [tinyint] NULL ,
[Topflag] [tinyint] NULL ,
[NewskindID] [int] NULL
) ON [PRIMARY]
GO
drop table [dbo].[News]
GOCREATE TABLE [dbo].[News] (
[NewsID] [int] IDENTITY (1, 1) NOT NULL ,
[UserID] [int] NULL ,
[Title] [varchar] (200) COLLATE Chinese_PRC_CI_AS NULL ,
[Subtitle] [varchar] (300) COLLATE Chinese_PRC_CI_AS NULL ,
[Content] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[Author] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[Flag] [tinyint] NULL ,
[NDate] [datetime] NULL ,
[Delflag] [tinyint] NULL ,
[Topflag] [tinyint] NULL ,
[NewskindID] [int] NULL
) ON [PRIMARY]
GO
@ChoiceResult varchar(100),
@Title varchar(200),
@Subtitle varchar(300),
@Content varchar(400),
@UserIDD int,
@Author varchar(100),
@NDate datetime
as
begin
declare @sql nvarchar(1000)
set @sql=' insert into News select '+
cast(@UserIDD as nvarchar) + ' UserID,''' +
@Title + ''' Title,''' +
@Subtitle +''' Subtitle,''' +
@Content + ''' Content, '''+
@Author + ''' Author,''' +
cast(@NDate as nvarchar) +''' NDate,'+
' NewskindID from Kindnews where NewskindID in ('+@ChoiceResult+N')'
exec(@sql)
end
GO
默认是0
Topflag也是
CREATE PROCEDURE AddNews
@ChoiceResult varchar(100),
@Title varchar(200),
@Subtitle varchar(300),
@Content varchar(400),
@UserIDD int,
@Author varchar(100),
@Flag tinyint,
@NDate datetime
as
begin
declare @sql varchar(1000)
set @sql=' insert into News select '+
str(@UserIDD) + ' UserID,''' +
@Title + ''' Title,''' +
@Subtitle +''' Subtitle,''' +
@Content + ''' Content, '''+
@Author + ''' Author, ' +
str(@Flag) + ' Flag,''' +
cast(@NDate as nvarchar) +''' NDate,'+
'0 Delflag, 0 Topflag,'+
' NewskindID NewskindID from Kindnews where NewskindID in ('+@ChoiceResult+N')' print @sql
--exec(@sql)
end
GO
--测试exec AddNews '1,2','标题','子标题','内容',3242,'作者',1,'2005-7-7'--结果
CREATE PROCEDURE AddNews
@ChoiceResult varchar(100),
@Title varchar(200),
@Subtitle varchar(300),
@Content varchar(400),
@UserIDD int,
@Author varchar(100),
@Flag tinyint,
@NDate datetime
as
begin
declare @sql varchar(1000)
set @sql=' insert into News select '+
str(@UserIDD) + ' UserID,''' +
@Title + ''' Title,''' +
@Subtitle +''' Subtitle,''' +
@Content + ''' Content, '''+
@Author + ''' Author, ' +
str(@Flag) + ' Flag,''' +
cast(@NDate as nvarchar) +''' NDate,'+
'0 Delflag, 0 Topflag,'+
' NewskindID NewskindID from Kindnews where NewskindID in ('+@ChoiceResult+N')' print @sql
--exec(@sql)
end
GO
--测试exec AddNews '1,2','标题','子标题','内容',3242,'作者',1,'2005-7-7'--结果
{
string sql="exec AddNews '{0}','{1}','{2}','{3}',{4},'{5}',{6},'{7}'";
sql=string.Format(sql,ChoiceResult,Title,Subtitle,Content,UserID,Author,flag,NDate);
SqlHelper.ExecuteNonQuery(SqlHelper.CONN_STRING_NON_DTC,CommandType.Text,sql);
}
CREATE PROCEDURE AddNews
@ChoiceResult varchar(100),
@Title varchar(200),
@Subtitle varchar(300),
@Content varchar(400),
@UserIDD int,
@Author varchar(100),
@Flag tinyint,
@NDate datetime
as
begin
declare @sql varchar(1000)
set @sql=' insert into News select '+
str(@UserIDD) + ' UserID,N''' +
@Title + N''' Title,N''' +
@Subtitle +N''' Subtitle,N''' +
@Content + N''' Content, N'''+
@Author + N''' Author, ' +
str(@Flag) + ' Flag,N''' +
cast(@NDate as nvarchar) +N''' NDate,'+
'0 Delflag, 0 Topflag,'+
' NewskindID NewskindID from Kindnews where NewskindID in ('+@ChoiceResult+N')' --print @sql
exec(@sql)
end
GO