Problem with Store Procedure

[varshavmane]

Hi All, I am using WCF Service with Silverlight. I wrote a StoredProcedure which returns the records from temp table. But when i drag that SP into .dbml file, the return type of SP is becomes int, not ISingleResultType<>. I dont know reason for this. Here is the SP:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[proc_GetProgressionAsaPercentageOfStage1]
@BaseModelID INT
AS
BEGIN
BEGIN TRY

SET NOCOUNT ON;
DECLARE @ID INT, @PercentageOfFirstStage DECIMAL, @Name NVARCHAR(500)

DECLARE  cur\_StageName CURSOR FOR
	SELECT ID, Name
	FROM dbo.tb\_Stage
	WHERE (No <> 0) AND (BaseModelID = 1)

DECLARE @StageCnt AS INT
	SELECT @StageCnt = COUNT(\*)
	FROM dbo.tb\_Stage
	WHERE (No <> 0) AND (BaseModelID = @BaseModelID) GROUP BY BaseModelID

DECLARE @Cnt INT
SET @Cnt = 0

DECLARE @PrevName NVARCHAR(500)
SET @PrevName = ''
DECLARE @sqlstr NVARCHAR(MAX)

IF OBJECT\_ID('tempdb.dbo..##tb\_Columns') IS NOT NULL
DROP TABLE ##tb\_Columns

SET @sqlstr = 'CREATE TABLE ##tb\_Columns(Description NVARCHAR(50),'

DECLARE @SQL NVARCHAR(MAX)

OPEN cur\_StageName
FETCH NEXT FROM cur\_StageName INTO @ID,@Name
WHILE @@FETCH\_STATUS = 0
	BEGIN
		IF @PrevName <> ''
			BEGIN
				SET @SQL = '\[' + @PrevName + ' To ' + @Name + '\]  DECIMAL(5,2)'
				IF @Cnt = @StageCnt - 2
					SET @sqlstr = @sqlstr + @SQL
				ELSE
					SET @sqlstr = @sqlstr + @SQL + ','
				SET @Cnt = @Cnt + 1					
			END
			SET @PrevName = @Name
		FETCH NEXT FROM cur\_StageName INTO @ID,@Name
	END
	SET @sqlstr = @sqlstr + ')'
	EXECUTE SP\_EXECUTESQL @sqlstr
	
	DECLARE  cur\_TimeLineTypeName CURSOR FOR
	SELECT ID, Name
	FROM dbo.tb\_TimeLineType
	WHERE (IsTreatment = 0) AND (BaseModelID = @BaseModelID)
	ORDER BY Name DESC

	OPEN cur\_TimeLineTypeName
	FETCH NEXT FROM cur\_TimeLineTypeName INTO @ID,@Name
	WHILE @@FETCH\_STATUS = 0
		BEGIN
				DECLARE @StrSQL AS NVARCHAR(MAX)
				SET @Cnt = 0
				SET @sqlstr = ''
				DECLARE  cur\_TimeLineProgressions CURSOR FOR
				SELECT 
						--TLTPP.TimeLineTypeID AS TimeLineTypeID,
						TLTPP.PercentageOfFirstStage AS PercentageOfFirstStage
				FROM 
						dbo.tb\_TimeLineType AS TLT 
					INNER JOIN dbo.tb\_TimeLineProgressionsAsPercentageOfFirstStage AS TLTPP 
						ON TLT.ID = TLTPP.TimeLineTypeID 
					INNER JOIN dbo.tb\_Stage AS S 
						ON TLTPP.StageID = S.ID
				WHERE 
						(TLT.BaseModelID = @BaseModelID) 
						AND (S.No <> 0) 
						AND (TLTPP

[Howard Richards]

The designer does try to analyse a SPROC to determine the output type but it's very limited, and in this case you're returning from a temp table based on constructed SQL - it would be difficult to determine what this is. Your best bet is to create a class manually that can hold the results and specify in the designer that it returns that type. Try reading "Handling Multiple Result Shapes from SPROCs" in http://weblogs.asp.net/scottgu/archive/2007/08/16/linq-to-sql-part-6-retrieving-data-using-stored-procedures.aspx[^]

'Howard