在数据库中动态运行Storedprocedure语法 [英] Run Storedprocedure syntax dynamically in database

问题描述

我已经存储了.sql文件，它包含来自"create proc ......"的内容.我想动态运行它.
我从一个位置选择.sql文件并读取该文件，然后将内容作为参数发送到存储过程中.现在我要运行此内容.我能做些什么?请帮助我.:-)

I have storedprocedure .sql file, it contains content from "create proc ......". I want to run this dynamically.
I select .sql file from one location and read this and send content as an parameter in a storedprocedure. Now i want to run this content. What can i do? pls help me.:-)

推荐答案

我从您的问题中了解到您保存了存储的sql的文件，您正试图访问该文件. br/>
首先，您应该了解保存在文件中的存储过程仅适用于用户，而不适用于sql服务器.实际的存储过程将另存为数据库对象.

您只需要创建一个sql连接对象和命令对象，即可从数据库访问您的存储过程.

您可以检查以下代码:

What i have understood from your question the file in which you have saved the sql stored you are trying to access that one.

First thing you should understood that the stored procedure saved in the file is just for the user and not for the sql server. The actual stored procedure is saved as database object in your database.

you just need to create an sql connection object and command object which are going to access your stored procedure from the database.

You can check this code:

public void RunStoredProc()
	{
		SqlConnection conn = null;
		SqlDataReader rdr  = null;

		Console.WriteLine("\nTop 10 Most Expensive Products:\n");

		try
		{
			// create and open a connection object
			conn = new 
				SqlConnection("Server=(local);DataBase=Northwind;Integrated Security=SSPI");
			conn.Open();

			// 1. create a command object identifying
			// the stored procedure
			SqlCommand cmd  = new SqlCommand(
				"Ten Most Expensive Products", conn);

			// 2. set the command object so it knows
			// to execute a stored procedure
			cmd.CommandType = CommandType.StoredProcedure;

			// execute the command
			rdr = cmd.ExecuteReader();

			// iterate through results, printing each to console
			while (rdr.Read())
			{
				Console.WriteLine(
					"Product: {0,-25} Price:

{1,6:####.00}", rdr["TenMostExpensiveProducts"], rdr["UnitPrice"]); } } finally { if (conn != null) { conn.Close(); } if (rdr != null) { rdr.Close(); } } }

{1,6:####.00}", rdr["TenMostExpensiveProducts"], rdr["UnitPrice"]); } } finally { if (conn != null) { conn.Close(); } if (rdr != null) { rdr.Close(); } } }

如果要在存储过程中动态运行SQL语句，可以使用 ^ ]

If you want to run an SQL statement dynamically inside a stored procedure, you can use sp_executesql[^]

这篇关于在数据库中动态运行Storedprocedure语法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！