GO
CREATE PROC InsertUserConInfo(
@ConTableName varchar(30),
@ConName varchar(30),
@ConAddress varchar(50),
@ConPhone varchar(20),
@ConEmail varchar(40),
@ConBirthday datetime,
@ConGroup varchar(20),
@ConMark varchar(50)
)
AS
DECLARE @str_sql varchar(100)
SET @str_sql='INSERT INTO'+ @ConTableName+'(ConName,ConAddress,ConPhone,ConEmail,ConBirthday,ConGroup,ConMark)
VALUES('+@ConName+','+@ConAddress+','+@ConPhone+','+@ConEmail+','+@ConBirthday+','+@ConGroup+','+@ConMark+')'
EXEC(@str_sql)
GO
由于我要动态的插入表中,所以我启用了一个字符串变量来保存表名。我的执行语句如下:InsertuserConInfo 'Table1','小李,'湖北','13167978778','[email protected]','1986-6-6','a','无备注'(其中表Table1事先已经存在建立了)
可是就时提示这样的错误:服务器: 消息 241,级别 16,状态 1,过程 InsertUserConInfo,行 13
从字符串转换为 datetime 时发生语法错误。
帮帮小弟
CREATE PROC InsertUserConInfo(
@ConTableName varchar(30),
@ConName varchar(30),
@ConAddress varchar(50),
@ConPhone varchar(20),
@ConEmail varchar(40),
@ConBirthday datetime,
@ConGroup varchar(20),
@ConMark varchar(50)
)
AS
DECLARE @str_sql varchar(100)
SET @str_sql='INSERT INTO'+ @ConTableName+'(ConName,ConAddress,ConPhone,ConEmail,ConBirthday,ConGroup,ConMark)
VALUES('+@ConName+','+@ConAddress+','+@ConPhone+','+@ConEmail+','+@ConBirthday+','+@ConGroup+','+@ConMark+')'
EXEC(@str_sql)
GO
由于我要动态的插入表中,所以我启用了一个字符串变量来保存表名。我的执行语句如下:InsertuserConInfo 'Table1','小李,'湖北','13167978778','[email protected]','1986-6-6','a','无备注'(其中表Table1事先已经存在建立了)
可是就时提示这样的错误:服务器: 消息 241,级别 16,状态 1,过程 InsertUserConInfo,行 13
从字符串转换为 datetime 时发生语法错误。
帮帮小弟
---------
执行语句有问题InsertuserConInfo 'Table1','小李,'湖北',
改成
InsertuserConInfo 'Table1','小李','湖北',
@ConTableName varchar(30),
@ConName varchar(30),
@ConAddress varchar(50),
@ConPhone varchar(20),
@ConEmail varchar(40),
@ConBirthday datetime,
@ConGroup varchar(20),
@ConMark varchar(50)
)
AS
DECLARE @str_sql varchar(100)
SET @str_sql='INSERT INTO'+ @ConTableName+'(ConName,ConAddress,ConPhone,ConEmail,ConBirthday,ConGroup,ConMark)
VALUES('''+@ConName+''','''+@ConAddress+''','''+@ConPhone+''','''+@ConEmail+''','''+@ConBirthday+''','''+@ConGroup+''','''+@ConMark+''')'
EXEC(@str_sql)
GO
CREATE PROC InsertUserConInfo(
@ConTableName varchar(30),
@ConName varchar(30),
@ConAddress varchar(50),
@ConPhone varchar(20),
@ConEmail varchar(40),
@ConBirthday datetime,
@ConGroup varchar(20),
@ConMark varchar(50)
)
AS
DECLARE @str_sql varchar(500)
SET @str_sql='INSERT INTO '+ @ConTableName+'(ConName,ConAddress,ConPhone,ConEmail,ConBirthday,ConGroup,ConMark)
VALUES('''+@ConName+''','''+@ConAddress+''','''+@ConPhone+''','''+@ConEmail+''','''+convert(char(10),@ConBirthday,120)+''','''+@ConGroup+''','''+@ConMark+''')'
EXEC(@str_sql)
GO exec InsertuserConInfo 'Table1','小李','湖北','13167978778','[email protected]','1986-6-6','a','无备注'
convert(varchar(16),@ConBirthday,120)
原来要用''','''来表示???
在解释下!
sql里面的字符串连接是怎么一个搞法的?
那查询语句,比如按SelectType查询,条件是SelectValue。
改怎么写,我怎么还是不会呢?
我上面的写的有问题,该怎么改啊!
--这里有两个致命错误
1.SET @str_sql='INSERT INTO'+ @ConTableName+'(ConName,ConAddress,ConPhone,ConEmail,ConBirthday,ConGroup,ConMark)
此句请看好了,假如@ConTableName='customer' 那么它将变成什么样 insert intocustomer(ConName...),
显然是错误的了,
2.@ConBirthday为datetime,你现在是用字符窜来拼 SQL,那么,字符窜和时间能拼么,显然不能,故需要将@ConBirthday转换为
字符窜
修改后的SQL:'
--请在sql server2005上运行
CREATE PROC InsertUserConInfo(
@ConTableName varchar(30),
@ConName varchar(30),
@ConAddress varchar(50),
@ConPhone varchar(20),
@ConEmail varchar(40),
@ConBirthday datetime,
@ConGroup varchar(20),
@ConMark varchar(50)
)
AS
DECLARE @str_sql nvarchar(max) --定义成nvarchar(max),sql server2005的特性
--CHAR(9) 制表符
SET @str_sql=N'INSERT INTO'+CHAR(9)+ @ConTableName+CHAR(9)+N'(ConName,ConAddress,ConPhone,ConEmail,ConBirthday,ConGroup,ConMark)
VALUES('+@ConName+','+@ConAddress+','+@ConPhone+','+@ConEmail+','+CAST(@ConBirthday AS nvarchar(25))+','+@ConGroup+','+@ConMark+')' EXEC sp_executesql(@str_sql)--用此防注入--EXEC(@str_sql)
GO
1.SET @str_sql='INSERT INTO'+ @ConTableName+'(ConName,ConAddress,ConPhone,ConEmail,ConBirthday,ConGroup,ConMark)
此句请看好了,假如@ConTableName='customer' 那么它将变成什么样 insert intocustomer(ConName...),
显然是错误的了,
2.@ConBirthday为datetime,你现在是用字符窜来拼 SQL,那么,字符窜和时间能拼么,显然不能,故需要将@ConBirthday转换为
字符窜
修改后的SQL:'
--请在sql server2000上运行
CREATE PROC InsertUserConInfo(
@ConTableName varchar(30),
@ConName varchar(30),
@ConAddress varchar(50),
@ConPhone varchar(20),
@ConEmail varchar(40),
@ConBirthday datetime,
@ConGroup varchar(20),
@ConMark varchar(50)
)
AS
DECLARE @str_sql nvarchar(600)
--CHAR(9) 制表符
SET @str_sql='INSERT INTO'+CHAR(9)+ @ConTableName+CHAR(9)+'(ConName,ConAddress,ConPhone,ConEmail,ConBirthday,ConGroup,ConMark)
VALUES('+@ConName+','+@ConAddress+','+@ConPhone+','+@ConEmail+','+CAST(@ConBirthday AS varchar(25))+','+@ConGroup+','+@ConMark+')' EXEC sp_executesql(@str_sql)--用此防注入--EXEC(@str_sql)
GO