stored procedure Output parameter value is Null
-
Hi I am using following stored procedure ----------------------------------- set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[GetUserControlTemplateCode] @product_id int, @genmask_code nvarchar(max) output AS BEGIN SET NOCOUNT ON; Select dbo.cp_product.product_type, dbo.cp_genmask.genmask_code from dbo.cp_genmask inner join dbo.cp_product on dbo.cp_genmask.genmask_product_type=dbo.cp_product.product_type where dbo.cp_product.product_id=@product_id END -------------------------------- when I execute the above code using exec GetUserControlTemplateCode 1,'x' I get correct values for x i.e. output parameter. My code behind to call stored procedure is as follows --------------------------------- protected void Page_Load(object sender, EventArgs e) { SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["sConnectionString2"].ConnectionString); SqlCommand Com = con.CreateCommand(); Com.CommandType = CommandType.StoredProcedure; Com.CommandText = "GetUserControlTemplateCode"; //Create parameter object to provide input SqlParameter parInput = Com.Parameters.Add("@product_id", SqlDbType.Int); parInput.Direction = ParameterDirection.Input; parInput.Value = nProductID; // Create parameter to hold output SqlParameter parOutput = Com.Parameters.Add("@genmask_code", SqlDbType.NVarChar,300); parOutput.Direction = ParameterDirection.Output; //Open the connection con.Open(); //Execute command try { Com.ExecuteNonQuery(); } catch (Exception ex) { Response.Write(ex.ToString()); } string sTemplateCode = Convert.ToString(parOutput.Value); sTemplateCode = sTemplateCode.Replace("ProductID", nProductID.ToString()); Control myControl = ParseControl(sTemplateCode); PlaceHolderForProducts.Controls.Add(myControl); con.Close(); } ----------------------------------- when I execute the code, i do not get any exception, but the value of output parameter is null. Can someone please help me to understand whats missing here? Thanks.
-
Hi I am using following stored procedure ----------------------------------- set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[GetUserControlTemplateCode] @product_id int, @genmask_code nvarchar(max) output AS BEGIN SET NOCOUNT ON; Select dbo.cp_product.product_type, dbo.cp_genmask.genmask_code from dbo.cp_genmask inner join dbo.cp_product on dbo.cp_genmask.genmask_product_type=dbo.cp_product.product_type where dbo.cp_product.product_id=@product_id END -------------------------------- when I execute the above code using exec GetUserControlTemplateCode 1,'x' I get correct values for x i.e. output parameter. My code behind to call stored procedure is as follows --------------------------------- protected void Page_Load(object sender, EventArgs e) { SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["sConnectionString2"].ConnectionString); SqlCommand Com = con.CreateCommand(); Com.CommandType = CommandType.StoredProcedure; Com.CommandText = "GetUserControlTemplateCode"; //Create parameter object to provide input SqlParameter parInput = Com.Parameters.Add("@product_id", SqlDbType.Int); parInput.Direction = ParameterDirection.Input; parInput.Value = nProductID; // Create parameter to hold output SqlParameter parOutput = Com.Parameters.Add("@genmask_code", SqlDbType.NVarChar,300); parOutput.Direction = ParameterDirection.Output; //Open the connection con.Open(); //Execute command try { Com.ExecuteNonQuery(); } catch (Exception ex) { Response.Write(ex.ToString()); } string sTemplateCode = Convert.ToString(parOutput.Value); sTemplateCode = sTemplateCode.Replace("ProductID", nProductID.ToString()); Control myControl = ParseControl(sTemplateCode); PlaceHolderForProducts.Controls.Add(myControl); con.Close(); } ----------------------------------- when I execute the code, i do not get any exception, but the value of output parameter is null. Can someone please help me to understand whats missing here? Thanks.
I did not see the statement setting value for @genmask_code so infact, it have no return value. Also I think exec GetUserControlTemplateCode 1,'x' will not work because of lacking OUTPUT modifier. It should be:
exec GetUserControlTemplateCode 1, @x OUTPUT print @x -
Hi I am using following stored procedure ----------------------------------- set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[GetUserControlTemplateCode] @product_id int, @genmask_code nvarchar(max) output AS BEGIN SET NOCOUNT ON; Select dbo.cp_product.product_type, dbo.cp_genmask.genmask_code from dbo.cp_genmask inner join dbo.cp_product on dbo.cp_genmask.genmask_product_type=dbo.cp_product.product_type where dbo.cp_product.product_id=@product_id END -------------------------------- when I execute the above code using exec GetUserControlTemplateCode 1,'x' I get correct values for x i.e. output parameter. My code behind to call stored procedure is as follows --------------------------------- protected void Page_Load(object sender, EventArgs e) { SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["sConnectionString2"].ConnectionString); SqlCommand Com = con.CreateCommand(); Com.CommandType = CommandType.StoredProcedure; Com.CommandText = "GetUserControlTemplateCode"; //Create parameter object to provide input SqlParameter parInput = Com.Parameters.Add("@product_id", SqlDbType.Int); parInput.Direction = ParameterDirection.Input; parInput.Value = nProductID; // Create parameter to hold output SqlParameter parOutput = Com.Parameters.Add("@genmask_code", SqlDbType.NVarChar,300); parOutput.Direction = ParameterDirection.Output; //Open the connection con.Open(); //Execute command try { Com.ExecuteNonQuery(); } catch (Exception ex) { Response.Write(ex.ToString()); } string sTemplateCode = Convert.ToString(parOutput.Value); sTemplateCode = sTemplateCode.Replace("ProductID", nProductID.ToString()); Control myControl = ParseControl(sTemplateCode); PlaceHolderForProducts.Controls.Add(myControl); con.Close(); } ----------------------------------- when I execute the code, i do not get any exception, but the value of output parameter is null. Can someone please help me to understand whats missing here? Thanks.
Your stored procedure returns a set of data. The result set contains two columns, product_type and genmask_code. Use
ExecuteReader()or use aDataAdapterto get the information out, or change the stored procedure. To populate the@genmask_codeoutput parameter you can do something like this:SELECT @genmask_code = dbo.cp_genmask.genmask_code
FROM ...NOTE: You should only do this if you expect the
SELECTstatement to retrieve only one row.
Scottish Developers events: * .NET debugging, tracing and instrumentation by Duncan Edwards Jones and Code Coverage in .NET by Craig Murphy * Developer Day Scotland: are you interested in speaking or attending? My: Website | Blog
-
Your stored procedure returns a set of data. The result set contains two columns, product_type and genmask_code. Use
ExecuteReader()or use aDataAdapterto get the information out, or change the stored procedure. To populate the@genmask_codeoutput parameter you can do something like this:SELECT @genmask_code = dbo.cp_genmask.genmask_code
FROM ...NOTE: You should only do this if you expect the
SELECTstatement to retrieve only one row.
Scottish Developers events: * .NET debugging, tracing and instrumentation by Duncan Edwards Jones and Code Coverage in .NET by Craig Murphy * Developer Day Scotland: are you interested in speaking or attending? My: Website | Blog
Colin Angus Mackay wrote:
NOTE: You should only do this if you expect the SELECT statement to retrieve only one row.
You won't get an error if it does retrieve more than one row, but if it does, you'll simply get the value from the last row returned by the query. Unless you put an
ORDER BYclause in, however, which row that is will be unpredictable. If no rows are returned, the value of the variable will be unchanged. Stability. What an interesting concept. -- Chris Maunder -
Colin Angus Mackay wrote:
NOTE: You should only do this if you expect the SELECT statement to retrieve only one row.
You won't get an error if it does retrieve more than one row, but if it does, you'll simply get the value from the last row returned by the query. Unless you put an
ORDER BYclause in, however, which row that is will be unpredictable. If no rows are returned, the value of the variable will be unchanged. Stability. What an interesting concept. -- Chris MaunderThanks for your replies, I changed my stored procedure as follows to assign the value to output variable: ALTER PROCEDURE [dbo].[GetUserControlTemplateCode] @product_id int, @genmask_code nvarchar(max) output AS BEGIN SET NOCOUNT ON; Select dbo.cp_product.product_type, @genmask_code=dbo.cp_genmask.genmask_code from dbo.cp_genmask inner join dbo.cp_product on dbo.cp_genmask.genmask_product_type=dbo.cp_product.product_type where dbo.cp_product.product_id=@product_id END when I execute this I get following error: A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations. where should I do data assignment operation?
-
Thanks for your replies, I changed my stored procedure as follows to assign the value to output variable: ALTER PROCEDURE [dbo].[GetUserControlTemplateCode] @product_id int, @genmask_code nvarchar(max) output AS BEGIN SET NOCOUNT ON; Select dbo.cp_product.product_type, @genmask_code=dbo.cp_genmask.genmask_code from dbo.cp_genmask inner join dbo.cp_product on dbo.cp_genmask.genmask_product_type=dbo.cp_product.product_type where dbo.cp_product.product_id=@product_id END when I execute this I get following error: A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations. where should I do data assignment operation?
I changed the code as follows ALTER PROCEDURE [dbo].[GetUserControlTemplateCode] @product_id int, @genmask_code nvarchar(max) output AS BEGIN SET NOCOUNT ON; Declare @product_type int Select b.product_type, @genmask_code=dbo.cp_genmask.genmask_code from dbo.cp_genmask as a inner join dbo.cp_product as b on a.genmask_product_type=b.product_type where b.product_id=@product_id END I executed this code using using exec GetUserControlTemplateCode 1, @genmask_code output print @genmask_code It gives me message as command executed successfully, but I can not see any data in output window. Again when I call this procedure from code behind code it shows null value... Please suggest where I am wrong.