如何从sql获取输出参数 [英] How to get output parameters from sql

问题描述

我希望我的id作为输出，当我提交记录时我想看到该记录的ID



im尝试这个我没有得到任何东西

i want my id as output when i submit the record i want to see the ID of that record

i m trying this i am not getting any thing

ALTER PROCEDURE [dbo].[Tech_sup] 
	
(
    
    
	@Build_name varchar(max),
	@Dept varchar(max),
	@Floor_no varchar(50),
	@Call_recvd varchar (max),
	@Ext_no int ,
	@Caller_name varchar(max) ,
	@Call_assign varchar(max),
	@problem varchar(max),
	@ip varchar(20) ,
	@Rec_Status int,
	@prob_type varchar(50),
	@id int output
	
	
	
)
AS
BEGIN
           
          
          INSERT INTO dbo.Tech_data (Build_name,Dept,Floor_no,Call_recvd,Ext_no,Caller_name,Call_assign,problem,ip,date_time,Rec_Status,prob_type)values (@Build_name,@Dept,@Floor_no,@Call_recvd,@Ext_no,@Caller_name,@Call_assign,@problem,@ip,getdate(),1,@prob_type)
            
        return @id

 END

c＃code < br $> b $ b

c# code

con.Open();
                   SqlCommand cmd = new SqlCommand();
                   cmd.CommandType = CommandType.StoredProcedure;
                   cmd.CommandText = "Tech_sup";
                   cmd.Parameters.AddWithValue("@Build_name", Build.Text);
                   cmd.Parameters.AddWithValue("@Dept", DropDownList2.Text);
                   cmd.Parameters.AddWithValue("@Floor_no", Floor.Text);
                   cmd.Parameters.AddWithValue("@Ext_no", Ext.Text);
                   cmd.Parameters.AddWithValue("@Call_recvd",DropDownList4.Text);
                   cmd.Parameters.AddWithValue("@Call_assign", DropDownList1.Text);
                   cmd.Parameters.AddWithValue("@Caller_name", Caller_Nm.Text);
                   cmd.Parameters.AddWithValue("@problem", Prob.Text);
                   cmd.Parameters.AddWithValue("@Rec_Status", SqlDbType.Bit);
                   cmd.Parameters.AddWithValue("@prob_type", DropDownList3.Text);
                   cmd.Parameters.Add("@id", SqlDbType.Int);
                   cmd.Parameters["@id"].Direction = ParameterDirection.Output;





                   if (string.IsNullOrEmpty(Ip.Text))
                   {
                       cmd.Parameters.Add(new SqlParameter("@ip", DBNull.Value.ToString()));
                   }
                   else
                   {
                       cmd.Parameters.Add(new SqlParameter("@ip", Ip.Text));
                   }
                   cmd.Connection = con;
                   cmd.ExecuteNonQuery();
                   con.Close();
                   TextBox1.Text = cmd.Parameters["@id"].Value.ToString();
                   ClientScriptManager cs = Page.ClientScript;
                   cs.RegisterStartupScript(this.GetType(), "id", "alert(" + cmd.Parameters["@id"].Value.ToString() + "');", true);

推荐答案

在sp上插入记录后，写下以下行



SEt @ id =从dbo.Tech_data中选择max（id）



然后返回@id。





你会得到输出。





问候，

Prakash.T

after insert record on sp, write the below line

SEt @id=Select max(id) from dbo.Tech_data

then return @id.


you will get the output.


regards,
Prakash.T

嗨亲爱的，

请更改您的代码如下

存储的Procuedure

Hi Dear,
Please do change in your code as below
Stored Procuedure

ALTER PROCEDURE [dbo].[Tech_sup] 
(
    	@Build_name varchar(max),
	@Dept varchar(max),
	@Floor_no varchar(50),
	@Call_recvd varchar (max),
	@Ext_no int ,
	@Caller_name varchar(max) ,
	@Call_assign varchar(max),
	@problem varchar(max),
	@ip varchar(20) ,
	@Rec_Status int,
	@prob_type varchar(50),
	@id int output
)
AS
BEGIN
        set @id = 0  
          
          INSERT INTO dbo.Tech_data (Build_name,Dept,Floor_no,Call_recvd,Ext_no,Caller_name,Call_assign,problem,ip,date_time,Rec_Status,prob_type)values (@Build_name,@Dept,@Floor_no,@Call_recvd,@Ext_no,@Caller_name,@Call_assign,@problem,@ip,getdate(),1,@prob_type)
            
        set @id = Scope_identity()
 
 END

如果Id列是标识，则在插入语句后写下面一行

If the Id column is identity then write the following line after the insert statement

SET @id = @@IDENTITY

然后返回@id

Then return @id

这篇关于如何从sql获取输出参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！