You must Sign In to post a response.
  • Category: ASP.NET

    How to return a query from sp?

    I came across the situation where I have to return a query from sp and
    assign that to session.This sample query.Original one is large one having inner join on many table.Table name is dynamic.There 2 query in the sp.One should execute and other can send to the C# page.How to access that query in C#.I don't want it's result.just query.I have shown only one query here that I want to return.

    example :

    create Procedure Sample
    (
    @id int
    )
    as
    begin
    declare @pid varchar(10)
    declare @query varchar(500)

    set @pid = cast(@id as varchar(10))

    set @query ='select * from Table1 where id = '+@pid+''
    --Execute sp_executesql @query
    print @query
    end
  • #695173
    Hi,

    Try like this code


    create procedure qry_return
    @id int,
    @c1 varchar(1000) output
    as
    Begin
    declare @pid varchar(10)
    set @pid = cast(@id as varchar(10))
    set @c1 ='select * from Table1 where id = '''+ @pid + ''''
    return
    End


    From code behind get and store in session


    using System.Data;
    using System.Data.SqlClient;
    public partial class _Default : System.Web.UI.Page
    {
    SqlConnection sqlcon = new SqlConnection(ConfigurationManager.ConnectionStrings["Con"].ConnectionString);
    SqlCommand sqlcmd = new SqlCommand();
    SqlDataAdapter da = new SqlDataAdapter();
    DataTable dt = new DataTable();
    protected void Page_Load(object sender, EventArgs e)
    {
    }
    protected void Button1_Click(object sender, EventArgs e)
    {
    string i;
    sqlcon.Open();
    sqlcmd = new SqlCommand("qry_return", sqlcon);
    sqlcmd.CommandType = CommandType.StoredProcedure;
    sqlcmd.Parameters.Add("@id", SqlDbType.Int).Value = 101; //pass your textbox value here
    sqlcmd.Parameters.Add("@c1", SqlDbType.VarChar, 1000);
    //Get here out parameter like below
    sqlcmd.Parameters["@c1"].Direction = ParameterDirection.Output;
    sqlcmd.ExecuteNonQuery();
    i = sqlcmd.Parameters["@c1"].Value.ToString();
    da = new SqlDataAdapter(sqlcmd);
    Session["query"]= i.ToString(); //here i store that query in session
    sqlcon.Close();
    }
    }

    Regards
    N.Ravindran
    Your Hard work never fails

  • #695181
    I have to return a query from sp and assign that to session.This sample query.Original one is large one having inner join on many table
    create Procedure Sample
    (
    @id int
    )
    as
    begin
    declare @pid varchar(10)
    declare @query varchar(500)

    set @pid = cast(@id as varchar(10))

    set @query ='select * from Table1 where id = '+@pid+''
    --Execute sp_executesql @query
    --print @query
    select @query
    end

    Then add this type of code in your C# to get value of this OUTPUT parameter from stored procedure:

    System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand("Sample", cn);
    cmd.CommandType = CommandType.StoredProcedure;
    System.Data.SqlClient.SqlParameter parm = new System.Data.SqlClient.SqlParameter("@ID", SqlDbType.Int);
    parm.Value = 1;
    parm.Direction = ParameterDirection.Input;
    cmd.Parameters.Add(parm);
    System.Data.SqlClient.SqlParameter parm2 = new System.Data.SqlClient.SqlParameter("@SQLQuery", SqlDbType.VarChar);
    parm2.Size = 50;
    parm2.Direction = ParameterDirection.Output; // This is important!
    cmd.Parameters.Add(parm2);
    cn.Open();
    cmd.ExecuteNonQuery();
    cn.Close();

    // Print the output value
    Console.WriteLine(cmd.Parameters["@SQLQuery"].Value);
    Console.ReadLine();
    }

  • #695205
    Create a Output parameter and then set the value in this Output parameter.

    the final statement must be RETURN @Outputparamtername so that you can read the value in code behind.


    ALTER PROCEDURE getQuery
    (
    @qry varchar(5000) OUTPUT
    )
    AS
    SELECT @Qry = 'ur Query'
    RETURN @qry

    Thanks & Regards
    Anil Kumar Pandey
    Microsoft MVP, DNS MVM

  • #695277
    Thanks guys for ur help.It is working properly now.


  • This thread is locked for new responses. Please post your comments and questions as a separate thread.
    If required, refer to the URL of this page in your new post.