Help with appending input variable to sql statement

I have following sproc that is obtaining Start Date and End date in mm/dd/yyyy format from user input via crystal report.  I need to append this to query as WHERE clause but am getting this error "Invalid column name 'SQL4'."

ALTER PROCEDURE [dbo].[mtwd_ETAPDetailReport_test]
@BeginDate as varchar(10),
@EndDate as varchar(10)

as


DECLARE @vbegdate datetime
DECLARE @venddate datetime
DECLARE @SQL varchar(8000)
DECLARE @SQL2 varchar(8000)
DECLARE @SQL3 varchar(8000)
DECLARE @SQL4 varchar(8000)
DECLARE @sCriteria varchar(8000)


Set @vbegdate = @vbegdate + ' 00:00:00.000'
Set @venddate = @venddate +  ' 23:59:59.997'

SET @vbegdate=CONVERT(datetime,@BeginDate,102)
SET @venddate=CONVERT(datetime,@EndDate,102)
SET @SQL2 = 'ETStock.AssignmentDateTime between '
SET @SQL3 = ' and '
SET @SQL4 = ' AND (ETStock.ETapStatus NOT IN (15, 16))'

SET @sCriteria = @SQL2 + @vbegdate + @SQL3 + @venddate + SQL4

select @SQL = 'SELECT  distinct   ETStock.StockID as StockID, ETStock.ClaimNumber, t1.catastrophecode as CatCode, t1.YardName, t1.enteredtimestamp as assignmentdate, t1.ReleaseDate, t1.PoolArrivalDate,
ISNULL(dbo.dw_fnBusinessDaysBetweenDates(t1.ReleaseDate, t1.PoolArrivalDate, 1, 0), 0) AS DaysReleaseToArrival, TTFDate.TitleTransmittalDate as DateDocumentsScanned,
ISNULL(ETDates.FirstOwnerContact,ETDates.DocumentsMailedtoOwner) as DateFirstAttemptOwnerContact, ISNULL(dbo.dw_fnBusinessDaysBetweenDates(TTFDate.TitleTransmittalDate,ETDates.FirstOwnerContact,  1, 0), 0) AS DaysReleaseToContact,
ISNULL(ETDates.OwnerContactMade,ETDates.DocumentsMailedtoOwner) as DateFirstOwnerContact,  ISNULL(dbo.dw_fnBusinessDaysBetweenDates(ETDates.FirstOwnerContact, ETDates.OwnerContactMade, 1, 0), 0) AS DaysFirstAttemptToContact,
ISNULL(ETDates.DocumentsMailedtoOwner, ETDates.OwnerContactMade) as DateDocumentsMailedToOwner,
ISNULL(dbo.dw_fnBusinessDaysBetweenDates(ETDates.OwnerContactMade, ETDates.DocumentsMailedtoOwner, 1, 0), 0) AS DaysFromContactToMail, ISNULL(ETDates.DocumentsReceivedOwner, ETDates.AllDocumentsScanned) as DateDocumentsReceivedByOwner,
ISNULL(dbo.dw_fnBusinessDaysBetweenDates(ISNULL(ETDates.DocumentsMailedtoOwner,ETDates.OwnerContactMade),ETDates.DocumentsReceivedOwner, 1, 0), 0) AS DaysFromMailedToReceive, ETDates.AllDocumentsVerified as DateDocumentsVerified,
ISNULL(dbo.dw_fnBusinessDaysBetweenDates(ETDates.DocumentsReceivedOwner, ETDates.AllDocumentsVerified, 1, 0), 0) AS DaysFromReceivedToVerify, t1.AppliedForDate as DateTitleAppliedFor, ISNULL(dbo.dw_fnBusinessDaysBetweenDates(ETDates.AllDocumentsVerified, t1.AppliedForDate, 1, 0), 0) AS DaysFromVerifiedToApply,
t1.NewTitleReceivedDate as DateTitleBack, ISNULL(dbo.dw_fnBusinessDaysBetweenDates(t1.AppliedForDate,t1.NewTitleReceivedDate, 1, 0), 0) AS DaysFromSentToReceive,
t1.TitleApprovalDate as DateTitleApprovaltoSell, ISNULL(dbo.dw_fnBusinessDaysBetweenDates(t1.NewTitleReceivedDate, t1.TitleApprovalDate, 1, 0), 0) AS DaysFromTitleBack,
ETDates.SaleDate as DateSaleDate, ISNULL(dbo.dw_fnBusinessDaysBetweenDates(ETDates.ApprovedForSale, ETDates.SaleDate, 1, 0), 0) AS DaysFromApprovalToSale,
ETDates.ProceedsDate as DateProceedsDate, ISNULL(dbo.dw_fnBusinessDaysBetweenDates(ETDates.SaleDate, ETDates.ProceedsDate, 1, 0), 0) AS DaysFromSaleToProceeds,
ISNULL(dbo.dw_fnBusinessDaysBetweenDates(ETDates.AssignmentDate, ETDates.FirstOwnerContact, 1, 0), 0) AS DaysFromAssignToAttempt, ETStock.etapstatus
FROM stockassignments AS t1 INNER JOIN dw_ETapStock AS ETStock ON t1.Stockid = ETStock.LotNumber AND t1.NationalYardID = ETStock.NationalYardId
LEFT OUTER JOIN vw_EtapEventDates AS ETDates ON ETStock.StockID = ETDates.StockID LEFT OUTER JOIN AimsNet_rfCatastrophe ON t1.CatastropheCode = AimsNet_rfCatastrophe.CatastropheCode
LEFT OUTER JOIN vw_ETAPScanDate AS TTFDate ON t1.Stockid = TTFDate.stockid WHERE ' + @sCriteria

print @sql
exec (@SQL)
LVL 1
mdward4Asked:
Who is Participating?
 
Scott PletcherSenior DBACommented:
Alright, let's clean this whole thing up :-).

You need to get rid of time and just compare by date.  For example, if  you want all of July 2012, you code it this way:

WHERE
    datetime_column >= '20120701' AND datetime_column < '20120801'

That is, go one day past the desired ending day and use less than.

Then, NO time issues and NO date format issues! ('yyyymmdd' will *ALWAYS* work in SQL Server).

[Btw, you have a contradiction:
"is obtaining Start Date and End date in mm/dd/yyyy format"
...
CONVERT(datetime,@BeginDate,102)
Those two don't match: format 102 is yyyy.mm.dd, not mm/dd/yyyy.

I'll assume for the code below that the format really is 102 -- you can change the format code to 101 if it really is mm/dd/yyyy.]



--DECLARE @vbegdate datetime --<<-- remove this
--DECLARE @venddate datetime --<<-- remove this
--Set @vbegdate = @vbegdate + ' 00:00:00.000' --<<-- remove this
--Set @venddate = @venddate +  ' 23:59:59.997' --<<-- remove this
--SET @vbegdate=CONVERT(datetime,@BeginDate,102) --<<-- remove this
--SET @venddate=CONVERT(datetime,@EndDate,102) --<<-- remove this

SET @SQL2 = 'ETStock.AssignmentDateTime >= ''' + CONVERT(char(8), CONVERT(datetime, @BeginDate, 102), 112) + ''''
SET @SQL2 = ' AND ETStock.AssignmentDateTime < ''' + CONVERT(char(8), DATEADD(DAY, 1, CONVERT(datetime, @EndDate, 102)), 112) + '''' --NOTE: one day added to @EndDate
SET @SQL4 = ' AND (ETStock.ETapStatus NOT IN (15, 16))'

SET @sCriteria = @SQL2 + @SQL3 + @SQL4
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>SET @sCriteria = @SQL2 + @vbegdate + @SQL3 + @venddate + SQL4

<wild guess>
Since all your @SQL variables are varchar(8000), adding three of them up would require 24000 characters, unless you CAST them to be 8000 total, such as...

SET @sCriteria = CAST(@SQL2 + @vbegdate + @SQL3 + @venddate + SQL4 as varcahr(8000))

<another wild guess>
Eyeball the 'print @sql' where you see SQL4, and make sure there are spaces everywhere their should be spaces.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
HOLD EVERYTHING

>SET @sCriteria = @SQL2 + @vbegdate + @SQL3 + @venddate + SQL4
You are missing the @ in front of SQL4, should be ...

SET @sCriteria = @SQL2 + @vbegdate + @SQL3 + @venddate + @SQL4
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Scott PletcherSenior DBACommented:
You also need to put quotes around the date values in the criteria (date literals/constants must be enclosed in quotes in SQL Server):

SET @sCriteria = @SQL2 + ' ''' + @vbegdate + ''' ' + @SQL3 + ' ''' + @venddate + ' '' ' + @SQL4

Also, typically trailing spaces may not always be retained in varchar variables in SQL, so don't rely on them for spacing/delimiting -- use leading spaces in variables instead (best), or add spaces when concatenating the variables, as demonstrated above.
0
 
mdward4Author Commented:
Scotts suggestion fixed the original issue, am now getting this error:

Msg 241, Level 16, State 1, Procedure mtwd_ETAPDetailReport_test, Line 28
Conversion failed when converting datetime from character string.

Any idea?
0
 
mdward4Author Commented:
Thanks for your help ScottPletcher!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.