LINQ to SQL Cret [英] LINQ to SQL Cret

问题描述

如果执行a，如何获取存储过程以返回表用存储过程构建的select语句？我需要在ac＃LINQ to SQL查询中使用结果数据集。

我需要它来 

return  （（ ISingleResult < sp_ssi_WARResult >） （result.ReturnValue））;

而不是

  return   （（  int  < span style ="font-family：Consolas; color：black">）（result.ReturnValue））;  

请参阅存储的 
程序代码

谢谢

 ALTER PROCEDURE [dbo]。[sp_CompareTables2]（@ table1 varchar（100 ），
 @ table2 Varchar（100），@ T1ColumnList varchar（1000），
 @ T2ColumnList varchar（1000）=''）
 AS 
 
声明@SQL varchar（8000）; 
 DECLARE @result AS TABLE（
 ID INT，
 Message VARCHAR（50）
）
 IF @ t2ColumnList =''SET @ T2ColumnList = @ T1ColumnList 
 set @SQL ='SELECT'''+ @ table1 +'''AS TableName，'+ @ t1ColumnList + 
'FROM'+ @ Table1 +'UNION ALL SELECT'''+ @ table2 +'''作为TableName，'+ 
 @ t2ColumnList +'FROM'+ @ Table2 
设置@SQL ='SELECT Max（TableName）为TableName，'+ @ t1ColumnList + 
' FROM（'+ @SQL +'）GROUP BY'+ @ t1ColumnList + 
'HADING COUNT（*）= 1'
 +'order by'+ @ T1ColumnList 
 exec（@SQL ）

解决方案

您可能想在SQL论坛而不是C＃论坛中询问。

无论如何，这里有一篇文章如何制作一个返回表格的UDF：

http://msdn.microsoft.com/en-us/library/ ms191165.aspx

唯一棘手的部分是你必须在返回类型声明期间定义表的签名（类似于你创建表的方式）。

如果你想获得额外的输出值（比如选择的@@ Rowcount）你可以使用
输出参数。

How can I get a stored procedure to return a table after if executes a select statement built with the stored procedure? I need to use the resulting data set in a c# LINQ to SQL query.

I need it to 

return ((ISingleResult<sp_ssi_WARResult>)(result.ReturnValue));

Instead of

return ((int)(result.ReturnValue));

Please see stored  procedure code

Thanks

ALTER PROCEDURE [dbo].[sp_CompareTables2](@table1 varchar(100),
        @table2 Varchar(100), @T1ColumnList varchar(1000),
        @T2ColumnList varchar(1000) = '')
AS
 
declare @SQL varchar(8000);
DECLARE @result AS TABLE (
   ID INT,
   Message VARCHAR(50)
)
IF @t2ColumnList = '' SET @T2ColumnList = @T1ColumnList
set @SQL = 'SELECT ''' + @table1 + ''' AS TableName, ' + @t1ColumnList +
' FROM ' + @Table1 + ' UNION ALL SELECT ''' + @table2 + ''' As TableName, ' +
@t2ColumnList + ' FROM ' + @Table2
set @SQL = 'SELECT Max(TableName) as TableName, ' + @t1ColumnList +
' FROM (' + @SQL + ') A GROUP BY ' + @t1ColumnList +
' HAVING COUNT(*) = 1'
+ ' order by  ' + @T1ColumnList 
exec ( @SQL)

解决方案

You may want to ask that in the SQL Forum instead of the C# Forum.

Anyway here is an article how to make a UDF that returns a table:

http://msdn.microsoft.com/en-us/library/ms191165.aspx

The only tricky part is that you have to define the signature of the table (similar to how you create a table) during the return type declaration.

If you want to have additional output values (like @@Rowcount of the select) you can use output-parameters for those.

这篇关于LINQ to SQL Cret的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！