在程序中及查询分析器中都出现的奇怪问题:存储过程参数值被截断

Question

需要执行的语句
exec Tab_Class_GetList @WhereCondition = N'Tab_ClassTypeNo in (''da73298b-24d4-4f5a-ba7f-e77455c1a861'',''0bf3583b-5cfd-4c60-833a-fe25494cf3e5'',''f89d0270-11aa-4486-92ed-a8512a24508f'',''3e25eead-6c34-4723-ad62-4987fbb9ca61'',''128c6989-b4cc-4901-8dbf-b7e2ae5f2717'',''2fce3436-32f6-4bc0-b1fd-95a4f7a47ec3'',''cf34b626-1b42-4f46-965b-9615aa152786'',''00d96b3d-0862-4bda-b724-76464b1bdd78'',''cd82d97a-78f0-42f8-bf92-7f9829c798f3'',''8fe55929-92b2-465a-bfb0-833cecbfdd43'',''968b9f79-d023-414c-b29a-6396bb8e499c'',''1e8c6c38-d5f8-41e8-a9ed-d42c1cd2cac4'',''7af1b561-bd33-40f1-b12b-d3b5b7ac69f1'',''899ad182-c3e7-4ee4-9275-f1a55f6b9220'',''584854e4-6fd5-46e1-9d8f-a19f8400e69a'',''18da7403-b66a-4278-9fd8-3025aba9a38b'',''edcc79dd-915c-41ce-a0ce-3132d24f1410'',''ab8c0bcd-e04c-413d-898d-3d496b75242e'',''36e38e2f-b5e3-48f5-8506-3e225e897852'',''38904a69-9b53-44dc-a3a6-510386d58723'',''1a806bc0-e323-4a5e-b72d-628fb8babe4d'',''5f947a74-fc4b-413f-b97f-0b142a6ae92f'',''aded7f2c-e047-4012-802b-8a9ce1dc42db'',''0bffcf64-a130-4510-b7a0-fe89f57bf683'',''c53ff20e-7a34-4384-b1ea-d868f0fe783c'',''cbc13d1f-3fa7-4b7e-9b19-d8
8a3baf83e0'',''c18b49e5-1e54-4d6b-96a1-be67c1448617'')', @OrderBy = N''
在程序中及查询分析器中执行都会提示
Unclosed quotation  before the character string '7af1b561-'.
使用Sql Profiler发现执行的语句是这样的
exec InfoCompany_DynamicGetList @WhereCondition = N'InfoCompanyTypeNo in (''da73298b-24d4-4f5a-ba7f-e77455c1a861'',''0bf3583b-5cfd-4c60-833a-fe25494cf3e5'',''f89d0270-11aa-4486-92ed-a8512a24508f'',''3e25eead-6c34-4723-ad62-4987fbb9ca61'',''128c6989-b4cc-4901-8dbf-b7e2ae5f2717'',''2fce3436-32f6-4bc0-b1fd-95a4f7a47ec3'',''cf34b626-1b42-4f46-965b-9615aa152786'',''00d96b3d-0862-4bda-b724-76464b1bdd78'',''cd82d97a-78f0-42f8-bf92-7f9829c798f3'',''8fe55929-92b2-465a-bfb0-833cecbfdd43'',''968b9f79-d023-414c-b29a-6396bb8e499c'',''1e8c6c38-d5f8-41e8-a9ed-d42c1cd2cac4'',''7af1b561-', @OrderByExpression = N''
第一个参数的值被截断.
请问要在怎么解决.已经爬过网.没找到相应的资料.

Answer 1

存储过程如下:CREATE PROCEDURE [dbo].[Tab_Class_GetList]
@WhereCondition nvarchar(4000),
@OrderBy nvarchar(250) = NULL
ASSET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTEDDECLARE @SQL nvarchar(3250)SET @SQL = '
SELECT
...省略....
FROM
[dbo].[Tab_Class]
WHERE
' + @WhereConditionIF @OrderBy IS NOT NULL AND LEN(@OrderBy) > 0
BEGIN
SET @SQL = @SQL + '
ORDER BY
' + @OrderBy
ENDEXEC sp_executesql @SQL

Answer 2

刚才自己做了些测试 感觉是你存储过程里面的语句有错
alter PROCEDURE [dbo].[Tab_Class_GetList]
    @WhereCondition nvarchar(4000),
    @OrderBy nvarchar(250) = NULL
ASSET NOCOUNT ON
--SET TRANSACTION ISOLATION LEVEL READ COMMITTEDDECLARE @SQL nvarchar(3250)SET @SQL = '
SELECT * from sysobjects where ' + @WhereConditionIF @OrderBy IS NOT NULL AND LEN(@OrderBy) > 0
BEGIN
    SET @SQL = @SQL + '
ORDER BY
    ' + @OrderBy
END
EXEC sp_executesql @SQL
exec Tab_Class_GetList @WhereCondition = N'name in (''da73298b-24d4-4f5a-ba7f-e77455c1a861'',''0bf3583b-5cfd-4c60-833a-fe25494cf3e5'',''f89d0270-11aa-4486-92ed-a8512a24508f'',''3e25eead-6c34-4723-ad62-4987fbb9ca61'',''128c6989-b4cc-4901-8dbf-b7e2ae5f2717'',''2fce3436-32f6-4bc0-b1fd-95a4f7a47ec3'',''cf34b626-1b42-4f46-965b-9615aa152786'',''00d96b3d-0862-4bda-b724-76464b1bdd78'',''cd82d97a-78f0-42f8-bf92-7f9829c798f3'',''8fe55929-92b2-465a-bfb0-833cecbfdd43'',''968b9f79-d023-414c-b29a-6396bb8e499c'',''1e8c6c38-d5f8-41e8-a9ed-d42c1cd2cac4'',''7af1b561-bd33-40f1-b12b-d3b5b7ac69f1'',''899ad182-c3e7-4ee4-9275-f1a55f6b9220'',''584854e4-6fd5-46e1-9d8f-a19f8400e69a'',''18da7403-b66a-4278-9fd8-3025aba9a38b'',''edcc79dd-915c-41ce-a0ce-3132d24f1410'',''ab8c0bcd-e04c-413d-898d-3d496b75242e'',''36e38e2f-b5e3-48f5-8506-3e225e897852'',''38904a69-9b53-44dc-a3a6-510386d58723'',''1a806bc0-e323-4a5e-b72d-628fb8babe4d'',''5f947a74-fc4b-413f-b97f-0b142a6ae92f'',''aded7f2c-e047-4012-802b-8a9ce1dc42db'',''0bffcf64-a130-4510-b7a0-fe89f57bf683'',''c53ff20e-7a34-4384-b1ea-d868f0fe783c'',''cbc13d1f-3fa7-4b7e-9b19-d8 
8a3baf83e0'',''c18b49e5-1e54-4d6b-96a1-be67c1448617'')', @OrderBy = N'' 我这个测试没问题

Answer 3

忘记说了.我的是MS-SQL 2000

Answer 4

不好意思.自己猪头了..  原来的参数 是      @WhereCondition nvarchar(500),  修改存储过程的脚本已经写了.不过没执行..哈哈..所以数据库中的存储过程 还是原来的..