Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 404
  • Last Modified:

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)


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)
  • 2
  • 2
  • 2
1 Solution
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.
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:

>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 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.
Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

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?
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:

    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"
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
mdward4Author Commented:
Thanks for your help ScottPletcher!

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now