Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Stored procedure Conversion failed when converting date and/or time from character string  [new]
Alexjinni
Member

Откуда:
Сообщений: 5
Добрый день

Есть SP которая выдает ошибку : Conversion failed when converting date and/or time from character string при вызове ее с определенными параметрами.
НО когда тело этой SP вытащить и запустить в новом окне, предварительно обьявив те же самые параметры- все работает нормально.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER PROCEDURE [dbo].[RecordGetList_All2]
@UserID int,
@State int,
@FormID int,
@OnlyMyData int
AS
BEGIN
SET NOCOUNT ON;

DECLARE @SQL nvarchar(max);
DECLARE @TMP nvarchar(max);

SELECT @SQL = CASE WHEN @SQL IS NULL THEN '' ELSE @SQL +',' END +
'['+ REPLACE(FieldName, '''', '''''') +']'
FROM ( SELECT DISTINCT ([FieldName]), MAX([FieldType]) AS FieldType
FROM [dbo].[RecordItems]
WHERE RecordId IN (SELECT RecordId FROM Records WHERE FormID=@FormId)
GROUP BY FieldName) T;

IF @SQL = '' OR @SQL IS NULL
BEGIN
RETURN;
END;


CREATE TABLE #TempTable
(
[RecordId] uniqueidentifier,
[Version] int
);

SELECT @TMP = CASE WHEN @TMP IS NULL THEN '' ELSE @TMP +'; ' END +
'ALTER TABLE #TempTable ADD ['+ [FieldName] +'] ' +
CASE
WHEN FieldType = 'Date' THEN ' datetime '
WHEN FieldType = 'Decimal' THEN ' decimal '
ELSE ' nvarchar(max) '
END
+ ' NULL '
FROM ( SELECT DISTINCT ([FieldName]), MAX([FieldType]) AS FieldType
FROM [dbo].[RecordItems]
WHERE RecordId IN (SELECT RecordId FROM Records WHERE FormID=@FormId)
GROUP BY FieldName) T;

EXEC (@TMP);

EXEC('INSERT INTO #TempTable
SELECT FF.*
FROM (
SELECT RecordId, Version, ' + @SQL + ' FROM(
SELECT
RecordItems.RecordId,
RecordItems.FieldName,
RecordItems.FieldValue,
RecordItems.Version
FROM [RecordItems]
INNER JOIN (SELECT MAX(Version) AS Version, RecordItems.RecordId
FROM RecordItems
WHERE RecordItems.RecordId IN (SELECT RecordId FROM Records
WHERE Records.FormID='+@FormID+')
AND RecordId IN (SELECT RecordData.RecordId FROM RecordData
WHERE RecordData.Stage=0 AND RecordData.RecordId=RecordId)
GROUP BY RecordItems.RecordId) AS MaxVersion
ON MaxVersion.RecordId=RecordItems.RecordId AND
MaxVersion.Version=RecordItems.Version
) AS T
PIVOT (MAX([FieldValue]) FOR [FieldName] IN (' + @SQL + ')) AS PVT
) AS FF
');

---------------------------------------------
-- Update MoneyFields
---
-- change data on fields
DECLARE @TMP_0 NVARCHAR(max);
DECLARE @TMP_1 NVARCHAR(max);

SELECT @TMP_0 = 'UPDATE #TempTable SET ';
SELECT @TMP_1 = CASE WHEN @TMP_1 IS NULL THEN '' ELSE @TMP_1 +', ' END +
'['+ [FieldName] +']=CASE WHEN CharIndex('' '', ['+
[FieldName] +']) > 0 THEN ltrim(rtrim(left(['+ [FieldName] +'],
CharIndex('' '', ['+ [FieldName] +']) - 1))) ELSE ['+
[FieldName] +'] END '
FROM ( SELECT DISTINCT ([FieldName]), MAX([FieldType]) AS FieldType
FROM [dbo].[RecordItems]
WHERE RecordId IN (SELECT RecordId FROM Records WHERE FormID=@FormId)
AND [FieldType] = 'MoneyField'
GROUP BY FieldName) T;
IF @TMP_1 <> ''
BEGIN
EXEC (@TMP_0 + @TMP_1);
END;

-- ALTER TABLE MyTable ALTER COLUMN MyColumn NVARCHAR(20)
SELECT @TMP_0 = NULL;
SELECT @TMP_0 = CASE WHEN @TMP_0 IS NULL THEN '' ELSE @TMP_0 +'; ' END +
'ALTER TABLE #TempTable ALTER COLUMN ['+ [FieldName] +'] money '
FROM ( SELECT DISTINCT ([FieldName]), MAX([FieldType]) AS FieldType
FROM [dbo].[RecordItems]
WHERE RecordId IN (SELECT RecordId FROM Records WHERE FormID=@FormId)
AND [FieldType] = 'MoneyField'
GROUP BY FieldName) T;
EXEC (@TMP_0);

SELECT F.*, R.State, UsersFormRights.AccessLevel,
[dbo].[FormatRecordNumber](@FormID, R2.RecordNr, R.Version) AS
RecordNumber,
Creators.[Name] as [CreatorName],
Updaters.[Name] as [UpdaterName],
RecordStates.[Title] as [StateTitle],
R.MarketingAccess,
Forms.Title FormTitle
FROM Records AS R2
LEFT JOIN RecordData AS R ON R.RecordId=R2.RecordId
LEFT JOIN UsersFormRights ON UsersFormRights.FormID = R2.FormID and
UsersFormRights.UserID=@UserID
INNER JOIN RecordStates on RecordStates.State = R.State
LEFT JOIN Users as Creators on Creators.id = R2.[CreatorId]
LEFT JOIN Users as Updaters on Updaters.id = R.[UpdaterId]
LEFT JOIN #TempTable AS F ON F.RecordId=R.RecordId AND F.Version = R.Version
Left JOin Forms on Forms.ID = R2.FormId
WHERE
R.Version = (SELECT MAX(Version) FROM RecordData WHERE
RecordData.RecordId=R.RecordId) AND
R2.FormID=@FormID AND (R.[State]=@State OR @State = -1)
AND (((CreatorId=@UserID) OR (R.UpdaterId=@UserID))
OR ((@OnlyMyData=1 AND UsersFormRights.AccessLevel<=1) OR
(@OnlyMyData=0 AND UsersFormRights.AccessLevel>1)))

DROP TABLE #TempTable;
END
2 окт 12, 21:16    [13258392]     Ответить | Цитировать Сообщить модератору
 Re: Stored procedure Conversion failed when converting date and/or time from character string  [new]
Glory
Member

Откуда:
Сообщений: 104751
И как узнать, какая из команд вашей процедуры генерирует ошибку ?
2 окт 12, 21:31    [13258429]     Ответить | Цитировать Сообщить модератору
 Re: Stored procedure Conversion failed when converting date and/or time from character string  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31965
Alexjinni
НО когда тело этой SP вытащить и запустить в новом окне, предварительно обьявив те же самые параметры- все работает нормально.
Что такое "тело этой SP вытащить и запустить"? Это вы про этот скрипт создания процедуры? Ну да, с чего это она не будет создаваться?

Ещё оформляйте тексты в теге SRC и не фантазируйте насчёт ошибок, просто копируйте их сюда.
2 окт 12, 21:40    [13258460]     Ответить | Цитировать Сообщить модератору
 Re: Stored procedure Conversion failed when converting date and/or time from character string  [new]
Alexjinni
Member

Откуда:
Сообщений: 5
SELECT @TMP = CASE WHEN @TMP IS NULL THEN '' ELSE @TMP +'; ' END +
'ALTER TABLE #TempTable ADD ['+ [FieldName] +'] ' +
CASE
WHEN FieldType = 'Date' THEN ' datetime '
WHEN FieldType = 'Decimal' THEN ' decimal '
ELSE ' nvarchar(max) '
END
+ ' NULL '
FROM ( SELECT DISTINCT ([FieldName]), MAX([FieldType]) AS FieldType
FROM [dbo].[RecordItems]
WHERE RecordId IN (SELECT RecordId FROM Records WHERE FormID=@FormId)
GROUP BY FieldName) T;

EXEC (@TMP);

EXEC('INSERT INTO #TempTable
SELECT FF.*
FROM (
SELECT RecordId, Version, ' + @SQL + ' FROM(
SELECT
RecordItems.RecordId,
RecordItems.FieldName,
RecordItems.FieldValue,
RecordItems.Version
FROM [RecordItems]
INNER JOIN (SELECT MAX(Version) AS Version, RecordItems.RecordId
FROM RecordItems
WHERE RecordItems.RecordId IN (SELECT RecordId FROM Records
WHERE Records.FormID='+@FormID+')
AND RecordId IN (SELECT RecordData.RecordId FROM RecordData
WHERE RecordData.Stage=0 AND RecordData.RecordId=RecordId)
GROUP BY RecordItems.RecordId) AS MaxVersion
ON MaxVersion.RecordId=RecordItems.RecordId AND
MaxVersion.Version=RecordItems.Version
) AS T
PIVOT (MAX([FieldValue]) FOR [FieldName] IN (' + @SQL + ')) AS PVT
) AS FF
');



В этом коде вытаскиваются значения из recordItems и потом делается PIVOT в темп таблицу.
Групируются данные по [FieldType] из RecordItems и если [FieldType]='Date' то записывается значение в колонку с типом datetime

Данные из RecordItems для которых [FieldType]='Date', я проверил с помощью функции ISDATE : тма либо даты либо пустые строки.
2 окт 12, 21:40    [13258461]     Ответить | Цитировать Сообщить модератору
 Re: Stored procedure Conversion failed when converting date and/or time from character string  [new]
Alexjinni
Member

Откуда:
Сообщений: 5
alexeyvg
Alexjinni
НО когда тело этой SP вытащить и запустить в новом окне, предварительно обьявив те же самые параметры- все работает нормально.
Что такое "тело этой SP вытащить и запустить"? Это вы про этот скрипт создания процедуры? Ну да, с чего это она не будет создаваться?

Ещё оформляйте тексты в теге SRC и не фантазируйте насчёт ошибок, просто копируйте их сюда.


Нет я имею ввиду что я вытащил весь текст между BEGIN и END и запустил его в новом окне подставив теже значения вместо параметров.
2 окт 12, 21:43    [13258472]     Ответить | Цитировать Сообщить модератору
 Re: Stored procedure Conversion failed when converting date and/or time from character string  [new]
Glory
Member

Откуда:
Сообщений: 104751
Alexjinni
Данные из RecordItems для которых [FieldType]='Date', я проверил с помощью функции ISDATE : тма либо даты либо пустые строки.

Т.е. вы признаете, что храните в текстовом поле нечто, что должно быть датой ?
И что серверу таки приходится "converting date and/or time from character string " ?
2 окт 12, 21:48    [13258497]     Ответить | Цитировать Сообщить модератору
 Re: Stored procedure Conversion failed when converting date and/or time from character string  [new]
Alexjinni
Member

Откуда:
Сообщений: 5
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.
2 окт 12, 21:51    [13258508]     Ответить | Цитировать Сообщить модератору
 Re: Stored procedure Conversion failed when converting date and/or time from character string  [new]
Alexjinni
Member

Откуда:
Сообщений: 5
Glory
Alexjinni
Данные из RecordItems для которых [FieldType]='Date', я проверил с помощью функции ISDATE : тма либо даты либо пустые строки.

Т.е. вы признаете, что храните в текстовом поле нечто, что должно быть датой ?
И что серверу таки приходится "converting date and/or time from character string " ?


Да так и есть.
Но вопрос почему, если запустить весь запрос в новом окне, то все нормально форматируется?
И ISDATE показывает что значения в таблице valid.
2 окт 12, 21:53    [13258517]     Ответить | Цитировать Сообщить модератору
 Re: Stored procedure Conversion failed when converting date and/or time from character string  [new]
Glory
Member

Откуда:
Сообщений: 104751
Alexjinni
Но вопрос почему, если запустить весь запрос в новом окне, то все нормально форматируется?
И ISDATE показывает что значения в таблице valid.

Вы сначала определите текст той команды(в единственном числе), которая генерирует ошибку.
А потом уже делайте выводы, из какого поля и в каком виде происходит конвертация.
2 окт 12, 21:57    [13258533]     Ответить | Цитировать Сообщить модератору
 Re: Stored procedure Conversion failed when converting date and/or time from character string  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
Alexjinni
В этом коде вытаскиваются значения из recordItems и потом делается PIVOT в темп таблицу.
Групируются данные по [FieldType] из RecordItems и если [FieldType]='Date' то записывается значение в колонку с типом datetime

Данные из RecordItems для которых [FieldType]='Date', я проверил с помощью функции ISDATE : тма либо даты либо пустые строки.
Я не пойму, вы хотите чтобы мы за вас телепатически нашли ошибку в динамически сгенерированном коде?

Код в ХП и без нее может по разному выполняться с тем же конечным результатом, скажем из-за parameter sniffing. И ваши проверки происходят раньше/позже конвертации. Поэтому тут падает, а тут не падает.
2 окт 12, 22:16    [13258597]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить