We help IT Professionals succeed at work.
Get Started

Help with appending input variable to sql statement

mdward4
mdward4 asked
on
423 Views
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)

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
Senior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
This problem has been solved!
Unlock 1 Answer and 6 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE