We help IT Professionals succeed at work.

Help with appending input variable to sql statement

mdward4
mdward4 asked
on
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)
Comment
Watch Question

Jim HornSQL Server Data Dude
CERTIFIED EXPERT
Most Valuable Expert 2013
Author of the Year 2015

Commented:
>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.
Jim HornSQL Server Data Dude
CERTIFIED EXPERT
Most Valuable Expert 2013
Author of the Year 2015

Commented:
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
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
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.

Author

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?
Senior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
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

Author

Commented:
Thanks for your help ScottPletcher!

Explore More ContentExplore courses, solutions, and other research materials related to this topic.