如何在存储过程中使用多个查询 [英] how to use multiple query in stored procedure

问题描述

在下面的代码中，我在SP中使用了多个查询.当我从C#代码传递类型2时，它期望@noofpersons为错误.在选择查询中没有必要

In below code i used multiple queries in SP. when i passed type 2 from C# code its expecting @noofpersons as a error. which is not necessary in that select query

CREATE PROCEDURE [dbo].[RoomDetails_Insertquery]
(
@type int,
@roomno bigint,
@noofpersons bigint,
@roomtype nvarchar(150),
@amt bigint
)
AS
BEGIN
	IF @type=1
BEGIN
	INSERT INTO roomdetails VALUES (@roomno,@noofpersons,@roomtype,@amt)
END

ELSE IF @type=2
BEGIN
	SELECT * FROM roomdetails WHERE roomno=@roomno
END
ELSE IF @type=3
BEGIN
UPDATE roomdetails SET noofpersons=@noofpersons,roomtype=@roomtype,@amt=amt WHERE roomno=@roomno
END   
END

推荐答案

调用存储的proc传递Null时

while calling stored proc pass Null

exec  [dbo].[RoomDetails_Insertquery] @type=2,@roomno=200,@noofpersons=null,@roomtype='something',
@amt=2000

或在程序中，
在声明@noopersons时将其设置为默认值

or in the procedure,
while declare @noopersons set it as default

CREATE PROCEDURE [dbo].[RoomDetails_Insertquery]
(
@type int,
@roomno bigint,
@noofpersons bigint =null,
@roomtype nvarchar(150),
@amt bigint 
)

并且您只能传递其他4个参数

and you can pass only the other 4 parameters

尝试以下代码:-

根据您的sp，必须将所有参数传递给sp ..

try below code:-

As per your sp,you must pass the all parameters to sp..

SqlCommand cmd = new SqlCommand("RoomDetails_Insertquery", gn.cn());
                  cmd.Parameters.AddWithValue("@type", 2);
                  cmd.Parameters.AddWithValue("@roomno", textBox1.Text);
                  cmd.Parameters.AddWithValue("@noofpersons", Null);
                  cmd.Parameters.AddWithValue("@roomtype",  Null);
                  cmd.Parameters.AddWithValue("@amt", Null); 
                  cmd.CommandType = CommandType.StoredProcedure;
                  SqlDataAdapter da = new SqlDataAdapter(cmd);
                  DataTable dt = new DataTable();
                  da.SelectCommand = cmd;

这篇关于如何在存储过程中使用多个查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！