sql server过程中传递参数 [英] parametter pass in sql server procedure

问题描述

我正在创建一个程序

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.

推荐答案

将sql部分中的第13行更改为如下所示:

Change line 13 in the sql part to something like this:

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

为避免可能的SQL注入风险，并利用了缓存的查询计划.您也可以尝试这样的操作.

To avoid possible SQL Injection risk and gives advantage of cached query plan. You also can try something like this.

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

HI，

试试这个

更改过程[dbo].[getDept](@ DptID int，@ varCn int，@ ids varchar(max))在
上没有资产 声明@query varchar(500)
如果@ varCn = 1
开始
从deptId = @ DptID
的部门中选择dept_name 结束
否则，如果@ varCn = 2
开始
设置@query =''从(``+ @ ids +'')中的deptId的部门中选择dept_name''
exec(@query)
结束
其他
从部门开始选择部门名称
结束

Try this

ALTER PROCEDURE [dbo].[getDept](@DptID int,@varCn int,@ids varchar(max))asset nocount on
Declare @query varchar(500)
if @varCn=1
begin
select dept_name from departments where deptId=@DptID
end
else if @varCn=2
begin
set @query =''select dept_name from departments where deptId in (''+@ids+'')''
exec (@query)
end
else
begin select dept_name from departments
end

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