sql server过程中传递参数

本文介绍了sql server过程中传递参数的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我正在创建一个程序

I m create a procedure

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[getDept](@DptID int,@varCn int,@ids varchar(max))
as
set nocount on
       if @varCn=1
begin
          select dept_name from departments where deptId=@DptID
end
else if @varCn=2
begin
            select dept_name from departments where deptId in (@ids)
end
else
begin
            select dept_name from departments
end

我在C#中称procdure:-

I call procdure in c# :-

DataSet ds = new DataSet();
ds = Connector.ExecuteDataset(func.cn, CommandType.StoredProcedure, "getDeptt", new SqlParameter[3]{
            new SqlParameter("@DptID", 1),
            new SqlParameter("@varCn", 2),
           new SqlParameter("@ids", "2,3,4,5")});

但出现错误(Conversion failed when converting the varchar value ''2,3,4,5'' to data type int

请帮忙.

在此先感谢.

But an error ( Conversion failed when converting the varchar value ''2,3,4,5'' to data type int

Please help.

Thanks in advance.

推荐答案

DECLARE @SQL NVARCHAR(4000)
SET @SQL=
''select dept_name from departments where deptId in ('' +@ids + '')''
EXEC(@SQL)

else if @varCn=2
begin
    DECLARE @sqlCommand nvarchar(max)
    SET @sqlCommand = ' SELECT dept_name from departments where deptId in (' + @ids +')'
    EXECUTE sp_executesql @sqlCommand, N'@ids varchar(max)', @ids = @ids
end

这篇关于sql server过程中传递参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！