sql create table with stored procedure with optional parameters

[Bittu]

I am writing a report based on parameters, utilizing a sql stored procedure. Problem is there are several 'optional' parameters, where the user can choose a value, or ignore that parameter altogether. I need to use the stored proc as a dataset in asp.net report. Only way I thought of doing it is to create a table from the results. But I'm clueless.How do I turn this into a 'create table' stored proc?Any assistance is greatly appreciated.CindyThis is what I have so far - \[code\]ALTER PROCEDURE [dbo].[rspSCL]-- Add the parameters for the stored procedure here(@year as int,@RRID as int,@State as nchar(2),@Sub as varchar(75),@app as int,@sprayed as int,@subcontracted as int,@thirdapp as bit)ASBEGIN-- SET NOCOUNT ON added to prevent extra result sets from-- interfering with SELECT statements.SET NOCOUNT ON;-- Insert statements for procedure hereDECLARE @SQL as varchar(4000)DECLARE @SUBQ AS varchar(400)--what fields need to be seenSET @SQL = 'SELECT C.CRID, C.DOT, C.RR, C.Pref, C.MP, C.Division, C.Subdivision,C.City, C.Street, C.State, C.County, C.RestrictedCounty, C.Remarks, C.SpecialInstructions, C.Route, C.ThirdAppRequired, C.MainTrks, C.OtherTrks, C.OnSpur, C.MaxSpeed, C.SubContracted, C.FenceEncroachment, C.Lat, C.Long, C.PropertyType, C.WarningDevice,C.Surface, C.ROWNE, C.ROWNW, C.ROWSE, C.ROWSW, C.ROWWidth, C.ExtNE, C.ExtNW, C.ExtSE, C.ExtSW, C.TempActive, A.Spray, A.ContractYear' SET @SUBQ = CASE WHEN @app = 1 THEN 'FROM Crossings LEFT OUTER JOIN (select A.CRID, A.spray FROM ApplicationData A WHERE A.ContractYear = ' + CAST( @year AS varchar(4)) + ') as APPS ON Crossings.CRID = APPS.CRID' WHEN @app = 2 THEN 'FROM Crossings inner join (SELECT crid, C,OUNT(APPID)as count from ApplicationData where ContractYear = ' + CAST( @year AS varchar(4)) + ' group by CRID) as APPS ON Crossings.CRID = APPS.CRID WHERE Crossings.RR = '+ CAST( @RRID AS varchar(4)) + ' and crossings.state = '+ @state + ' and count = 1'when @app = 3 THEN 'FROM Crossings inner join (SELECT crid, COUNT(APPID)as count from ApplicationData where ContractYear = ' + CAST( @year AS varchar(4)) + ' group by CRID) as APPS ON Crossings.CRID = APPS.CRID WHERE Crossings.RR = '+ CAST( @RRID AS varchar(4)) + ' and crossings.state = '+ @state + ' and count = 2' ELSE ''END--add the FROM to the querySET @SQL = @SQL + @SUBQ--add the subdivision to the where statementIf @Sub != 'ALL' SET @SQL = @SQL + 'AND (C.Subdivision = ' + @Sub + ')'ELSE SET @SQL = @SQL--add sprayed to whereDECLARE @S as varchar(100)SET @S =CASE WHEN @sprayed = 1 THEN 'AND (A.sprayed = 1)'WHEN @sprayed = 0 THEN 'AND (A.sprayed = 0)'ELSE ''ENDSET @SQL = @SQL + @S--add subcontracted to whereDECLARE @C as varchar (100)SET @C = CASE WHEN @subcontracted = 0 THEN 'AND (C.subcontracted = 0)'WHEN @subcontracted = 1 THEN 'AND (C.subcontracted = 1)'ELSE ''END SET @SQL = @SQL + @CIF @thirdapp = 1SET @SQL = @SQL + 'AND (C.ThirdAppRequired = 1)'ELSESET @SQL = @SQLEND\[/code\]