I have written a stored procedure in SQL and want to use it in my Linq2SQL designer. So I drag and drop it into my designer from server explorer in visual studio (2019) as I usually do, but this procedure has Return Type None. Seems to be clear that there is no value returned when I look at my SQL procedure, because it use EXEC for executing a query, but I can see the result in SQL Server Management Studio.
Here is my stored procedure:
CREATE PROCEDURE [dbo].[ret_VAL]
@ITEMNR nvarchar(255),
@COLUM_NAME nvarchar(255)
AS
DECLARE @query AS NVARCHAR(max)
SET @query = N'SELECT ' + @COLUM_NAME + ' AS VALUE FROM [PROD].[dbo].[MMView] Where ITEMNUMBER like ''' + @ITEMNR + ''''
EXEC (@query)
GO
When I run the stored procedure in SSMS I am getting a result, but I can't use it in Linq2SQL. Sure, I can drag and drop it in my Linq2SQL designer but it has no Return Type. Can somebody tell me what I am missing here, to get a Return Type (Return value)?
The L2S designer can't determine the return type for dynamically executed queries.
Are all columns that you pass in the "@colum_name" parameter of the same data type?
If so, if you temporarily change the stored proc to something like:
CREATE PROCEDURE [dbo].[ret_VAL]
@ITEMNR nvarchar(255),
@COLUM_NAME nvarchar(255)
AS
SELECT top 1 somecolumn AS VALUE FROM [PROD].[dbo].[MMView] Where 1=0
GO
...and then add it to the L2S designer, then it should get the correct return type definition in the datacontext. Then you can change the stored proc back to the original definition.
However, if the columns are of different data type then that won't work since the definition will only match the column you use in the temporarily changed stored proc.