Help with appending input variable to sql statement

Posted on 2012-08-10
Last Modified: 2012-08-20
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)
Question by:mdward4
    LVL 65

    Expert Comment

    by:Jim Horn
    >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.
    LVL 65

    Expert Comment

    by:Jim Horn

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

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

    Expert Comment

    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.
    LVL 1

    Author Comment

    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?
    LVL 68

    Accepted Solution

    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, 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
    LVL 1

    Author Closing Comment

    Thanks for your help ScottPletcher!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
    SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    7 Experts available now in Live!

    Get 1:1 Help Now