储存程序 [英] store procedure

问题描述

cmd = new SqlCommand("SELECT PrivillegesName, PrivillegesGroup from tblPrivilleges where PrivillegesID in ( " + privillege + " ) ", cn);
            SqlDataReader dr = cmd.ExecuteReader();

我想为此编写存储过程...我的问题是如何通过("+ privillege +")作为@ parameter

i want to write store procedure for this...my problem is that how to pass ( " + privillege + " ) as @ parameter

推荐答案

这里的参数完全没有上下文.您正在将查询字符串传递给阅读器，因此必须在将其传递给阅读器之前对其进行构建.

其次，检查值是否在集合中要求您指定集合.在您的情况下，这意味着以允许sql执行的方式格式化字符串，例如"1,2,3,4".

最后，您拼写错误了特权" ...

First, the idea of a parameter is completely out of context here. You''re passing a query string to your reader, so you have to build it BEFORE passing it to your reader.

Second, checking to see if a value is in a set requires that you specify the set. In your case, it means formatting a string in such a way as to allow the sql to execute, such as "1,2,3,4".

Lastly, you misspelled "privilege"...

您像这样向SqlCommand对象添加了一个参数；

You add a parameter to the SqlCommand object like so;

cmd.Parameters.AddWithValue("@privillege", privillege);

也不要忘记将SqlCommand.CommandType更改为存储过程，例如

also don''t forget to change the SqlCommand.CommandType to stored procedure e.g.

cmd.CommandType = CommandType.StoredProcedure;

但是，对于指定的查询，您不能使用参数，因为将IN作为WHERE子句的一部分使用时，您不能传递用于这种情况的参数.

However in your case with the query as specified you can''t use parameters as you are using IN as part of the WHERE clause you can''t pass parameters for use in this case.

将"privillege"作为varchar参数传递，并在您的过程中使用sp_executesql执行语句

pass "privillege" as varchar parameter and in your procedure use sp_executesql to execute statement

create proceudre getitems 
@privilleg varchar(max)
as 
begin


SET @SQLString = "SELECT *
    FROM table1
    WHERE  items in (@item)";


DECLARE @SQLString nvarchar(500);
DECLARE @ParmDefinition nvarchar(500);

SET @ParmDefinition = "@item varchar(max)";


EXECUTE sp_executesql
    @SQLString
    ,@ParmDefinition
    ,@item = @privilleg

end

这篇关于储存程序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！