向SQL中的存储过程传递以逗号分隔的参数

本文关键字：分隔 参数 SQL 存储过程 | 更新日期: 2023-09-27 18:13:49

如果我从T-SQL调用我的存储过程:

exec [dbo].[StoredProcedureName] '''Vijay'', ''Rana'', 1, 0'

在SQL Server Mgmt Studio，它工作得很好但当我从我的应用程序调用它它给我错误

字符串" Vijay'，'Rana'，1,0'后的未闭引号。

我在谷歌上搜索并找到了这个EXEC sp_executesql @FinalQuery，但它不适合我

编辑
我把它命名为

public virtual IDataReader ImportFirefighter(String  query)
{
        Database database = DatabaseFactory.CreateDatabase();
        DbCommand command = database.GetStoredProcCommand("[StoreProcedureName]");
        database.AddInParameter(command, "@query", DbType.String, query);
        IDataReader reader = null;
        try
        {
            reader = database.ExecuteReader(command);
        }
        catch (DbException ex)
        {
            throw new DataException(ex);
        }
        return reader;
    }

EDIT My complete Store Procedure

-- =============================================          
-- Author:  <Author,,Name>          
-- Create date: <Create Date,,>          
-- Description: <Description,,>          
-- =============================================         
--[dbo].[ImportIntoFirefighter]  '''Vijay'',''Rana'',''AC'',''AC'',''VOL'',1,0,0,1,1,''NA'','''',''VOL'','''','''',0,'''','''',0,1,1,'''',0&''Vijay21'',''Rana2'',''AC'',''AC'',''VOL'',1,0,0,1,1,''NA'','''',''VOL'','''','''',0,'''','''',0,1,1,'''',0&''Vijay32'',''Rana3'',''AC'',''AC'',''VOL'',1,0,0,1,1,''NA'','''',''VOL'','''','''',0,'''','''',0,1,1,'''',0&''Vijay42'',''Rana4'',''AC'',''AC'',''VOL'',1,0,0,1,1,''NA'','''',''VOL'','''','''',0,'''','''',0,1,1,'''',0'  

ALTER PROCEDURE [dbo].[ImportIntoFirefighter]         
@query VARCHAR(MAX)          
AS          
BEGIN         
DECLARE @TotalRecord int          
DECLARE @loopcount int          
DECLARE @TempQueryList TABLE                
  (             
  [ID] INT IDENTITY(1,1),                        
     [VALUE] VARCHAR(1000)                
   )           
DECLARE @Result TABLE                
  (             
  [iff_id] INT IDENTITY(1,1),                        
     [last_name] VARCHAR(50),        
     [first_name] VARCHAR(50),        
     [email] VARCHAR(50),        
     [mobile_number] VARCHAR(50),        
     [error] VARCHAR(max)              
   )           

   insert into @TempQueryList (VALUE) (           
   SELECT SUBSTRING('&' + @query + '&', Number + 1,          
    CHARINDEX('&', '&' + @query + '&', Number + 1) - Number -1)AS VALUE          
    FROM master..spt_values          
    WHERE Type = 'P'          
    AND Number <= LEN('&' + @query + '&') - 1          
    AND SUBSTRING('&' + @query + '&', Number, 1) = '&' )          
 Set @TotalRecord = (select count(*) FROM @TempQueryList)                   
  --select * from @TempQueryList          
   --Loop For Each Repeated Schedule           
 set  @loopcount = 1          
 WHILE @loopcount <= @TotalRecord            
  BEGIN          
  Declare @SingleQuery varchar(1000)        
  select @SingleQuery = Value  from @TempQueryList where id = @loopcount          
   BEGIN TRY        
   --print '[AddFirefighter] '  +  @SingleQuery        
   --SELECT 1/0;        
    --execute (@SingleQuery)    
    declare @FinalQuery varchar(max)   
   -- Select @SingleQuery =  LEFT(RIGHT(@SingleQuery, len(@SingleQuery)-1),len(@SingleQuery)-2)  

    set @FinalQuery = '[AddFirefighter] ' +  @SingleQuery  
        print  @FinalQuery  


  EXEC  (@FinalQuery)   

   END TRY        
   BEGIN CATCH        
   insert into @Result (last_name,first_name,email,mobile_number,error) values ( '','','','',ERROR_MESSAGE() )         
   -- Execute the error retrieval routine.            
   END CATCH         
  --print @loopcount          
  SET @loopcount = @loopcount + 1          
  END          
  select * from @Result        
--execute (@query)          
END

向SQL中的存储过程传递以逗号分隔的参数

是分隔符，所以在我看来，你的字符串变成了'Vijay'，'Rana'，1,0我认为你是在同一个"字符串"中混合字符串和数字，你需要做的是传递'Vijay'，'Rana'，'1'，'0'(一串字符串)，然后在你的过程中整理东西。要做到这一点，你传递的字符串应该是"Vijay"，"Rana"，"1"，"0"之类的东西。根据您如何处理存储过程中的事情，您甚至可能需要'' '''' Vijay''''，''''Rana''''，''''1''''，''''0'''' '' .最好创建一个简单的过程，它只返回字符串作为测试床

如果您正在使用c#和asp.net，您应该在代码中设置参数，而不是构建动态sql语句。如果您已经设置了存储过程，那么我认为没有理由调用动态sql语句并在字符串中构建参数。

下面是一个使用存储过程参数化调用sql的示例。http://msdn.microsoft.com/en-us/library/yy6y35y8 (v = vs.110) . aspx 
using (SqlConnection connection = new SqlConnection(connectionString))
{
// Create the command and set its properties.
SqlCommand command = new SqlCommand();
command.Connection = connection;
command.CommandText = "SalesByCategory";
command.CommandType = CommandType.StoredProcedure;
// Add the input parameter and set its properties.
SqlParameter parameter = new SqlParameter();
parameter.ParameterName = "@CategoryName";
parameter.SqlDbType = SqlDbType.NVarChar;
parameter.Direction = ParameterDirection.Input;
parameter.Value = categoryName;
// Add the parameter to the Parameters collection.
command.Parameters.Add(parameter);
// Open the connection and execute the reader.
connection.Open();
SqlDataReader reader = command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
Console.WriteLine("{0}: {1:C}", reader[0], reader[1]);
}
}
else
{
Console.WriteLine("No rows found.");
}
reader.Close();
}

如果根据您的问题，您的存储过程有四个参数，那么您可以将这些参数添加到SqlCommand中，然后执行命令。

    //Build your command
    SqlConnection conn = new SqlConnection(yourConnectionString);
    SqlCommand cmd = new SqlCommand("stored_procedure_name", conn);
    cmd.CommandType = CommandType.StoredProcedure;
    //Define the parameters to pass to the stored procedure
    cmd.Parameters.Add("@firstParameter", SqlDbType.NVarChar, 255);
    cmd.Parameters.Add("@secondParameter", SqlDbType.NVarChar, 255);
    cmd.Parameters.Add("@thridParameter", SqlDbType.Int);
    cmd.Parameters.Add("@fourthParameter", SqlDbType.Int);
    //Assign Values to the parameters
    cmd.Parameters["@firstParameter"].Value = "Vijay";
    cmd.Parameters["@secondParameter"].Value = "Rana";
    cmd.Parameters["@thirdParameter"].Value = 1;
    cmd.Parameters["@fourthParameter"].Value = 0;
    //Execute the command
    conn.Open();
    cmd.ExecuteNonQuery();
    conn.Close();
相关文章：