我在SQL查询分析器里粘贴代码执行就没问题，但在前台调用就出现“
传递给 LEFT 或 SUBSTRING 函数的长度参数无效。 关键字 'FROM' 附近"的错误。
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'GetForecastCalQueryForSimpleDataArea' AND type = 'P')
DROP PROCEDURE GetForecastCalQueryForSimpleDataArea
GO
CREATE PROCEDURE GetForecastCalQueryForSimpleDataArea
(
@ForecastId varchar(25), --Forecast运算编号
@PartNumberCustomer varchar(50) --客户料号
)
AS
IF (dbo.IsOverdue() = 1) Return -1;
DECLARE @sql varchar(5000);
DECLARE @area varchar(100);
SELECT @sql = '
WITH t AS
(
SELECT
a.ForecastId,
a.CustomerId,
a.PartNumberCustomer,
a.PartNumberTRIT,
a.BOM,
dbo.GetSubProducts(a.BOM) AS ChildProducts,
dbo.GetSubProductBOMs(a.BOM) AS ChildBOMs,
b.[Text],
SUM(ISNULL(a.QtyCurrent,0)) as QtyCurrent
FROM ForecastData a
INNER JOIN ForecastDataArea b ON a.ForecastId = b.ForecastId AND a.BeginDate = b.BeginDate AND a.PartNumberCustomer = ' + char(39) + @PartNumberCustomer + char(39) +
' WHERE b.ForecastId = ' + char(39) + @ForecastId + char(39) +
' GROUP BY
a.ForecastId,
a.CustomerId,
a.PartNumberCustomer,
a.PartNumberTRIT,
a.BOM,
b.[Text]
)
SELECT
[ForecastId],
[CustomerId],
[PartNumberCustomer],
[PartNumberTRIT],
[BOM],
[ChildProducts],
[ChildBOMs],';
SELECT @area = '';
SELECT @area = @area + '['+ [Text] + '],' FROM dbo.ForecastDataArea Where ForecastId = @ForecastId;
DECLARE @LEN int;
SELECT @LEN =LEN(ISNULL(@area,''));
IF(@LEN > 1) SELECT @area = LEFT(@area,@LEN - 1);
SELECT @sql = @sql + @area + ' FROM t PIVOT(SUM([QtyCurrent]) FOR [Text] IN(' + @area + ')) as pvt';
--Execute WriteSystemHistory @sql
EXEC (@sql);
Return @@RowCount;
GO
下面是在前台执行的代码：
public const string PROC_NAME_GetForecastCalQueryForSimpleDataArea = "GetForecastCalQueryForSimpleDataArea";
public static System.Data.SqlClient.SqlCommand CreateForecastCalQueryForSimpleDataAreaCommand(string forecastId, string partNumberCustomer)
{
System.Data.SqlClient.SqlCommand cmd = SqlHelper.DataCommandProvider.CreateCommand(PROC_NAME_GetForecastCalQueryForSimpleDataArea, CommandType.StoredProcedure);
cmd.Parameters.Add("@" + ForecastCalOptions.Col_ForecastId, SqlDbType.VarChar, 25).Value = forecastId;
cmd.Parameters.Add("@" + ForecastCalOptions.Col_PartNumberCustomer, SqlDbType.VarChar, 50).Value = partNumberCustomer;
return cmd;
}
...
SqlCommand cmd = ForecastCalUtils.CreateForecastCalQueryForSimpleDataAreaCommand(forecastId, partNumberCustomer);
SqlDataAdapter sda = new SqlDataAdapter(cmd);
DataTable dt = new DataTable("ForecastData");
dt.BeginLoadData();
sda.Fill(dt); //出错
sda.FillSchema(dt, SchemaType.Mapped);
dt.EndLoadData();
怎么回事呢？
