filter by WHERE has no effect
-
SQL WHERE clause not working trying to filter by date "WHERE [dtmDocDate] >= '2020-09-01'" has no effect
Issue is related to a fairly complex query that includes:
DECLARE @orderByClause nvarchar(MAX) = ''
DECLARE @whereClause nvarchar(MAX)
DECLARE @tableHasIdentity bit
DECLARE @sql nvarchar(MAX)
DECLARE @columnList nvarchar(MAX)
DECLARE @valueList nvarchar(MAX)
DECLARE @Column_ID int
DECLARE @Column_Name varchar(128)
DECLARE @Data_Type varchar(128)
--Initialize variables
SET @whereClause = '[dtmDocDate] >= '2020-09-01''
SET @columnList = ''
SET @valueList = ''
SET @Column_ID = 0
SET @Column_Name = ''
******************* Statement below is not adding the where clause to the built up SQL statement
IF LEN(@whereClause) > 0 --length is 21 per the Watch Window
SET @sql = @sql + ' WHERE ' + @whereClause
- This topic was modified 3 years, 1 month ago by onechipshot.
-
Incorrect syntax?
SET @whereClause = '[dtmDocDate] >= ''2020-09-01'''
_____________
Code for TallyGenerator -
Yes, most definitely.
Since my last post I tried:
SET @whereClause = '[dtmDocDate] >= DATEPART(dy, 2020-09-01)'
with the same result.
However, I think I have discovered the underlying issue. The script of which this is a small part builds an INSERT script where 31 column names and there related values are concatenated into one insert statement per row with the filter clause (WHERE.....) added to the end. Using the immediate window I discovered that the script only made it to column number 16 and no WHERE clause.
Thank you so much for your initial response. Diving back into the script and will follow up here if I narrow down the issue.
- onechipshot wrote:
Yes, most definitely.
Since my last post I tried:
SET @whereClause = '[dtmDocDate] >= DATEPART(dy, 2020-09-01)'
with the same result.
However, I think I have discovered the underlying issue. The script of which this is a small part builds an INSERT script where 31 column names and there related values are concatenated into one insert statement per row with the filter clause (WHERE.....) added to the end. Using the immediate window I discovered that the script only made it to column number 16 and no WHERE clause.
Thank you so much for your initial response. Diving back into the script and will follow up here if I narrow down the issue.
Your new version of @WhereClause is also incorrect.
Try to check it with
print @whereclause
And then compare the outcomes of following queries:
select DATEPART(dy, 2020-09-01)
select DATEPART(dy, '2020-09-01')_____________
Code for TallyGenerator -
SET @whereClause = [dtmDocDate] >= DATEPART(dy, '2020-09-01')
AND
SET @whereClause = [dtmDocDate] >= DATEPART(dy, 2020-09-01)
both receive errors on execution
Also tries with 's on beg and end of statement as the whole statement is being concentrated below
@whereClause is called by:
IF LEN(@whereClause) > 0
SET @sql = @sql + ' WHERE ' + @whereClause
As the length of the "@SQL" variable exceeds the character limits of PRINT AND ?, what command can be used to copy out the complete SQL script contained in @SQL? That way I can add the WHERE clause directly to the SQL window and run it there.
- onechipshot wrote:
SET @whereClause = [dtmDocDate] >= DATEPART(dy, '2020-09-01')
AND
SET @whereClause = [dtmDocDate] >= DATEPART(dy, 2020-09-01)
both receive errors on execution
You obviously have problems with handling quotes in dynamic SQL.
Before you try execution you have to check your script and make sure it's made up correctly.
'2020-09-01' in that formula is the date Sep 01, 2020, when
2020-09-01 is July 4 1905
As the length of the "@SQL" variable exceeds the character limits of PRINT AND ?, what command can be used to copy out the complete SQL script contained in @SQL?
You can use "divide and conquer" approach:
print substring (@whereClause, 1, 4000)
print substring (@whereClause, 4001, 8000)
print substring (@whereClause, 8001, 12000)
..._____________
Code for TallyGenerator -
Try SELECT too rather than PRINT, since SELECT can show more chars than PRINT. Also, be sure to adjust the options in SSMS to show the max chars possible.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
-
Thank all for your comments and ideas. Came up with this after reading a post to codeplex on escaping 's. Turns out the magic use of "'"s is.
'[dtmDocDate] >= CONVERT(datetime, ''2020-09-01'')'
Which worked perfectly. This enabled me to use current information from 45,000 rows of data instead of 2,000,000 rows to create inserts to a sandbox database. This was a critical part of a script used to create the INSERT statements.
As to the suggestions:
print substring (@whereClause, 1, 4000)
print substring (@whereClause, 4001, 8000)
print substring (@whereClause, 8001, 12000)
and
SELECT @SQL [processing-instruction(SQL)] FOR XML PATH(''), TYPE;
Working in the immediate window I received the error:
"could not be evaluated"
But the ideas did look promising.
Issue resolved.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply