存储过程版本：[code=SQL][/code]
USE [Remote Calibration]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[StoredProcedureAddRows]
@rowindex int,
@tablename nvarchar(80)
AS
BEGIN
DECLARE @newtable table
(SeQuence int PRIMARY KEY NOT NULL,
Calibrator varchar(15),
UUT varchar(15),
Target varchar(15),
Item varchar(45),
Command varchar(45),
TimeInterval int,
Discription varchar(50))
DECLARE @sql nvarchar(80)
SET @sql='INSERT INTO @newtable SELECT * FROM [email protected];
EXEC(@sql)
UPDATE @newtable
SET SeQuence=SeQuence+1 WHERE SeQuence > @rowindex
DECLARE @sql2 varchar(80)
SET @sql2=N'INSERT INTO @newtable(SeQuence) VALUES ([email protected]+N')'
EXEC(@sql2)
END
RETURN
执行存储过程时报错如下：
消息1087，级别15，状态2，第1 行
必须声明表变量"@newtable"。
(0 行受影响)
消息245，级别16，状态1，过程StoredProcedureAddRows，第27 行
在将nvarchar 值'INSERT INTO @newtable(SeQuence) VALUES (' 转换成数据类型int 时失败。
函数版本：[code=SQL][/code]
USE [Remote Calibration]
GO
/****** 对象: UserDefinedFunction [dbo].[AddRows] 脚本日期: 04/17/2012 09:16:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[AddRows]
(
@rowindex int,
@tablename nvarchar(80)
[email protected] int = 5,
@parameter2 datatype
*/
)
RETURNS @newtable TABLE
( SeQuence int PRIMARY KEY NOT NULL,
Calibrator varchar(15),
UUT varchar(15),
Target varchar(15),
Item varchar(45),
Command varchar(45),
TimeInterval int,
Discription varchar(50) )
/* @table_variable TABLE (column1 datatype, column2 datatype) */
AS
BEGIN
DECLARE @sql AS NVARCHAR(100);
SET @sql='INSERT INTO @newtable SELECT * FROM @tablename;';
EXEC sp_executesql
@[email protected]
UPDATE @newtable
SET SeQuence=SeQuence+1 WHERE SeQuence > @rowindex
INSERT INTO @newtable(SeQuence) VALUES (@rowindex)
/* INSERT INTO @table_variable
SELECT ... FROM ... */
RETURN
END
C#调用：
[code=C#][/code]
string strSql = "StoredProcedureAddRows";
SqlCommand cmd = new SqlCommand(strSql, con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@rowindex", SqlDbType.Int).Value = rowtoadd;
cmd.Parameters["@rowindex"].Direction = ParameterDirection.Input;
cmd.Parameters.Add("@tablename", SqlDbType.NVarChar).Value = "ViewKeithley2000_M8831";
cmd.Parameters["@tablename"].Direction = ParameterDirection.Input;
cmd.ExecuteReader();
报错：错误：过程AddRows的请求失败，因为AddRows是表值函数对象
想了很久也找不到错误原因，望高手指点
------解决方案--------------------
DECLARE @sql nvarchar(8000),@tablename varchar(100)
SET @sql='DECLARE @newtable table
(SeQuence int PRIMARY KEY NOT NULL,
Calibrator varchar(15),
UUT varchar(15),
Target varchar(15),
Item varchar(45),
Command varchar(45),
TimeInterval int,
Discription varchar(50))
INSERT INTO @newtable SELECT * FROM [email protected];
EXEC(@sql)