过程需要未提供的参数 [英] Procedure expects parameter which was not supplied

问题描述

在 SQL Server 中访问存储过程时出现错误

I'm getting the error when accessing a Stored Procedure in SQL Server

Server Error in '/' Application.
Procedure or function 'ColumnSeek' expects parameter '@template', which was not supplied. 

当我通过 .net 与 sql (System.data.SqlClient) 的数据连接调用带有参数的存储过程时，即使我提供了参数，也会发生这种情况.这是我的代码.

This is happening when I call a Stored Procedure with a parameter through .net's data connection to sql (System.data.SqlClient), even though I am supplying the parameter. Here is my code.

SqlConnection sqlConn = new SqlConnection(connPath);
sqlConn.Open();

//METADATA RETRIEVAL
string sqlCommString = "QCApp.dbo.ColumnSeek";
SqlCommand metaDataComm = new SqlCommand(sqlCommString, sqlConn);
metaDataComm.CommandType = CommandType.StoredProcedure;
SqlParameter sp = metaDataComm.Parameters.Add("@template",SqlDbType.VarChar,50);
sp.Value = Template;

SqlDataReader metadr = metaDataComm.ExecuteReader();

我的存储过程是:

   USE [QCApp]
   GO
   SET ANSI_NULLS ON
   GO
   SET QUOTED_IDENTIFIER ON
   GO

   ALTER PROCEDURE [dbo].[ColumnSeek] 
       @template varchar(50)
   AS
   EXEC('SELECT Column_Name, Data_Type 
   FROM [QCApp].[INFORMATION_SCHEMA].[COLUMNS] 
   WHERE TABLE_NAME = ' + @template);

我想弄清楚我在这里做错了什么.

I'm trying to figure out what I'm doing wrong here.

事实证明，模板为空，因为我从通过 URL 传递的参数中获取它的值，并且我搞砸了 url 参数传递(我使用了 @ 代替 &)

As it turns out, Template was null because I was getting its value from a parameter passed through the URL and I screwed up the url param passing (I was using @ for and instead of &)

推荐答案

我会检查我的应用程序代码，看看您将 @template 设置为什么值.我怀疑它是空的，这就是问题所在.

I would check my application code and see what value you are setting @template to. I suspect it is null and therein lies the problem.

这篇关于过程需要未提供的参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！