In my ASP.NET page I use a stored procedure that have a parameter declared as OUTPUT... however...I do not know how to get this OUTPUT to be stored in a ASP.NET-variable...
I have two stored procedures one generates an output parameter that I then use in the second stored procedure. 1 Try2 Dim myCommand As New SqlCommand("JP_GetChildren", myConn)3 myCommand.CommandType = Data.CommandType.StoredProcedure4 5 myCommand.CommandType = Data.CommandType.StoredProcedure6 myCommand.Parameters.Add(New SqlParameter("@ParentRule", Data.SqlDbType.NVarChar))7 myCommand.Parameters.Add(New SqlParameter("@PlantID", Data.SqlDbType.NVarChar))8 myCommand.Parameters.Add(New SqlParameter("@New_ReleasingRulePrefix", Data.SqlDbType.NVarChar))9 myCommand.Parameters.Add(New SqlParameter("@New_ReleasingRuleSuffix", Data.SqlDbType.NVarChar))10 myCommand.Parameters.Add(New SqlParameter("@New_PlantID", Data.SqlDbType.NVarChar))11 myCommand.Parameters.Add(New SqlParameter("@New_RuleSetID", Data.SqlDbType.NVarChar))12 myCommand.Parameters.Add(New SqlParameter("@Count", Data.SqlDbType.Int))13 myCommand.Parameters.Add(New SqlParameter("@IDField", Data.SqlDbType.NVarChar))14 15 Dim OParam As New SqlParameter()16 OParam.ParameterName = "@IDFieldOut" 17 OParam.Direction = ParameterDirection.Output18 OParam.SqlDbType = SqlDbType.NVarChar19 myCommand.Parameters.Add(OParam)20 21 22 myCommand.Parameters("@ParentRule").Value = txtParentRule.Text23 myCommand.Parameters("@PlantID").Value = txtStartingPlantID.Text24 myCommand.Parameters("@New_ReleasingRulePrefix").Value = txtReleaseRuleFromPrefix.Text25 myCommand.Parameters("@New_ReleasingRuleSuffix").Value = txtReleaseRuleFromSuffix.Text26 myCommand.Parameters("@New_PlantID").Value = txtEndingPlantID.Text27 myCommand.Parameters("@New_RuleSetID").Value = txtEndingRuleSetID.Text28 myCommand.Parameters("@Count").Value = 129 myCommand.Parameters("@IDField").Value = " " 30 myCommand.Parameters("@IDFieldOut").Value = 031 32 myCommand.ExecuteNonQuery()33 34 Dim IDField As String = myCommand.Parameters("@IDFieldOut").Value35 If i run this stored procedure in sql it does return my parameter. But when i run this code IDField comes back null. Any ideas
I have a sqlserver stored procedure with multiple output parameter like below. I am not sure how I can read the two output parameters at my VB.net code. I am trying to use ExecuteScalar from VB.net and it is throwing an exception. Procedure:CREATE PROCEDURE dbo.spGetUser @UserLoginId VARCHAR(80), 'E-mail of user @Password VARCHAR(50), 'Password in hash @UserId INT=NULL OUTPUT, 'system generated user id@UserType CHAR(2)=NULL OUTPUT 'type of the user AS BEGIN .......... END VB Code: Inside my business layer class =========================== TryWith objSQLCommand .CommandType = CommandType.StoredProcedure .CommandText = "spGetUser".Parameters.AddWithValue("@UserLoginId", UserLoginId) .Parameters.AddWithValue("@Password", Password).Parameters.AddWithValue("@UserId", UserId) .Parameters.AddWithValue("@UserType", UserType).Parameters("@UserId").Direction = ParameterDirection.Output .Parameters("@UserType").Direction = ParameterDirection.Output End With bolReturn = mobjDataAccess.Execute(objSQLCommand) If bolReturn Then GetDetail = True End If ========================== Inside my dataaccess layer class =========================== Public Function Execute(ByVal objSQLCommand As SqlCommand) As Boolean Execute = False Dim intReturn As Integer ' 0 = Success, Otherwise Error intReturn = 1 TryCall Connect()With objSQLCommand .Connection = mobjConnection .ExecuteScalar() End WithCall Disconnect() If intReturn = 0 Then Execute = TrueEnd If Catch ex As ExceptionCall Disconnect() ex.Source = "Execute" Execute = False End Try End Function
I have a stored procedure that inserts a new record, and returns the ID value for the newly inserted record. The procedure works fine, but I'm unable to get that return value in my code. Here's what I have: IF OBJECT_ID ( 'dbo.dbEvent', 'P') IS NOT NULL DROP PROCEDURE dbEvent GO CREATE PROCEDURE @Name varchar(200) ,@Location varchar(200) AS INSERT INTO Event ( NAME , LOCATION ) VALUES ( NAME , @LOCATION ) SELECT SCOPE_IDENTITY()
And my code behind: public Int64 Insert(SqlConnection Conn) { try { using (SqlCommand Command = new SqlCommand("dbo.dbEvent", Conn)) { Command.CommandType = CommandType.StoredProcedure; Command.Parameters.Add("@ID", ID).Direction = ParameterDirection.Output; Command.Parameters.Add("@NAME", SqlDbType.VarChar, 200).Value = PermitName; Command.Parameters.Add("@LOCATION", SqlDbType.VarChar, 200).Value = Location; if (Conn.State != ConnectionState.Open) Conn.Open(); Command.ExecuteNonQuery(); Int64 _requestId = Convert.ToInt64(Command.Parameters.Add("@ID", SqlDbType.BigInt).Value.ToString()); return _requestId; } I'm getting the error that I have "Too many arguments specified" in my Insert() method. When I test the procedure in Query Analyzer, it works fine and returns the correct value. Any suggestions? Don't I need to declare that return value in my .NET code as an output parameter?
I should validate a USER and if the user is not a valid user it should return a message to the front end application for this I have a procedure like this .
Basically I need to pass an output parameter to the front end application returned by this procedure.
Create procedure test @userid VARCHAR(20) AS Declare c1 cursor AS Select userid from USERS_TBL
Open Cursor C1 FETCH NEXT from C1 INTO @Temp While @fetch_status = 0 BEGIN If @Temp <> @Userid -- IF the USER is not a valid USER ...It should not execute the Futher code(statement) it should exit the procedure by returning the error statement. --- --- --- FETCH NEXT from C1 INTO @Temp END
can we use output parameter with insert query in stored procedure. suppose we insert a record and we want to get the id column of this record which is identity column. we want to get this value in output parameter. ie insert record and get its id column value in output parameter.
I have a stored procedure (named Insert_SRegister) that calls another stored procedure (named: Generate_Student_Code) and a value should be returned from the second to the first one. The program goes like this: The first stored procedure inserts info of students and every student should have his own id which is gentratead by the second stored procedure.
The second sp which generates the id does its job well and the first sp which inserts the info does its job well too, the Problem occurs when the genrated ID is to retrurn from the second sp to the first. I do no not know why it refuses to be transmitted in a correct way it gives no error but the id inserted in the db is always zero and I am sure that the id is generated in correct way, the problem occurs only in the transmission;
As Begin Declare @Exists Int, -- Return Value @StudentCode VarChar(50)
----------------------------------------- -- here I call the second proc and assgin it output to variable --@StudentCode ----------------------------------------------------------------------------------- exec @StudentCode = dbo.Generate_Student_Code @YGroup,@YDate, @StudentCode OUTPUT
----Do work and insert info in db
End
Code of the second stored procedure is: ALTER Procedure dbo.Generate_Student_Code
@YGroup VarChar (3), @YDate VarChar (4), @newID VarChar (50) OUTPUT As Begin set nocount on Declare @Exists int, -- Return Value @Mv varchar(5), @IdLen int If Exists(Select SID From SRegisteration) Begin select @Mv = CAST(CAST(Max(RIGHT(SID, 5))AS int) + 1 AS varchar(50)) From SRegisteration Set @IdLen = DATALENGTH(@Mv)
Set @Mv = case when @IdLen = 1 then '0000' + @Mv when @IdLen = 2 then '000' + @Mv when @IdLen = 3 then '00' + @Mv when @IdLen = 4 then '0' + @Mv else @Mv end
Set @newID = 'S' + '-' + @YGroup + '-' + @YDate + '-' + @Mv ---------Here I return the ID value select @newID -------------------------------------- Select @Exists = 1
End
Else
Begin Select @Exists = 0 Set @newID = 'S' + '-' + @YGroup + '-' + @YDate + '-' + '00001' ---------Here I return the ID value select @newID ------------------ Return @Exists End
I am calling a procedure with two input parameters and one output parameter (third) in the following format. I'm receiving a database error on the return parameter. *result. What is the correct syntax for an output parameter.
Hi, I'm having problems retrieving the output parameter from my stored procedure, i'm getting the following error An SqlParameter with ParameterName '@slideshowid' is not contained by this SqlParameterCollection code as follows: int publicationId = (int) Session["PublicationId"];string title = txtTitle.Text;int categoryid = Convert.ToInt32(dlCategory.SelectedItem.Value);SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);SqlCommand myCommand = new SqlCommand("sp_be_addSlideshow", myConnection);myCommand.CommandType = CommandType.StoredProcedure;myCommand.Parameters.Add(new SqlParameter("@publicationid", SqlDbType.Int));myCommand.Parameters["@publicationid"].Value = publicationId;myCommand.Parameters.Add(new SqlParameter("@title", SqlDbType.NVarChar));myCommand.Parameters["@title"].Value = title;myCommand.Parameters.Add(new SqlParameter("@categoryid", SqlDbType.Int));myCommand.Parameters["@categoryid"].Value = categoryid;myConnection.Open();myCommand.ExecuteNonQuery();string slideshowId = myCommand.Parameters["@slideshowid"].Value.ToString();myConnection.Close(); my stored procedure: CREATE PROCEDURE sp_be_addSlideshow( @publicationid int, @title nvarchar(50), @categoryid int, @slideshowid int = NULL OUTPUT)AS INSERT INTO Slideshow(publicationid,title,slideshowcategoryid,deleted) VALUES (@publicationid,@title,@categoryid,0) SELECT @slideshowid = SCOPE_IDENTITY()GO
I am trying to retrieve the Output value from running a Stored Procedure. How can I retrieve the value into a variable? Below is the source code that I have:1 ' Insert question_text into Question table 2 Dim conn As New SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString) 3 Dim cmd As New SqlCommand 4 Dim QuestionID As Integer 5 6 ' Set connection to DB 7 cmd.Connection = conn 8 9 cmd.CommandText = "usp_QuestionResponse_ins" 10 cmd.CommandType = Data.CommandType.StoredProcedure 11 12 Dim sp1 As SqlParameter = cmd.Parameters.Add(New SqlParameter("@question_id", SqlDbType.Int, 4)).Direction = ParameterDirection.Output 13 cmd.Parameters.Add(New SqlParameter("@assessment_id", AssessmentID)) 14 cmd.Parameters.Add(New SqlParameter("@domain_id", DomainID)) 15 cmd.Parameters.Add(New SqlParameter("@question_text_en", QuestionTextEn)) 16 cmd.Parameters.Add(New SqlParameter("@question_text_sp", QuestionTextSp)) 17 cmd.Parameters.Add(New SqlParameter("@category_en", CategoryEn)) 18 cmd.Parameters.Add(New SqlParameter("@display_order", DisplayOrder)) 19 cmd.Parameters.Add(New SqlParameter("@question_template_id", 3)) 20 QuestionID = sp1.Value 21 cmd.Connection.Open() 22 cmd.ExecuteNonQuery() 23 24 conn.Close() 25 cmd.Dispose() 26
Here is the Stored Procedure that I have: 1 ALTER PROCEDURE usp_QuestionResponse_ins 2 ( 3 @question_id int = null output, 4 @assessment_id int = null, 5 @domain_id int = null, 6 @question_text_en nvarchar(1000) = null, 7 @question_text_sp nvarchar(1000) = null, 8 @category_en nvarchar(500) = null, 9 @display_order smallint = null, 10 @question_template_id int = null 11 ) 12 As 13 BEGIN 14 DECLARE @Err Int 15 16 DECLARE @QuestionID INT 17 DECLARE @ReturnValue INT 18 SET @ReturnValue = 0 19 20 DECLARE @CategoryId int 21 SELECT @CategoryId = NULL 22 23 -- Check to see if an answer has already been inserted (i.e. revisiting) 24 SELECT @CategoryId = category_id FROM category WHERE (category_name = @category_en) AND (active_f = 1) AND (delete_f = 0) 25 IF ( @CategoryId IS NOT NULL ) 26 BEGIN 27 EXEC @ReturnValue = usp_question_ins @question_id OUTPUT, @assessment_id, @domain_id, 2, 1, 'Right', @display_order, 1, 8, @CategoryID, @question_text_en, Null, Null, 'Horizontal', 0, Null, Null, 0, 1, 0 28 SET @QuestionID = @ReturnValue 29 END 30 31 IF ( @QuestionID IS NOT NULL ) 32 BEGIN 33 DECLARE @question_locale_id int 34 EXEC usp_question_locale_ins @QuestionID, 1, @question_text_en, 1, 0 35 EXEC usp_question_locale_ins @QuestionID, 2, @question_text_sp, 1, 0 36 37 END 38 39 RETURN @QuestionID 40 End
What am I doing wrong? How do I properly capture the QuestionID from the SP? Running the SP from command line works fine. Thanks
I am trying to run a stored procedure in a C# class I built for this purpose, and get its output parameter. I get the following error: Exception Details: System.Data.SqlClient.SqlException: Procedure or function 'sp_visitor_add' expects parameter '@visitor_id', which was not supplied.I've pasted the code below. The error occurs on line 24. I don't understand why I am getting this error because I am supplying the "@visitor_id" OUTPUT parameter in line 17. 1 public class SetSession 2 { 3 public object AddVisitor(object webSiteNumber, string sessionId) 4 { 5 string _conString = null; 6 SqlConnection _con = null; 7 SqlCommand _cmdSelect = null; 8 9 _conString = WebConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString; 10 _con = new SqlConnection(_conString); 11 12 _cmdSelect = new SqlCommand("sp_visitor_add", _con); 13 _cmdSelect.CommandType = CommandType.StoredProcedure; 14 15 _cmdSelect.Parameters.Add("@web_site_number", SqlDbType.Int).Value = webSiteNumber; 16 _cmdSelect.Parameters.Add("@session_id", SqlDbType.VarChar, 32).Value = sessionId; 17 _cmdSelect.Parameters.Add("@visitor_id", SqlDbType.Int).Direction = ParameterDirection.Output; 18 19 int result = 0; 20 21 try 22 { 23 _con.Open(); 24 _cmdSelect.ExecuteNonQuery(); 25 result = (int)_cmdSelect.Parameters["@visitor_id"].Value; 26 } 27 finally 28 { 29 _con.Close(); 30 } 31 return result; 32 } 33 } Here is the top of my stored procedure for reference: CREATE PROCEDURE [dbo].[sp_visitor_add] @web_site_number int = NULL, @session_id varchar(50) = NULL, @visitor_id int OUTPUTAS ... Can anyone suggest a solution?
Hi, I am trying to cast the parameter returned from the stored procedure to an int32 type. But it is giving me an error message as " Input string is not in the correct format " my code is int valid = Int32.Parse(command.Parameters["@Valid"].Value.ToString()); the @Valid parameter is an output parameter and is declared as int in the stored procedure.... the command is a DbCommand object does anyone have an idea why this error is happening. Naveen
CREATE PROCEDURE [Get_WirelessProducts_By_Page] @CurrentPage int, @PageSize int, @TotalRecords int output AS --Create a temp table to hold the current page of data --Add and ID column to count the records CREATE TABLE #TempTable ( ID int IDENTITY PRIMARY KEY, ProductID int, ProductCategoryID nvarchar(50), ProductBandwidthKB int, ProductOverridePrice nvarchar(50), UseWirelessOverridePrice bit, DedicationTypeID int, DedicationTypeName nvarchar(50) ) --Fill the temp table with the Customers data INSERT INTO #TempTable ( ProductID, ProductCategoryID, ProductBandwidthKB, ProductOverridePrice, UseWirelessOverridePrice, DedicationTypeID, DedicationTypeName ) SELECT W.ProductID, W.ProductCategoryID, W.ProductBandwidthKB, W.ProductOverridePrice, W.UseWirelessOverridePrice, W.DedicationTypeID, D.DedicationTypeName FROM tblWirelessProducts W INNER JOIN tblDedicationTypes D ON W.DedicationTypeID = D.DedicationTypeID --Create variable to identify the first and last record that should be selected DECLARE @FirstRec int, @LastRec int SELECT @FirstRec = (@CurrentPage - 1) * @PageSize SELECT @LastRec = (@CurrentPage * @PageSize + 1) --Select one page of data based on the record numbers above SELECT ProductID, ProductCategoryID, ProductBandwidthKB, ProductOverridePrice, UseWirelessOverridePrice, DedicationTypeID DedicationTypeName FROM #TempTable WHERE ID > @FirstRec AND ID < @LastRec --Return the total number of records available as an output parameter
SELECT @TotalRecords = COUNT(*) FROM tblWirelessProducts GO Here is the relevant VB Code: Dim parmReturnValue As SqlParameter 'conProducts is already open cmdProducts = New SqlCommand("Get_WirelessProducts_By_Page", conProducts) cmdProducts.CommandType = CommandType.StoredProcedure cmdProducts.Parameters.Add("@CurrentPage", intCurrentPage) cmdProducts.Parameters.Add("@PageSize", dgrdProducts.PageSize) parmReturnValue = cmdProducts.Parameters.Add("@TotalRecords", SqlDbType.int) parmReturnValue.Direction = ParameterDirection.Output conProducts.Open dgrdProducts.DataSource = cmdProducts.ExecuteReader()
If Not IsDBNull(cmdProducts.Parameters("@TotalRecords").Value) then Response.Write(cmdProducts.Parameters("@TotalRecords").Value) End IfThe rows are returned correctly, but the output parameter "@TotalRecords" doesn't return anything. Any ideas what I'm doing wrong? Thanks in advance for your help.
Greetings all! I'm writing a small content management program for a website that I'm putting together. One of the critical aspects of this projects is that users want to be able to cut n' paste from Word in to a FreeTextBox control and save it to the database. Well, we've run in to some problems. These entries are running well over 4000 characters, and I'm going to have to use an ntext datatype in order to the field to accomodate the amount of text they want to post. My input stored procedures work great and it's dumping it well in to the table. But the problem I'm having is with the output. Apparently, you can't pass the values to an output parameter or reference the field directly in a stored procedure. I've scoured the internet and purchased several books just to deal with this issue. Can someone point me in the right direction or offer some examples of getting ntext data out through a stored procedure? I'm in a real squeeze here. TIA Matt
Hi all,Could someone give me an example on how to create and execute the store procedure with using output parameter?In normal, I create the store procedure without using output parameter, and I did it as follow:CREATE PROC NewEmployee (ID int(9), Name Varchar (30), hiredate DateTime, etc...)ASBEGIN //my codeENDGOWhen i executed it, I would said: Execute NewEmployee 123456789, 'peter mailler', getDate(), etc....For output parameter:CREATE PROC NewEmployee (ID int(9), Name Varchar (30), hiredate DateTime, @message Varchar(40) out)ASBEGIN insert into Employee ...... //if error encountered set @message = "Insertion failure"ENDGOExec NewEmployee 123456789, 'peter mailler', getDate(), do I need to input something for the output parameter here?Anyone could give me an example on how to handle the output parameter within the store procedure coz I am not sure how to handle it?Many thanks.
Note: @procName,@strAccount,@intHospital,@patType are passed to this procedure from another procedure Note: The @procname procedure also takes the above parameters and @outDupCheck as output parameter
DECLARE @sqlStr NVARCHAR(500) DECLARE @ParmDefinition NVARCHAR(500) DECLARE @parmINAccount VARCHAR(30),@parmINHospId int , @ParmINpatType varchar(1) DECLARE @parmRET1 int SET @parmINAccount = @strAccount SET @parmINHospId = @intHospital SET @ParmINpatType = @patType
Being a rookie to stored procedures the following cry for help will be posted.
The stored procedure below is supposed to create a dynamic string as an output parameter
create procedure spGetFieldNameList @TableName varchar(256),@String varchar(8000) output As select @String = @String + sc.name + ',' from syscolumns as sc(nolock) join sysobjects as so(nolock) on sc.id = so.id and so.name = @TableName order by colid asc
Creating the stored procedure does not trigger an error.
Calling the stored procedure as: spGetFieldNameList 'OfferCondition'. Causes the following message: Server: Msg 201, Level 16, State 4, Procedure spGetFieldNameList, Line 0 Procedure 'spGetFieldNameList' expects parameter '@String', which was not supplied.
Calling it this way: declare @String as varchar(8000) set @String = '' set @String = spGetFieldNameList 'OfferCondition'.
Causes: Server: Msg 170, Level 15, State 1, Line 3 Line 3: Incorrect syntax near 'OfferCondition'.
Hi, the stored procedure returns only the first letter of the ouput parameter i.e 'e'. spvr_ErrorDescription='error while updating data'. Actually It must return 'error while updating data'. can anybody help me to solve this issue.How to set the size of output parameter in dataaccess layer.
Below is the actual code
public string UserCostCenter_Upd(string spvp_Offering, string spvp_UserId, string spvp_CostCenter, DateTime spvp_StartDate, DateTime spvp_ExpiryDate, ref string spvr_ErrorDescription) { // The instance ds will hold the result set obtained from the DataRequest DataSet ds = new DataSet(); RequestParameter[] parms = new RequestParameter[7]; parms[0] = new RequestParameter("@Return_Value", DbType.Int32, ParameterDirection.ReturnValue, null, true); parms[1] = new RequestParameter("@p_Offering", DbType.AnsiString); parms[1].Value = spvp_Offering; parms[2] = new RequestParameter("@p_UserId", DbType.AnsiStringFixedLength); if (spvp_UserId == null) parms[2].Value = DBNull.Value; else parms[2].Value = spvp_UserId; parms[3] = new RequestParameter("@p_CostCenter", DbType.AnsiString); if (spvp_CostCenter == null) parms[3].Value = DBNull.Value; else parms[3].Value = spvp_CostCenter; parms[4] = new RequestParameter("@p_StartDate", DbType.DateTime); if (spvp_StartDate == null) parms[4].Value = DBNull.Value; else parms[4].Value = spvp_StartDate; parms[5] = new RequestParameter("@p_ExpiryDate", DbType.DateTime); if (spvp_ExpiryDate == null) parms[5].Value = DBNull.Value; else parms[5].Value = spvp_ExpiryDate; parms[6] = new RequestParameter("@r_ErrorDescription", DbType.String, ParameterDirection.InputOutput , null, true); parms[6].Value = spvr_ErrorDescription;
// Create an instance of DataSQLClient
AirProducts.GlobalIT.Framework.DataAccess.DataSqlClient daSQL = new DataSqlClient(Constants.ApplicationName); ; // typDSRef holds the ref. to the strongly typed dataset DataSet typDSRef = (DataSet)ds; DataSet resultDataSet = daSQL.ExecuteDataSet("[ap_UserCostCenter_Upd]", ref parms); // Call DataHelper.MapResultSet function for mapping the result set obtained in ordinary DataSet to strongly typed DataSet DataHelper helper = new DataHelper(); if (!helper.MapResultSet(ref typDSRef, resultDataSet)) ds = null; // Returns a null reference if the DataHelper.MapResultSet is failed. //returnCode = (int)parms[0].Value; spvr_ErrorDescription = (string)((parms[6].Value == DBNull.Value) ? null : parms[6].Value); return spvr_ErrorDescription ; }
Hi guys. I have a procedure with just one parameter. That's a output parameter. It's type is NVARCHAR I think there's no problem with the procedure. Now I want to execute it. Can I declare that variable passed by parameter to the procedure ? Must I use the Execute command ?
How can I declare output parameter in stored procedure, written in C++/CLR? In C# it is proc_name( out SqlInt32 value ). In VB.NET it is proc_name( <Out()> ByRef value As SqlInt32 ). And in C++ ?
Hello, I am struggling with this, having read many msdn articles and posts I am non the wiser. I have a sproc with an output parameter @regemail. I can call the sproc OK from my code as below, via a tableadapter. And the sproc works as desired from management studio express. I want to get the result returned in the OUTPUT parameter (NOT the Return Value) and place it in a variable. Can anyone advise how I can do this? J. THE CODE I USE TO CALL THE SPROC Dim tableAdapter As New DataSet1TableAdapters.RegistrationTableAdaptertableAdapter.SPVerifyUser(strRegGuid, String.Empty) THE STORED PROCEDURECREATE Proc [prs_VerifyUser @regid uniqueidentifier,@regemail nvarchar(250) OUTPUT ASBEGIN IF EXISTS(SELECT RegEmail from dbo.Registration WHERE RegID = @regid) BEGIN SELECT @regemail = RegEmail FROM Registration WHERE RegID = @regid Return 1 END Return 2 END
Hi all,i have a problem and couldnt find anything even close to it. please help me, here is the description of what i m trying to accomplish:I have a trigger that is generating a column value and calling a stored procedure after the value is generated. And this stored procedure is setting this generated value as an output parameter. But my problem is:my asp.net page is only sending an insert parameter to the table with the trigger, trigger is running some code depending on the insert parameter and calling this other stored procedure internally. So basically i m not calling this last stored procedure that sets the output parameter within my web form. How can i get the output parameter in my webform? Everthing is working now, whenever an insert hits the table trigger runs and generates this value and called stored procedure sets it as an output parameter. I can get the output parameter with no problem in query analyzer, so the logic has no problem but i have no idea how this generated output parameter can be passed in my webform since its not initiated there.any help will greately be appreciated, i m sure asp.net and sql server 2000 is powerful and flexible enough to accomplish this but how??-shane
I am having a problem getting the output parameter from a stored procedure that deletes a record. I use the EXACT same process for the insert and update procs and they work fine. For some reason, the delete proc is either not returning an output parameter, or the SqlDataSource (dsCompany) is not able to get the information in the dsCompany_Deleted method. I simply want the proc to return an output value so that when I eventually get around to checking if the record can be deleted due to foreign key restraints, I can deliver the appropriate message to the user.I have highlighted the line that is giving the problem. I also highlighted a default value in the parameter, that when removed causes the app to crash because it is getting a null value back. Thanks in advance to anyone who can help!STORED PROC (eventually I will add foreign key restraints) ALTER PROCEDURE CompanyDelete @iCompanyId int, @iOutput int OUTPUT AS BEGIN DELETE FROM tblCompany WHERE iCompanyId = @iCompanyId
Hi,the stored procedure returns only the first letter of the ouput parameter i.e 'e'. Actually It must return 'error while updating data'. can anybody helpme to solve this issue.How to set the size of output parameter.spvr_ErrorDescription='error while updating data which I get from the output parameter of stored procedure. How to set the size of output parameter in dataaccess layer. Below is the actual code.public string UserCostCenter_Upd(string spvp_Offering,string spvp_UserId,string spvp_CostCenter,DateTime spvp_StartDate,DateTime spvp_ExpiryDate,ref string spvr_ErrorDescription){// The instance ds will hold the result set obtained from the DataRequestDataSet ds = new DataSet();RequestParameter[] parms = new RequestParameter[7];parms[0] = new RequestParameter("@Return_Value", DbType.Int32, ParameterDirection.ReturnValue, null, true);parms[1] = new RequestParameter("@p_Offering", DbType.AnsiString);parms[1].Value = spvp_Offering;parms[2] = new RequestParameter("@p_UserId", DbType.AnsiStringFixedLength);if (spvp_UserId == null)parms[2].Value = DBNull.Value;elseparms[2].Value = spvp_UserId;parms[3] = new RequestParameter("@p_CostCenter", DbType.AnsiString);if (spvp_CostCenter == null)parms[3].Value = DBNull.Value;elseparms[3].Value = spvp_CostCenter;parms[4] = new RequestParameter("@p_StartDate", DbType.DateTime);if (spvp_StartDate == null)parms[4].Value = DBNull.Value;elseparms[4].Value = spvp_StartDate;parms[5] = new RequestParameter("@p_ExpiryDate", DbType.DateTime);if (spvp_ExpiryDate == null)parms[5].Value = DBNull.Value;elseparms[5].Value = spvp_ExpiryDate;parms[6] = new RequestParameter("@r_ErrorDescription", DbType.String, ParameterDirection.InputOutput , null, true);parms[6].Value = spvr_ErrorDescription;// Create an instance of DataSQLClientAirProducts.GlobalIT.Framework.DataAccess.DataSqlClient daSQL = new DataSqlClient(Constants.ApplicationName); ;// typDSRef holds the ref. to the strongly typed datasetDataSet typDSRef = (DataSet)ds;DataSet resultDataSet = daSQL.ExecuteDataSet("[ap_UserCostCenter_Upd]", ref parms);// Call DataHelper.MapResultSet function for mapping the result set obtained in ordinary DataSet to strongly typed DataSetDataHelper helper = new DataHelper();if (!helper.MapResultSet(ref typDSRef, resultDataSet))ds = null; // Returns a null reference if the DataHelper.MapResultSet is failed. //returnCode = (int)parms[0].Value;spvr_ErrorDescription = (string)((parms[6].Value == DBNull.Value) ? null : parms[6].Value);return spvr_ErrorDescription ;}
Hello,I am currently trying to assign some string to a TEXT output parameterof a stored procedure.The basic structure of the stored procedure looks like this:-- 8< --CREATE PROCEDURE owner.StoredProc(@blob_data image,@clob_data text OUTPUT)ASINSERT INTO Table (blob_data, clob_data) VALUES (@blob_data, @clob_data);GO-- 8< --My previous attempts include using the convert function to convert astring into a TEXT data type:SET @clob_data = CONVERT(text, 'This is a test');Unfortunately, this leads to the following error: "Error 409: Theassignment operator operation cannot take a text data type as an argument."Is there any alternative available to make an assignment to a TEXToutput parameter?Regards,Thilo
