如何在MS SQL Server中将内联SQL转换为存储过程 [英] How to convert Inline SQL to Store Procedure in MS SQL Server

问题描述

如何将下面的SQL内联查询转换为MS SQL Server中的存储过程？

数据集sql_text = ISQL.ExecuteDataSet（< span class =code-string>  SP_ParameterValAdmin）; 
 
 sql_command = sql_text.Tables [ 0 ]。行[ 0 ] [  0 ]。ToString（）; 
 
 sql_command = 
 选择 DisplayName，报告代码
 来自 mstreports 
 其中 reportgroupcode = + 转换（myData.Tables [ 0 ]。行[i] [ 1 ]）+ 和有效= 1 + sql_command + 
 订单   reportcode 

解决方案

它应该如下所示：

 创建  PROCEDURE  TheNameOfProcedure 
  @ reportgroupcode < TypeOfData>，
  @ AnotherCondition < TypeOfData> 
  AS  
  BEGIN  
  SELECT  DisplayName，reportcode 
  FROM  mstreports 
  WHERE  reportgroupcode =  @ reportgroupcode   AND  active = 1  AND   @ AnotherCondition  
  ORDER   BY  reportcode 
  END  

其中：

< TypeOfData> 表示其中之一：数据类型（T-SQL） [ ^ ]

@rertrtgroupcode 来自 myData.Tables [0] .Rows [i] [1]

@AnotherCondition 来自 sql_text.Tables [0] .Rows [0] [0] .ToString（）

您应该对查询进行参数化，以使其成为正确的SQL存储过程。



SQL逻辑将是相同的，但是你会将代码中的@variables（参数）传递给SQL。



可能你应该在这里查看这个指南，它会有所帮助你进一步为sql商店程序创建复杂的动态内联查询。



优化存储过程 [ ^ ]



希望这会对你有帮助。

How to convert below SQL inline Query to Stored Procedure in MS SQL Server ?

Dataset sql_text = ISQL.ExecuteDataSet("SP_ParameterValAdmin");

sql_command = sql_text.Tables[0].Rows[0][0].ToString();

sql_command =
select DisplayName,reportcode
from mstreports
where reportgroupcode= + Convert(myData.Tables[0].Rows[i][1]) +  and active=1 + sql_command +
order by reportcode

解决方案

It should looks like:

CREATE PROCEDURE TheNameOfProcedure
    @reportgroupcode <TypeOfData>,
    @AnotherCondition <TypeOfData>
AS
BEGIN
    SELECT DisplayName, reportcode
    FROM mstreports
    WHERE reportgroupcode= @reportgroupcode  AND active=1 AND @AnotherCondition
    ORDER BY reportcode
END

where:
<TypeOfData> means one of that: Data Types (T-SQL)[^]
@reposrtgroupcode comes from myData.Tables[0].Rows[i][1]
@AnotherCondition comes from sql_text.Tables[0].Rows[0][0].ToString()

You should make your query parameterized in order to make it proper SQL Store Procedure.

The SQL logic will be the same however you will be passing @variables(parameters) form your code to SQL.

Probably you should check this guidelines here, It will help you overall further for creating complex dynamic inline queries to sql store procedures.

Optimize-stored-procedures[^]

Hope this will help you.

这篇关于如何在MS SQL Server中将内联SQL转换为存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！