asked on

Need help with query

Hi Expert,

This is my query in the stored procedure. When I execute it gives me error 'Invalid syntax near AND'. Any idea what is wrong.

Thanks in advance.

DECLARE @FromDate1 nchar(10)=NULL;
                  DECLARE @ToDate1 nchar(10)=NULL;
                  DECLARE @Classification1 nchar(1)=NULL;
                  SET @FromDate1 = @FromDate;
                  SET @ToDate1 = @ToDate;
                  SET @Classification1= @Classification;
                  SET @SQL='SELECT
                   U.[UserID]
                   ,ltrim(rtrim(U.[ClientUserID])) as ClientUserID
                   ,ltrim(rtrim(U.[UserFirstName])) as UserFirstName
                   ,ltrim(rtrim(U.[UserLastName])) as UserLastName
                   ,ltrim(rtrim(U.[UserAdd1])) as UserAdd1
                   ,ltrim(rtrim(U.[UserAdd2])) as UserAdd2
                   ,ltrim(rtrim(U.[UserCity])) as UserCity
                   ,ltrim(rtrim(s.[StateName])) as State
                   ,ltrim(rtrim(U.[UserZip])) as UserZip
                   ,ltrim(rtrim(U.[UserPhone])) as UserPhone
                   ,ltrim(rtrim(U.[UserEmail])) as UserEmail
                   ,ltrim(rtrim(F.StsDescription)) as UserType
                   ,ltrim(rtrim(U.[UserActive])) as UserActive
                   ,ltrim(rtrim(U.[UserStartDate])) as UserStartDate
                   ,ltrim(rtrim(FP.[StsDescription])) as Classification
                   ,ltrim(rtrim(D.[DayName])) as AttendenceDay
                   ,U.[TodaysDate],U.[AttendenceDay],U.[UserState],U.[UserType],U.[Classification]

                   FROM [USERDETAILS] as U

                   LEFT OUTER JOIN DAYMST As D on D.DayID =U.AttendenceDay
                   LEFT OUTER JOIN STATESMST As S on S.StateID =U.UserState
                   LEFT OUTER JOIN FPSCODES as F on F.StsDescription_Code=U.UserType and F.StatusCode =''UT''
                   LEFT OUTER JOIN FPSCODES as FP on FP.StsDescription_Code=U.Classification and FP.StatusCode =''CLF''
                   where U.[UserActive] =''Y'''


              if @FromDate1 <> ''
                  begin
                  SET @SQL = @SQL + ' and CONVERT(DATETIME, RIGHT(UserStartDate, 4) + LEFT(UserStartDate, 4), 112) >=' + CAST(@FromDate1 AS NVARCHAR(10));
                  end

              if @ToDate1 <> ''
                  begin
                   SET @SQL = @SQL + ' and CONVERT(DATETIME, RIGHT(UserStartDate, 4) + LEFT(UserStartDate, 4), 112) <= ' + CAST(@ToDate1 AS NVARCHAR(10));
            end

              if @Classification1 <> ''
                  begin
                        SET @SQL = @SQL + ' and U.[Classification] =' + CAST(@Classification1 AS NVARCHAR(1));
                  end

                  SET @SQL = @SQL + ' order by UserFirstName'
                  EXEC(@SQL)

Jim Horn

It would greatly help if you can place the above in a code block, then execute the query in SSMS, double-click on the error message, watch the cursor jump to the offending line, then tell us what line that is.

Otherwise we're guessing with a lot of code..

Also, throw in a SELECT @SQL and post that in this question. Might make the error obvious.

ASKER CERTIFIED SOLUTION