Fixing LINQ to SQL Issue: Stored Procedure Definition Returns int Instead of ResultSet

Kavitha

15 years ago

Problem

Today I was struck with an annoying problem while generating LINQ to SQL dbml file for a stored procedure using Visual Studio 2008.

When the stored procedure is dragged and dropped on the DBML designer, Visual Studio is generating wrong signature for the stored procedure output: instead of returning a result set as output, an int value is set as output.

Possible Cause

Only one stored procedure out of 100s available in the dbml file is affected with the bug in LINQ to SQL class builder. When the stored procedure is closely examined, the sp is returning a single record as output and the values of output columns are calculated in the sp(they are not retrieved from tables directly). Looks like it is the cause for misbehavior of Visual Studio 2008.

Solution or Workaround

After spending couple of unfruitful hours in fixing the issue directly, I Google for solution to the issue. Luckily a blog post gave a tip on work around to sort out this annoying issue. The work around is

Create a dummy stored procedure with same signature(input parameters & output columns) by returning dummy values
Generate the dbml file using the dummy stored procedure
Drop the dummy stored procedure and create the actual stored procedure on the database

Here is the dummy stored procedure used for generating the dbml file

CREATE PROCEDURE pGetProfileDetails  
(  
 @P_IdentifyingUID BIGINT,  
 @P_IdentifyingType VARCHAR(50)  
    
)  
AS  
BEGIN 
	SELECT	'test' AS Name, 
		'test' AS Industry, 
		'test' AS FunctionName, 
		'test' as JobRoles, 
		'test' as Skills, 
		'test' as Specializations
END

via