"tp" <t*@discussions.microsoft.com> wrote in message
news:6A61B5FF-75AD-4650-810D-6636925F6DDB@microsoft.com...
> Greetings,
>
> I have a stored procedure that I would like to use. It currently
> gives an error message and I am stumped. Would any one be able to help
> get this procedure to work for me.
>
> CREATE PROCEDURE reports.mp_getuserfpsfigures
>
> @ReportInfoType varchar(10) = 'ALL',
> @ReportBreakdown varchar(10) = 'ALL',
> @ReportType varchar(10) = 'ALL',
> @ReportLevelType varchar(10) = 'ALL',
> @Source varchar(10) = 'ALL',
> @ID varchar(12) = 'ALL',
> @UsageDate varchar(10) = 'ALL',
> @UsageDate2 varchar(10) = 'ALL',
> @FPSId varchar(10) = 'ALL'
>
> AS
> DECLARE @SQLString varchar(100),
>  @SQLString2 varchar(50),
>  @SQLString3 varchar(50),
>  @SQLString4 varchar(50),
>  @SQLString5 varchar(20),
>  @SQLString6 varchar(20),
>  @SQLString7 varchar(20),
>  @SQLString8 varchar(30),
>  @SQLString9 varchar(30),
>  @SQLString10 varchar(300),
>  @AndString varchar(10)
>
>
> SELECT @AndString = ''
> SELECT @SQLString = 'SELECT UserDetails.*,
tbl_SalesManagement.MANAGER_NAME,
> tbl_SalesManagement.VP_NAME, '
>
> SELECT @SQLString2 = 'FPS.FILE_ID, FPS_FILES.FILE_NAME, '
> SELECT @SQLString3 = 'FPS.PRODUCT_ID, FPS_PRODUCTS.PRODUCT_NAME, '
> SELECT @SQLString4 = 'FPS.SERVICE_ID, FPS_SERVICES.SERVICE_NAME, '
> SELECT @SQLString5 = 'FPS.USAGE_DATE, '
> SELECT @SQLString6 = 'FPS.NET '
> SELECT @SQLString7 = 'FPS.[USAGE] '
> SELECT @SQLString8 = 'FPS.NET,FPS.[USAGE] '
> SELECT @SQLString9 = 'INTO fps_figures_tbl '
> SELECT @SQLString10 = 'FROM tbl_SalesManagement RIGHT OUTER JOIN
UserDetails
> ON tbl_SalesManagement.EMPLOYEE_ID = UserDetails.REP1_ID LEFT OUTER JOIN
>                       FPS_SERVICES RIGHT OUTER JOIN FPS ON
> FPS_SERVICES.SERVICE_ID = FPS.SERVICE_ID LEFT OUTER JOIN FPS_PRODUCTS ON
> FPS.PRODUCT_ID = FPS_PRODUCTS.PRODUCT_ID LEFT OUTER JOIN
>                       FPS_FILES ON FPS.FILE_ID = FPS_FILES.FILE_ID ON
> UserDetails.USER_ID = FPS.USER_ID WHERE '
>
>
> /*SELECT String Statements*/
> IF UPPER(@ReportInfoType) = 'File'
>  BEGIN
>  SELECT @SQLString = @SQLString + @AndString + @SQLString2 + ''
>  END
> IF UPPER(@ReportInfoType) = 'Product'
>  BEGIN
>  SELECT @SQLString = @SQLString + @AndString + @SQLString3 + ''
>  END
> IF UPPER(@ReportInfoType) = 'Service'
>  BEGIN
>  SELECT @SQLString = @SQLString + @AndString + @SQLString4 + ''
>  END
>
> IF UPPER(@ReportBreakdown) = 'Month'
>  BEGIN
>  SELECT @SQLString = @SQLString + @AndString + @SQLString5 + ''
>  END
>
> IF UPPER(@ReportType) = 'All'
>  BEGIN
>  SELECT @SQLString = @SQLString + @AndString + @SQLString8 + ''
>  END
> IF UPPER(@ReportType) = 'Revenue'
>  BEGIN
>  SELECT @SQLString = @SQLString + @AndString + @SQLString6 + ''
>  END
> IF UPPER(@ReportType) = 'Usage'
>  BEGIN
>  SELECT @SQLString = @SQLString + @AndString + @SQLString7 + ''
>  END
>
> IF UPPER(@ReportLevelType) <> 'All'
>  BEGIN
>  SELECT @SQLString = @SQLString + @AndString + @SQLString9 + ''
>  END
> IF UPPER(@ReportLevelType) <> 'All'
>  BEGIN
>  SELECT @SQLString = @SQLString + @AndString + @SQLString10 + ''
>  END
>
> /*SELECT Where statements*/
>
>
> IF UPPER(@Source)<>'ALL'
>  BEGIN
>  SELECT @SQLString = @SQLString + @AndString + ' SOURCE_NAME = ' +
> CONVERT(varchar(12),@Source)
>  END
>
> IF UPPER(@ID) <> 'ALL'
>  BEGIN
>  SELECT @SQLString = @SQLString +  ' AND ORIG_USER IN( ' +
> CONVERT(varchar(12),@ID) + ')'
>  END
>
> IF UPPER(@UsageDate) <> 'ALL'
>  BEGIN
>  SELECT @SQLString = @SQLString + @AndString + ' AND USAGE_DATE BETWEEN
'''
> + @UsageDate + '''
>  AND ''' + @UsageDate2 + ''''
>  END
>
> IF UPPER(@ReportInfoType) = 'File'
>  BEGIN
>  SELECT @SQLString = @SQLString + @AndString + ' AND FILE_ID IN(' + @FPSId
+
> ')'''
>  END
>
> IF UPPER(@ReportInfoType) = 'Product'
>  BEGIN
>  SELECT @SQLString = @SQLString + @AndString + ' AND PRODUCT_ID IN(' +
> @FPSId + ')'''
>  END
> IF UPPER(@ReportInfoType) = 'Service'
>  BEGIN
>  SELECT @SQLString = @SQLString + @AndString + ' AND SERVICE_ID IN(' +
> @FPSId + ')'''
>  END
>
>
> /*select @SQLString*/
>
> EXEC(@SQLString)
> GO
>

"Todd Beaulieu" wrote:

> obviously, no one will be able to compile your proc. post the error and the
> exact statement that is failing. You can find that out by commenting
> everything out and executing it while slowly putting code back in.
>
> "tp" <t*@discussions.microsoft.com> wrote in message
> news:6A61B5FF-75AD-4650-810D-6636925F6DDB@microsoft.com...
> > Greetings,
> >
> > I have a stored procedure that I would like to use. It currently
> > gives an error message and I am stumped. Would any one be able to help
> > get this procedure to work for me.
> >
> > CREATE PROCEDURE reports.mp_getuserfpsfigures
> >
> > @ReportInfoType varchar(10) = 'ALL',
> > @ReportBreakdown varchar(10) = 'ALL',
> > @ReportType varchar(10) = 'ALL',
> > @ReportLevelType varchar(10) = 'ALL',
> > @Source varchar(10) = 'ALL',
> > @ID varchar(12) = 'ALL',
> > @UsageDate varchar(10) = 'ALL',
> > @UsageDate2 varchar(10) = 'ALL',
> > @FPSId varchar(10) = 'ALL'
> >
> > AS
> > DECLARE @SQLString varchar(100),
> >  @SQLString2 varchar(50),
> >  @SQLString3 varchar(50),
> >  @SQLString4 varchar(50),
> >  @SQLString5 varchar(20),
> >  @SQLString6 varchar(20),
> >  @SQLString7 varchar(20),
> >  @SQLString8 varchar(30),
> >  @SQLString9 varchar(30),
> >  @SQLString10 varchar(300),
> >  @AndString varchar(10)
> >
> >
> > SELECT @AndString = ''
> > SELECT @SQLString = 'SELECT UserDetails.*,
> tbl_SalesManagement.MANAGER_NAME,
> > tbl_SalesManagement.VP_NAME, '
> >
> > SELECT @SQLString2 = 'FPS.FILE_ID, FPS_FILES.FILE_NAME, '
> > SELECT @SQLString3 = 'FPS.PRODUCT_ID, FPS_PRODUCTS.PRODUCT_NAME, '
> > SELECT @SQLString4 = 'FPS.SERVICE_ID, FPS_SERVICES.SERVICE_NAME, '
> > SELECT @SQLString5 = 'FPS.USAGE_DATE, '
> > SELECT @SQLString6 = 'FPS.NET '
> > SELECT @SQLString7 = 'FPS.[USAGE] '
> > SELECT @SQLString8 = 'FPS.NET,FPS.[USAGE] '
> > SELECT @SQLString9 = 'INTO fps_figures_tbl '
> > SELECT @SQLString10 = 'FROM tbl_SalesManagement RIGHT OUTER JOIN
> UserDetails
> > ON tbl_SalesManagement.EMPLOYEE_ID = UserDetails.REP1_ID LEFT OUTER JOIN
> >                       FPS_SERVICES RIGHT OUTER JOIN FPS ON
> > FPS_SERVICES.SERVICE_ID = FPS.SERVICE_ID LEFT OUTER JOIN FPS_PRODUCTS ON
> > FPS.PRODUCT_ID = FPS_PRODUCTS.PRODUCT_ID LEFT OUTER JOIN
> >                       FPS_FILES ON FPS.FILE_ID = FPS_FILES.FILE_ID ON
> > UserDetails.USER_ID = FPS.USER_ID WHERE '
> >
> >
> > /*SELECT String Statements*/
> > IF UPPER(@ReportInfoType) = 'File'
> >  BEGIN
> >  SELECT @SQLString = @SQLString + @AndString + @SQLString2 + ''
> >  END
> > IF UPPER(@ReportInfoType) = 'Product'
> >  BEGIN
> >  SELECT @SQLString = @SQLString + @AndString + @SQLString3 + ''
> >  END
> > IF UPPER(@ReportInfoType) = 'Service'
> >  BEGIN
> >  SELECT @SQLString = @SQLString + @AndString + @SQLString4 + ''
> >  END
> >
> > IF UPPER(@ReportBreakdown) = 'Month'
> >  BEGIN
> >  SELECT @SQLString = @SQLString + @AndString + @SQLString5 + ''
> >  END
> >
> > IF UPPER(@ReportType) = 'All'
> >  BEGIN
> >  SELECT @SQLString = @SQLString + @AndString + @SQLString8 + ''
> >  END
> > IF UPPER(@ReportType) = 'Revenue'
> >  BEGIN
> >  SELECT @SQLString = @SQLString + @AndString + @SQLString6 + ''
> >  END
> > IF UPPER(@ReportType) = 'Usage'
> >  BEGIN
> >  SELECT @SQLString = @SQLString + @AndString + @SQLString7 + ''
> >  END
> >
> > IF UPPER(@ReportLevelType) <> 'All'
> >  BEGIN
> >  SELECT @SQLString = @SQLString + @AndString + @SQLString9 + ''
> >  END
> > IF UPPER(@ReportLevelType) <> 'All'
> >  BEGIN
> >  SELECT @SQLString = @SQLString + @AndString + @SQLString10 + ''
> >  END
> >
> > /*SELECT Where statements*/
> >
> >
> > IF UPPER(@Source)<>'ALL'
> >  BEGIN
> >  SELECT @SQLString = @SQLString + @AndString + ' SOURCE_NAME = ' +
> > CONVERT(varchar(12),@Source)
> >  END
> >
> > IF UPPER(@ID) <> 'ALL'
> >  BEGIN
> >  SELECT @SQLString = @SQLString +  ' AND ORIG_USER IN( ' +
> > CONVERT(varchar(12),@ID) + ')'
> >  END
> >
> > IF UPPER(@UsageDate) <> 'ALL'
> >  BEGIN
> >  SELECT @SQLString = @SQLString + @AndString + ' AND USAGE_DATE BETWEEN
> '''
> > + @UsageDate + '''
> >  AND ''' + @UsageDate2 + ''''
> >  END
> >
> > IF UPPER(@ReportInfoType) = 'File'
> >  BEGIN
> >  SELECT @SQLString = @SQLString + @AndString + ' AND FILE_ID IN(' + @FPSId
> +
> > ')'''
> >  END
> >
> > IF UPPER(@ReportInfoType) = 'Product'
> >  BEGIN
> >  SELECT @SQLString = @SQLString + @AndString + ' AND PRODUCT_ID IN(' +
> > @FPSId + ')'''
> >  END
> > IF UPPER(@ReportInfoType) = 'Service'
> >  BEGIN
> >  SELECT @SQLString = @SQLString + @AndString + ' AND SERVICE_ID IN(' +
> > @FPSId + ')'''
> >  END
> >
> >
> > /*select @SQLString*/
> >
> > EXEC(@SQLString)
> > GO
> >
>
>
>

"tp" wrote:

> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'INTO'.
>