-- Populate temporary table with da

[manuela]

Attn Rajeev
thanks very much

-- Populate temporary table with data where [datSurvey].[Status] <> 'D'
DECLARE curSurvey CURSOR FOR
SELECT [column names]
FROM [datSurvey] LEFT OUTER JOIN [datUser] ON [datSurvey].[Link] = [datUser].[Link]
WHERE [datSurvey].[Status] <> 'D'
ORDER BY [datUser].[GroupingLevel1] ASC

OPEN curSurvey
FETCH NEXT FROM curSurvey

INTO @Link, @SurveyID,@SurveyType,
@GroupingLevel1, @GroupingLevel2, @GroupingLevel3, @GroupingLevel4, @GroupingLevel5

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.

WHILE (@@FETCH_STATUS = 0)
BEGIN

-- if no parameters entered, return all data

IF ( (@Param_SurveyID = NULL) AND (@Param_GroupingLevel1 = NULL) )

BEGIN

SELECT [database column names]
FROM [datSurvey] LEFT OUTER JOIN [datUser] ON [datSurvey].[Link] = [datUser].[Link]
WHERE [datSurvey].[Status] <> 'D'
ORDER BY [datUser].[GroupingLevel1] ASC
END

-- if ONLY Survey Definition (i.e. Survey Type) parameter selected and others NULL

IF ( (@Param_SurveyID = NULL) AND (@Param_GroupingLevel1 = NULL) )

BEGIN
SELECT [column names]
FROM [datSurvey] LEFT OUTER JOIN [datUser] ON [datSurvey].[Link] = [datUser].[Link]
WHERE ( [datSurvey].[Status] <> 'D'
AND [datSurvey].[SurveyType] = @Param_SurveyID )
ORDER BY [datUser].[GroupingLevel1] ASC
END

END

CLOSE curSurvey

SELECT *
FROM #rpt_Survey;
DEALLOCATE curSurvey

 

[rajeevnandanmishra]

From your code i am not finding the create statement for table #rpt_Survey

I think if you create the #rpt_Survey at the top of your procedure with something like this.

Create #rpt_Survery (Column(s) ...)

And in the If statement before Selecting write
&quot;INSERT #rpt_Survey &quot; in both IFs.
like ...
---------------
IF ( (@Param_SurveyID = NULL) AND (@Param_GroupingLevel1 = NULL) )

BEGIN
INSERT #rpt_Survey
SELECT [database column names]
FROM [datSurvey] LEFT OUTER JOIN [datUser] ON [datSurvey].[Link] = [datUser].[Link]
WHERE [datSurvey].[Status] <> 'D'
ORDER BY [datUser].[GroupingLevel1] ASC
END
---------------


Hope you will get the desired result.

 

[manuela]

Rajeev thanks
meants to say have declared the two parameter variables before AS
and have declared other variables using DECLARE after AS
and then have created temp table
looks as if problem might be INSERT bit missing
will see how I go

 

[manuela]

Attn Rajeev
thanks very much for your help
all OK now!!!!