Convert Problem
-
This code fails when using Char(39). Does anyone know why? Is there a workaround?
EXEC (
'INSERT INTO #searchResult SELECT COUNT(' + @pkFieldName + ')
FROM dbo. ' + @tableName + '
WHERE (' + @fieldName + ' IS NOT NULL)
AND (CRTD_DT >= CONVERT(DATETIME, ' + CHAR(39) + '2005-07-11 00:00:00' + CHAR(39) + ' , 102))
HAVING (COUNT(' + @fieldName + ') = 0)'
)
When the snows fall and the white winds blow,The lone wolf dies but the pack survives.
Once you've accepted your flaws, no one can use them against you.
-
1. What is the error?
2. Why are you trying to convert 2005-07-11 00:00:00 to DateTime WHEN you are going to get the exact same information?
Good Hunting!AJ Ahrens
webmaster@kritter.net -
answers:
1. What is the error? Incorrect syntax near 'CHAR'
2. Why are you trying to convert 2005-07-11 00:00:00 to DateTime WHEN you are going to get the exact same information? To use it as a constraint in a query.
When the snows fall and the white winds blow,The lone wolf dies but the pack survives.
Once you've accepted your flaws, no one can use them against you.
-
1. Try parsing the string together into a variable and then PRINT the variable. SHOULD help ID the problem
2. SQL server is aware enough to realize that '2005-09-01 00:00:00.000' regardless of CAST|CONVERT to DATETIME is a DATETIME and can be used AS IS. Don't see that you will get MUCH (IF any) bang for that CONVERT...
Good Hunting!AJ Ahrens
webmaster@kritter.net -
Converting the char values to literals solved the problem.
This is not my code, so I don't know why datetime was used.
Thanks for your help.
When the snows fall and the white winds blow,The lone wolf dies but the pack survives.
Once you've accepted your flaws, no one can use them against you.
-
FWIW
If you are going to play with dynamic SQL, it would be best to do as the previous poster suggested and load the dynamically generated SQL string into a variable and then use exec() with that variable.
Such as the following:
declare @SQL varchar(8000),
@pkFieldName varchar(128),
@tableName varchar(128),
@fieldName varchar(128)
select @pkFieldName = 'PK_PrimeKey'
select @tableName = 'Primetable'
select @fieldName = 'PrimeTableColumn'
select @SQL = 'INSERT INTO #searchResult SELECT COUNT(' + @pkFieldName + ')' + char(10) +
'FROM dbo. ' + @tableName + char(10) +
'WHERE (' + @fieldName + ' IS NOT NULL) and' + char(10) +
' (CRTD_DT >= CONVERT(DATETIME, ' + CHAR(39) + '2005-07-11 00:00:00' + CHAR(39) + ' , 102))' + char(10) +
'HAVING (COUNT(' + @fieldName + ') = 0)' + char(10)
print @SQL
--EXEC (@SQL)
The advantage of this method is that the print statement will show the dynamically generated SQL statement as the exec() statement will see it. In this case it will look like this:
INSERT INTO #searchResult SELECT COUNT(PK_PrimeKey)
FROM dbo. Primetable
WHERE (PrimeTableColumn IS NOT NULL) and
(CRTD_DT >= CONVERT(DATETIME, '2005-07-11 00:00:00' , 102))
HAVING (COUNT(PrimeTableColumn) = 0)
Once you are satisfied with the SQL statement that was rendered, then you can uncomment the exec() command and let the fun begin!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply