如题,SQL 2000,在调用如下过程时提示"Must declare the variable '@SerID'"
declare @ret int
exec Proc_Insert_TestXmlInfo '11','22','33','44',@ret存储过程:
--测试XML备份
if object_id('Proc_Insert_TestXmlInfo','P') is not null
drop procedure Proc_Insert_TestXmlInfo;
gocreate proc Proc_Insert_TestXmlInfo
@ModelName nvarchar(20),
@BarcodeNo nvarchar(20),
@Station nvarchar(5),
@XmlInfo varchar(4096),
@Ret int output
as
set nocount on;
begin
declare @tb_name nvarchar(20),@SerID int set @Ret=-1
-- 设置数据表名
if(Month(getdate()) > 6)
begin
set @tb_name = 'Y' + Convert(nvarchar,Year(getdate())) + '_2'
end
else
begin
set @tb_name = 'Y' + Convert(nvarchar,Year(getdate())) + '_1'
end -- 判断数据表是否存在
if object_id(@tb_name,'U') is null
begin
exec ('CREATE TABLE [dbo].['+@tb_name+'] (
[SerialNo] [numeric](18, 0) IDENTITY (1, 1) not NULL ,
[ModelName] [varchar] (30) COLLATE Chinese_PRC_CI_AS NULL ,
[BarcodeNo] [varchar] (30) COLLATE Chinese_PRC_CI_AS not NULL ,
[Station] [varchar] (5) COLLATE Chinese_PRC_CI_AS not NULL ,
[Information] [varchar] (4096) COLLATE Chinese_PRC_CI_AS not NULL ,
[Upload_Date] [datetime] default getdate() not NULL
) ON [PRIMARY]')
end
--查询数据是否存在,如果存在就更新插入日期,否则插入数据
exec('select @SerID=SerialNo from '+@tb_name+'where Information='+@XmlInfo)
if (@@rowcount > 0)
begin
exec ('update from '+@tb_name+'set Upload_Date=getdate() where SerialNo=@SerID')
set @Ret=1
end
else
begin
--插入数据
exec ('insert into '+@tb_name+' (ModelName,BarcodeNo,Station,Information)
values('''+@ModelName+''','''+@BarcodeNo+''','''+@Station+''','''+@XmlInfo+''') ')
set @Ret=1
end
end
go
declare @ret int
exec Proc_Insert_TestXmlInfo '11','22','33','44',@ret存储过程:
--测试XML备份
if object_id('Proc_Insert_TestXmlInfo','P') is not null
drop procedure Proc_Insert_TestXmlInfo;
gocreate proc Proc_Insert_TestXmlInfo
@ModelName nvarchar(20),
@BarcodeNo nvarchar(20),
@Station nvarchar(5),
@XmlInfo varchar(4096),
@Ret int output
as
set nocount on;
begin
declare @tb_name nvarchar(20),@SerID int set @Ret=-1
-- 设置数据表名
if(Month(getdate()) > 6)
begin
set @tb_name = 'Y' + Convert(nvarchar,Year(getdate())) + '_2'
end
else
begin
set @tb_name = 'Y' + Convert(nvarchar,Year(getdate())) + '_1'
end -- 判断数据表是否存在
if object_id(@tb_name,'U') is null
begin
exec ('CREATE TABLE [dbo].['+@tb_name+'] (
[SerialNo] [numeric](18, 0) IDENTITY (1, 1) not NULL ,
[ModelName] [varchar] (30) COLLATE Chinese_PRC_CI_AS NULL ,
[BarcodeNo] [varchar] (30) COLLATE Chinese_PRC_CI_AS not NULL ,
[Station] [varchar] (5) COLLATE Chinese_PRC_CI_AS not NULL ,
[Information] [varchar] (4096) COLLATE Chinese_PRC_CI_AS not NULL ,
[Upload_Date] [datetime] default getdate() not NULL
) ON [PRIMARY]')
end
--查询数据是否存在,如果存在就更新插入日期,否则插入数据
exec('select @SerID=SerialNo from '+@tb_name+'where Information='+@XmlInfo)
if (@@rowcount > 0)
begin
exec ('update from '+@tb_name+'set Upload_Date=getdate() where SerialNo=@SerID')
set @Ret=1
end
else
begin
--插入数据
exec ('insert into '+@tb_name+' (ModelName,BarcodeNo,Station,Information)
values('''+@ModelName+''','''+@BarcodeNo+''','''+@Station+''','''+@XmlInfo+''') ')
set @Ret=1
end
end
go
解决方案 »
- 插入文本文件时怎样去掉双引号?
- 数据库的问题(截段)
- 每次操作都要操作多张表,是否都要用事务,事务用多了是否会不好?
- 为什么我的数据库连接会有很多个?(图),怎样才能查看是哪些代码占用了连接,
- 菜鸟提问! trigger可不可以跨服务器访问操作啊 急!!!!!!
- sql server 2005 如何安装 Reporting Services 报表服务?
- 在WIN2000 Profession下为什么不能安装sqlserve 7.0?
- 请教高手:希望用sql语句完成本地(内网)数据库向公网数据库复制表(要求数据与表结构一起完成)
- SQL2005连接数据库
- 大表索引的问题
- 统计sql,谢谢。。
- str函数在什么情况下四舍五入
SET @SQL = N'update from '+@tb_name+'set Upload_Date=getdate() where SerialNo=@SerID'
EXEC SP_EXECUTESQL @SQL ,N'@SerID INT',@SerID
if object_id('Proc_Insert_TestXmlInfo','P') is not null
drop procedure Proc_Insert_TestXmlInfo;
go
create proc Proc_Insert_TestXmlInfo
@ModelName nvarchar(20),
@BarcodeNo nvarchar(20),
@Station nvarchar(5),
@XmlInfo varchar(4096),
@Ret int output
as
set nocount on;
begin
declare @tb_name nvarchar(20),@SerID int
set @Ret=-1
-- 设置数据表名
if(Month(getdate()) > 6)
begin
set @tb_name = 'Y' + Convert(nvarchar,Year(getdate())) + '_2'
end
else
begin
set @tb_name = 'Y' + Convert(nvarchar,Year(getdate())) + '_1'
end
-- 判断数据表是否存在
if object_id(@tb_name,'U') is null
begin
exec ('CREATE TABLE [dbo].['+@tb_name+'] (
[SerialNo] [numeric](18, 0) IDENTITY (1, 1) not NULL ,
[ModelName] [varchar] (30) COLLATE Chinese_PRC_CI_AS NULL ,
[BarcodeNo] [varchar] (30) COLLATE Chinese_PRC_CI_AS not NULL ,
[Station] [varchar] (5) COLLATE Chinese_PRC_CI_AS not NULL ,
[Information] [varchar] (4096) COLLATE Chinese_PRC_CI_AS not NULL ,
[Upload_Date] [datetime] default getdate() not NULL
) ON [PRIMARY]')
end
--查询数据是否存在,如果存在就更新插入日期,否则插入数据
exec('select @SerID=SerialNo from '+@tb_name+'where Information='+@XmlInfo)
if (@@rowcount > 0)
begin
exec ('update from '+@tb_name+'set Upload_Date=getdate() where SerialNo=@SerID')--应该是这儿的问题,获取到的@serid应该是变量添加
set @Ret=1
end
else
begin
--插入数据
exec ('insert into '+@tb_name+' (ModelName,BarcodeNo,Station,Information)
values('''+@ModelName+''','''+@BarcodeNo+''','''+@Station+''','''+@XmlInfo+''') ')
set @Ret=1
end
end
拼接字符串注意参数的引用。
SET @SQL = N'
DECLARE @SerID INT
select @SerID=SerialNo from '+@tb_name+'where Information='+@XmlInfo + '
if (@@rowcount > 0)
begin
update from '+@tb_name+' set Upload_Date=getdate() where SerialNo=@SerID
set @Ret=1
end
else
begin --插入数据
insert into '+@tb_name+' (ModelName,BarcodeNo,Station,Information)
values('''+@ModelName+''','''+@BarcodeNo+''','''+@Station+''','''+@XmlInfo+''')
set @Ret=1
end'
EXEC SP_EXECUTESQL @SQL ,N'@Ret INT OUTPUT',@Ret OUTPUT