locked
Stored Procedure with Output parameter.. RRS feed

  • Question

  • User-1355475649 posted

    Hello everyone

    I have a 5 tables in My DB, and at first I want to select rows from tb_SPWebApplications table

    And AppName value will use as a string value to join another tables in ASP.NET code behind

    So I want to set @AppName but I don`t know well how to do it

    Please help me how can I do that...

    I made a sp like this

    ALTER PROCEDURE [dbo].[UP_SELECT_APPLICATION_STATUS]
    				@AppUrl		nvarchar(200)
    			,	@AppName	nvarchar(200)	output
    AS
    BEGIN
    	SET NOCOUNT ON;
    
        -- Insert statements for procedure here
    	SELECT 
    				AppName
    			,	AppUrl
    			,	AppPort
    			,	AppDefaultTimeZone
    			,	AppMaxiumFileSize
    			,	AppContentDatabaseCount
    			,	AppSitesCount
    	FROM
    			tb_SPWebApplications
    	WHERE
    				AppUrl = @AppUrl
    	
    	
    END
    Tuesday, September 27, 2016 8:45 AM

Answers

  • User-2057865890 posted

    Hi SuperRyden,

    You could refer following code snippets, see how to use and return value from Stored Procedure using Output Parameter in ASP.Net.

    Stored Procedure

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE PROCEDURE [dbo].[GetFruitName]
          @FruitId INT,
          @FruitName VARCHAR(30) OUTPUT
    AS
    BEGIN
          SET NOCOUNT ON;
         
          SELECT @FruitName = FruitName
          FROM Fruits
          WHERE FruitId = @FruitId
    END

    Return Output parameter from Stored Procedure in ASP.Net

    protected void Submit(object sender, EventArgs e)
    {
        string constring = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
        using (SqlConnection con = new SqlConnection(constring))
        {
            using (SqlCommand cmd = new SqlCommand("GetFruitName", con))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@FruitId", int.Parse(txtFruitId.Text.Trim()));
                cmd.Parameters.Add("@FruitName", SqlDbType.VarChar, 30);
                cmd.Parameters["@FruitName"].Direction = ParameterDirection.Output;
                con.Open();
                cmd.ExecuteNonQuery();
                con.Close();
                lblFruitName.Text = "Fruit Name: " + cmd.Parameters["@FruitName"].Value.ToString();
            }
        }
    }

    reference:

    #How to return Output parameter from Stored Procedure in ASP.Net using C# and VB.Net

    http://www.aspsnippets.com/Articles/How-to-return-Output-parameter-from-Stored-Procedure-in-ASPNet-in-C-and-VBNet.aspx 

    Best Regards,

    Chris

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, September 28, 2016 11:25 AM
  • User-595703101 posted

    Hello SuperRyden,

    You can try using temp table, which is not a good way but a solution from past

    CREATE PROCEDURE [dbo].[GetProduct]
    				@ProductId int,
    				@Name nvarchar(200) output
    AS
    BEGIN
    
    	SELECT 
    		ProductId, Name, ProductNumber
    	into #tmp
    	FROM Product
    	WHERE ProductId = @ProductId
    
    	select @Name = Name from #tmp
    	select * from #tmp
    			
    END
    GO
    
    declare @ProductId int = 4, @Name nvarchar(200)
    exec [UP_SELECT_APPLICATION_STATUS] @ProductId, @Name out
    select @Name

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, September 29, 2016 8:30 AM

All replies

  • User-2057865890 posted

    Hi SuperRyden,

    You could refer following code snippets, see how to use and return value from Stored Procedure using Output Parameter in ASP.Net.

    Stored Procedure

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE PROCEDURE [dbo].[GetFruitName]
          @FruitId INT,
          @FruitName VARCHAR(30) OUTPUT
    AS
    BEGIN
          SET NOCOUNT ON;
         
          SELECT @FruitName = FruitName
          FROM Fruits
          WHERE FruitId = @FruitId
    END

    Return Output parameter from Stored Procedure in ASP.Net

    protected void Submit(object sender, EventArgs e)
    {
        string constring = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
        using (SqlConnection con = new SqlConnection(constring))
        {
            using (SqlCommand cmd = new SqlCommand("GetFruitName", con))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@FruitId", int.Parse(txtFruitId.Text.Trim()));
                cmd.Parameters.Add("@FruitName", SqlDbType.VarChar, 30);
                cmd.Parameters["@FruitName"].Direction = ParameterDirection.Output;
                con.Open();
                cmd.ExecuteNonQuery();
                con.Close();
                lblFruitName.Text = "Fruit Name: " + cmd.Parameters["@FruitName"].Value.ToString();
            }
        }
    }

    reference:

    #How to return Output parameter from Stored Procedure in ASP.Net using C# and VB.Net

    http://www.aspsnippets.com/Articles/How-to-return-Output-parameter-from-Stored-Procedure-in-ASPNet-in-C-and-VBNet.aspx 

    Best Regards,

    Chris

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, September 28, 2016 11:25 AM
  • User-595703101 posted

    Hello SuperRyden,

    You can try using temp table, which is not a good way but a solution from past

    CREATE PROCEDURE [dbo].[GetProduct]
    				@ProductId int,
    				@Name nvarchar(200) output
    AS
    BEGIN
    
    	SELECT 
    		ProductId, Name, ProductNumber
    	into #tmp
    	FROM Product
    	WHERE ProductId = @ProductId
    
    	select @Name = Name from #tmp
    	select * from #tmp
    			
    END
    GO
    
    declare @ProductId int = 4, @Name nvarchar(200)
    exec [UP_SELECT_APPLICATION_STATUS] @ProductId, @Name out
    select @Name

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, September 29, 2016 8:30 AM