Stored Procedure Output Parameter Inside Select...
Does anyone know how can I (or can I) use a stored procedure output parameter(s) inside Select statement. For example
Select abc, cde, 'xyz' = Case
When 'aaa' then {output parameter of my stored procedure with 'aaa' as input parameter}
When 'bbb' then {output parameter of my stored procedure with 'bbb' as input parameter}
end
from MyTable
Thanks
Arcady
View Complete Forum Thread with Replies
Related Forum Messages:
SELECT Query Stmt Inside Stored Procedure
Friends, What are the possible usuages of a SELECT query stmt inside a stored procedure ?? How can we process the results of the SELECT query other than for documentation/Reporting purposes(Correct me if i'm wrong in this) ?? can any one throw some lite on this .. Thanks, SqlPgmr
View Replies !
Stored Procedure && Output Parameter
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?
View Replies !
Stored Procedure Output Parameter
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
View Replies !
Output Parameter Of A Stored Procedure
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; The code of the first procedur is : Create Procedure dbo.Insert_SRegister @YGroup VarChar (3), @YDate VarChar (4), @YTerm Char(1), @SName VarChar(30), @SSecondName VarChar(30), @SFirstName VarChar(30), @SGender TinyInt, @SBDate DateTime, @SBPlace VarChar(50), @SOCountry VarChar(50), @SOPassCount VarChar(50), @PassNo VarChar(50), @SOLang VarChar(50), @MOLang VarChar(50), @MName VarChar(50), @MHAdd VarChar(50), @MHT1 VarChar(20), @MHT2 VarChar(20), @MHT3 VarChar(20), @MHMob VarChar(20), @MWAdd VarChar(50), @MWT1 VarChar(20), @MWT2 VarChar(20), @MExt VarChar(10), @MWMob VarChar(20), @FName VarChar(50), @FHAdd VarChar(50), @FHT1 VarChar(20), @FHT2 VarChar(20), @FHT3 VarChar(20), @FHMob VarChar(20), @FWAdd VarChar(50), @FWT1 VarChar(20), @FWT2 VarChar(20), @FExt VarChar(10), @FWMob VarChar(20), @EAdd VarChar(50), @ETele VarChar(20), @SchName VarChar(50), @SAdd VarChar(50), @FBNo Smallint, @FSNo Smallint, @FONo VarChar(50), @EMedical VarChar(1000), @FDetails VarChar(1000), @FRDetails VarChar(1000), @PName VarChar(50), @StudentStatus VarChar(50) OUTPUT, @ID VarChar (50) OUTPUT 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 End
View Replies !
Output Parameter In Stored Procedure
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. any one knows the way?
View Replies !
OUTPUT Parameter From Stored Procedure
Hi 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 Thanks VENU
View Replies !
Help! !SQL Stored Procedure Output Parameter.
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. Code: CSTRING strSQL; strSQL.Format("BEGIN EQUIPMENT_UTILITIES.MOVE_EQUIPMENT('%s', '%s', '%d'); END;",(LPCSTR)lpEQNum,(LPCSTR)lpMoveTo, result); <-Failing at '%d' result. Thanks.
View Replies !
How To Use A Stored Procedure Inside Select Query (sql Server Version 8)
Hi, Please help me in this problem... i am new to sql server.. i am using sql server version 8...(doesnot support function with retun values..) so i have created a procedure... -----------procedure------------------(to find next monday after 6 months)------------------- [code] create proc next_Monday ( @myDate DATETIME ) as BEGIN set @myDate = dateadd(mm, 6, @myDate) while datepart(dw,@myDate) <> 2 begin set @myDate = dateadd(dd, 1, @myDate) end select @myDate end go [/code] -------------------------------------------------------- i can able to execute this procedure separately.... working well... but don't know how to call it inside another query.... the following throws error.... select smaster.sname, smaster.Datex, 'xxx'=(execute next_monday smaster.Datex) from smaster please help me... how to fix this problem...
View Replies !
Parsing The Output Parameter Of A Stored Procedure
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
View Replies !
Stored Procedure Output Parameter Problem
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?
View Replies !
Return Output Parameter From Stored Procedure?
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
View Replies !
Retrieve Output Parameter From Stored Procedure
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
View Replies !
NText To Stored Procedure Output Parameter
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
View Replies !
Value Of Stored Procedure Output Parameter Is Blank
Here's my stored procedure... 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.
View Replies !
Value Of Stored Procedure Output Parameter Is Blank
Here's my stored procedure... --------------------------------------------------------- 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 = .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 If --------------------------------------------------------- The 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.
View Replies !
How To Execute A Stored Procedure With Output Parameter
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 ? thanks a lot.
View Replies !
Creating Stored Procedure With Output Parameter
Hi 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'. Any help is highly appreciated. Thanks
View Replies !
Stored Procedure Output Parameter Issue
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 ; }
View Replies !
Reading An OUTPUT Parameter From A Stored Procedure Into A Variable
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
View Replies !
Problem With Output Parameter From Stored Procedure Using SqlDataSource
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 SELECT @iOutput = 1 END CODE BEHIND (abbreviated)protected void btnCompany_Click(object sender, EventArgs e) { dsCompany.InsertParameters["sCompany"].DefaultValue = txtCompany.Text; dsCompany.Insert(); txtCompany.Text = ""; txtCompany.Focus(); } protected void dsCompany_Inserted(object sender, SqlDataSourceStatusEventArgs e) { if ((int)e.Command.Parameters["@iOutput"].Value > 0) { lblMessage.Text = "Insert successful."; } else { lblMessage.Text = "Insert failed. Duplicate record would be created."; } } protected void dsCompany_Deleted(object sender, SqlDataSourceStatusEventArgs e) { if ((int)e.Command.Parameters["@iOutput"].Value > 0) { lblMessage.Text = "Delete successful."; } else { lblMessage.Text = "Delete failed. Other records use this value."; } } protected void dsCompany_Updated(object sender, SqlDataSourceStatusEventArgs e) { if ((int)e.Command.Parameters["@iOutput"].Value > 0) { lblMessage.Text = "Update successful."; } else { lblMessage.Text = "Update failed. Duplicate record would be created."; } } protected void gvCompany_RowEditing(object sender, GridViewEditEventArgs e) { lblMessage.Text = ""; } ASPX (abbreviated)<asp:SqlDataSource ID="dsCompany" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" DeleteCommand="CompanyDelete" DeleteCommandType="StoredProcedure" InsertCommand="CompanyInsert" InsertCommandType="StoredProcedure" SelectCommand="CompanySelect" SelectCommandType="StoredProcedure" UpdateCommand="CompanyUpdate" UpdateCommandType="StoredProcedure" OnInserted="dsCompany_Inserted" OnDeleted="dsCompany_Deleted" OnUpdated="dsCompany_Updated"> <DeleteParameters> <asp:Parameter Name="iCompanyId" Type="Int32" /> <asp:Parameter Direction="Output" Name="iOutput" Type="Int32" Size="8" DefaultValue="0"/> </DeleteParameters> <UpdateParameters> <asp:Parameter Name="iCompanyId" Type="Int32" /> <asp:Parameter Name="sCompany" Type="String" /> <asp:Parameter Direction="Output" Name="iOutput" Type="Int32" Size="8" DefaultValue="0" /> </UpdateParameters> <SelectParameters> <asp:Parameter DefaultValue="1" Name="iZero" Type="Int32" /> </SelectParameters> <InsertParameters> <asp:Parameter Name="sCompany" Type="String" /> <asp:Parameter Direction="Output" Name="iOutput" Type="Int32" Size="8" DefaultValue="0" /> </InsertParameters> </asp:SqlDataSource>
View Replies !
How To Get The Output Parameter From An Internally Called Stored Procedure???
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
View Replies !
Stored Procedure Output Parameter's Issue In Dataaccess Layer
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 ;}
View Replies !
How To Assign String Value To TEXT Output Parameter Of A Stored Procedure?
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
View Replies !
Retrieve An Output Parameter In Stored Procedure From Java Application
in my java application I've made a call to this stored procedureCREATE procedure pruebaICM@pANI varchar(20),@pTABLA varchar(20),@pInsert varchar(500),@pUpdate varchar(1000),@pFLAG varchar(1),@pResultado int OUTPUTasbeginDECLARE @ani varchar(20)declare @cliente intDECLARE @sentencia nvarchar(1000)DECLARE @tabla nvarchar(20)DECLARE @sentencia_where nvarchar(50)DECLARE @sql nvarchar(1050)SET NOCOUNT ONset @tabla = @pTABLAset @ani = @pANISELECT @sql= N'select @cliente=count(ani) from '+ @tabla + N' whereani = ' + @aniexec sp_executesql @sql, N'@Cliente INT OUTPUT', @Cliente OUTPUTSELECT @Clienteif (@pFLAG = 'A') or (@pFLAG = 'Actualizar') or (@pFLAG = 'I')beginif (@cliente = 0)beginset @sentencia = N'insert into ' +@pTABLA + N' values (' + @pInsert + N')'EXEC sp_executesql @sentenciaset @pResultado = 1SELECT @pResultadoreturn @pResultadoendif (@cliente = 1)beginset @sentencia = N'update ' + @pTABLA +N' set ' + @pUpdateset @sentencia_where = N' where ANI =' + @pANIset @sql = @sentencia +@sentencia_whereEXEC sp_executesql @sqlset @pResultado = 2SELECT @pResultadoreturn @pResultadoendendelse if (@pFLAG = 'B') or (@pFLAG = 'Borrar')beginif (@cliente = 0)beginset @pResultado = 0SELECT @pResultadoreturn @pResultadoendelse if (@cliente = 1)beginset @sentencia = N'delete from '+@pTABLA + N' where ANI = ' + @pANIEXEC sp_executesql @sentenciaset @pResultado = 3SELECT @pResultadoreturn @pResultadoendendEXEC sp_cursorcloseendMy problem is that the ouutput param @pResultado haven't got any valueand don't return anything to the java application. How can I fix thisproblem?Thanka very much for helping me!!!!
View Replies !
How To Display Return Value From Stored Procedure Output Parameter In Query Analyzer
I tried to display return value from stored procedure output parameter in Query Analyzer, but I do not know how to do it. Below is my stored procedure: CREATE PROCEDURE UserLogin ( @Email nvarchar(100), @Password nvarchar(50), @UserName nvarchar(100) OUTPUT ) AS SELECT @UserName = Name FROM Users WHERE Email = @Email AND Password = @Password GO If I run the query in Query Analyzer directly, I can display @UserName: DECLARE @UserName as nvarchar(100) SELECT @UserName = Name FROM Users WHERE Email = @Email AND Password = @Password Select @UserName But how can I display @UserName if I call stored procedure: exec UserLogin 'email', 'password', '' I believed it return int, right? Thanks in advance for help.
View Replies !
Calling A Stored Procedure That Inserts Records And Generates An Output Parameter
I will be calling a stored procedure in SQL Server from SSIS. The stored procedure inserts records in a table by accepting input parameters. In the process, it also generates an output parameter that it passes as part of the parameters defined inside the stored procedure. The output parameter value acts as the primary key value for the record inserted using the stored procedure. How can I call this stored procedure in SSIS? This is just one of the n steps as I will be extracting the output parameter generated by this stored procedure for the succeeding steps.
View Replies !
Stored Procedure With CURSOR OUTPUT Parameter, Using JDBC And A Callable Statement
My server is MS Sql Server 2005. I'm using com.microsoft.sqlserver.jdbc.SQLServerDriver as the driver class. I've established a connection to the database. I'm trying to invoke a stored procedure using JDBC and a callable statement. The stored procedure has a parameter @CurOut CURSOR VARYING OUTPUT. How do I setup the callable statement so the output parameter is accepted by the driver? I'm not really trying to pass a cursor up to the database Server but I'm wanting a cursor back from the stored procedure that is other than the result set or other value the stored procedure returns. First problem: What java.sql.Types (or SQL Server specific) value do I specify for the out parameter I'm registering on the CallableStatement? Second problem: What do I set the value of the parameter to? The code looks like: CallableStatement cstmt = myConnection.prepareCall(sQuery); cstmt.registerOutParameter(1, Types.OTHER); // What is the right type? cstmt.setNull(1, Types.OTHER); // What is the right type? if (cstmt.execute()) { ResultSet rs = cstmt.getResultSet(); } Execution results in a NullPointerException from the driver. What am I doing wrong? Thanks for your assistance. Jon Weaver
View Replies !
SQL Server 2005 JDBC Driver 1.1 Truncate Output Parameter From A Stored Procedure To 4000 Char.
I have a procedure that uses varchar(max) as output parameter, when I tried to retrieve the result of calling the procedure, the result is truncated to 4000 character. Is this a driver bug? Any workaround? Here is the pseudo code: create Procedure foo(@output varchar(max)) { set @foo = 'string that has more than 4000 characters...'; return; } Java code: CallableStatement cs = connection.prepareCall("{call foo ?}"); cs.registerOutputParameter(1, Types.longvarchar); // also tried Types.CLOB. cs.execute(); String result = cs.getString(1); // The result is truncated to 4000 char. -- Also tried CLOB clob = cs.getClob(1); long len = clob.length(); // The result is 4000. Thanks, Eric Wang
View Replies !
Execute SQL Task – Output Parameter On Stored Procedure Causes Task To Fail.
I have a SQL Task that calls a stored procedure and returns an output parameter. The task fails with error "Value does not fall within the expected range." The Stored Procedure is defined as follows: Create Procedure [dbo].[TestOutputParms] @InParm INT , @OutParm INT OUTPUT as Set @OutParm = @InParm + 5 The task uses an OLEDB connection and has a source type of Direct Input. The SQL Statement is Exec TestOutputParms 7, ? output The parameter mapping is: Variable Name Direction Data Type Parameter Name User::OutParm Output LONG @OutParm
View Replies !
Differentiate Between Whether Stored Procedure A Is Executed Inside Query Analyzer Or Executed Inside System Application Itself.
Just wonder whether is there any indicator or system parameters that can indicate whether stored procedure A is executed inside query analyzer or executed inside application itself so that if execution is done inside query analyzer then i can block it from being executed/retrieve sensitive data from it? What i'm want to do is to block someone executing stored procedure using query analyzer and retrieve its sensitive results. Stored procedure A has been granted execution for public user but inside application, it will prompt access denied message if particular user has no rights to use system although knew public user name and password. Because there is second layer of user validation inside system application. However inside query analyzer, there is no way control execution of stored procedure A it as user knew the public user name and password. Looking forward for replies from expert here. Thanks in advance. Note: Hope my explaination here clearly describe my current problems.
View Replies !
Using Output From A Stored Procedure As An Output Column In The OLE DB Command Transformation
I am working on an OLAP modeled database. I have a Lookup Transformation that matches the natural key of a dimension member and returns the dimension key for that member (surrogate key pipeline stuff). I am using an OLE DB Command as the Error flow of the Lookup Transformation to insert an "Inferred Member" (new row) into a dimension table if the Lookup fails. The OLE DB Command calls a stored procedure (dbo.InsertNewDimensionMember) that inserts the new member and returns the key of the new member (using scope_identity) as an output. What is the syntax in the SQL Command line of the OLE DB Command Transformation to set the output of the stored procedure as an Output Column? I know that I can 1) add a second Lookup with "Enable memory restriction" on (no caching) in the Success data flow after the OLE DB Command, 2) find the newly inserted member, and 3) Union both Lookup results together, but this is a large dimension table (several million rows) and searching for the newly inserted dimension member seems excessive, especially since I have the ID I want returned as output from the stored procedure that inserted it. Thanks in advance for any assistance you can provide.
View Replies !
Calling A Stored Procedure Inside Another Stored Procedure (or &"nested Stored Procedures&")
Hi all - I'm trying to optimized my stored procedures to be a bit easier to maintain, and am sure this is possible, not am very unclear on the syntax to doing this correctly. For example, I have a simple stored procedure that takes a string as a parameter, and returns its resolved index that corresponds to a record in my database. ie exec dbo.DeriveStatusID 'Created' returns an int value as 1 (performed by "SELECT statusID FROM statusList WHERE statusName= 'Created') but I also have a second stored procedure that needs to make reference to this procedure first, in order to resolve an id - ie: exec dbo.AddProduct_Insert 'widget1' which currently performs:SET @statusID = (SELECT statusID FROM statusList WHERE statusName='Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID) I want to simply the insert to perform (in one sproc): SET @statusID = EXEC deriveStatusID ('Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID) This works fine if I call this stored procedure in code first, then pass it to the second stored procedure, but NOT if it is reference in the second stored procedure directly (I end up with an empty value for @statusID in this example). My actual "Insert" stored procedures are far more complicated, but I am working towards lightening the business logic in my application ( it shouldn't have to pre-vet the data prior to executing a valid insert). Hopefully this makes some sense - it doesn't seem right to me that this is impossible, and am fairly sure I'm just missing some simple syntax - can anyone assist?
View Replies !
Code Inside! --&> How To Return The @@identity Parameter Without Using Stored Procedures
Hi.here is my code with my problem described in the syntax.I am using asp.net 1.1 and VB.NETThanks in advance for your help.I am still a beginner and I know that your time is precious. I would really appreciate it if you could "fill" my example function with the right code that returns the new ID of the newly inserted row. Public Function howToReturnID(ByVal aCompany As String, ByVal aName As String) As Integer 'that is the variable for the new id.Dim intNewID As Integer Dim strSQL As String = "INSERT INTO tblAnfragen(aCompany, aName)" & _ "VALUES (@aCompany, @aName); SELECT @NewID = @@identity" Dim dbConnection As SqlConnection = New SqlConnection(connectionString)Dim dbCommand As SqlCommand = New SqlCommand()dbCommand.CommandText = strSQL 'Here is my problem.'What do I have to do in order to add the parameter @NewID and'how do I read and return the value of @NewID within that function howToReturnID'any help is greatly appreciated!'I cannot use SPs in this application - have to do it this way! :-( dbCommand.Parameters.Add("@aFirma", aCompany.Trim)dbCommand.Parameters.Add("@aAnsprAnrede", aName.Trim) dbCommand.Connection = dbConnection TrydbConnection.Open()dbCommand.ExecuteNonQuery() 'here i want to return the new ID!Return intNewID Catch ex As Exception Throw New System.Exception("Error: " & ex.Message.ToString()) Finally dbCommand.Dispose()dbConnection.Close()dbConnection.Dispose() End Try End Function
View Replies !
Stored Procedure Using A Parameter Default To Select &"ALL&" Records - Help Please
Hi everyone, I have created a stored procedure in sql server with parameters for my c# application. Wanted to know is there anyway to set the default value for @searchpostcode to select all the records? Right now it brings the records based on the postcode specified .(I have dropdownlist in my c# application that passes the parameters for postcode) My stored procedure: CREATE PROCEDURE sp_accepting_practice (@searchpostcode as nvarchar(100)) AS SELECT dbo.tbdentists.Title, dbo.tbdentists.FirstName, dbo.tbdentists.Surname, dbo.tbpractices.PracticeName, dbo.tbpractices.PracticeAddress1, dbo.tbpractices.PracticeAddress2, dbo.tbpractices.Town, dbo.tbpractices.Postcode, dbo.tbpractices.Phone, dbo.tbdentistspractices.ListNo, dbo.tbtreatment.treatmentNatureFROM dbo.tbdentists INNER JOIN dbo.tbdentistspractices ON dbo.tbdentists.DentistId = dbo.tbdentistspractices.DentistId INNER JOIN dbo.tbpractices ON dbo.tbdentistspractices.PracticeId = dbo.tbpractices.PracticeId AND dbo.tbdentistspractices.PracticeId = dbo.tbpractices.PracticeId INNER JOIN dbo.tbtreatment ON dbo.tbdentistspractices.TreatmentId = dbo.tbtreatment.treatmentIdWHERE dbo.tbpractices.Postcode LIKE '%' + @searchpostcode + '%'ORDER BY dbo.tbpractices.PracticeId EXECUTE sp_accepting_practice G4GO I greatly appreciate your help. Thanks in Advance Regards Shini
View Replies !
How Do I Get A Procedure OUTPUT-parameter...
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... this is the sp: CREATE PROCEDURE spInsertNews @uidArticleId uniqueidentifier OUTPUT, @strHeading nvarchar(300), @strAbstract nvarchar(600), @strText nvarchar(4000), @dtDate datetime, @dtDateStart datetime, @dtDateStop datetime, @strAuthor nvarchar(200), @strAuthorEmail nvarchar(200), @strKeywords nvarchar(400) AS SET @uidArticleId = newid() INSERT INTO tblArticles VALUES(@uidArticleId ,@strHeading,@strAbstract,@strText,@dtDate,@dtDateStart,@dtDateStop,@strAuthor,@strAuthorEmail,@strKeywords) my asp code is something like this: ... SqlCommand sqlcmdInsertNewsArticle = new SqlCommand(insertCmd, sqlconCon); sqlcmdInsertNewsArticle.Parameters.Add(new SqlParameter("@strHeading", SqlDbType.NVarChar, 300)); sqlcmdInsertNewsArticle.Parameters["@strHeading"].Value = strHeading.Text; sqlcmdInsertNewsArticle.Parameters.Add(new SqlParameter("@strAbstract", SqlDbType.NVarChar, 600)); sqlcmdInsertNewsArticle.Parameters["@strAbstract"].Value = strAbstract.Text; sqlcmdInsertNewsArticle.Parameters.Add(new SqlParameter("@strText", SqlDbType.NVarChar, 4000)); sqlcmdInsertNewsArticle.Parameters["@strText"].Value = strText.Text; ... sqlcmdInsertNewsArticle.Connection.Open(); sqlcmdInsertNewsArticle.ExecuteNonQuery(); sqlcmdInsertNewsArticle.Connection.Close(); How do I do if I want to catch the OUTPUT-parameter (@uidArticleId)? anyone?
View Replies !
Multiple Output Parameter From Procedure
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
View Replies !
Creating A Store Procedure With Output Parameter
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.
View Replies !
Output Parameter Not Fetched From Dynamic T-SQL Procedure
I have a STORED PROC for dynamic T-SQL that returns a OUTPUT varaible of typevarchar . This works fine in query analyzer. But when I try to get values in asp.net page it does not return anything. No execeptions or errors also. Below is the code snippet HDConn = CommonMethods.BuildConnection();SqlCommand cmd1 = new SqlCommand("GenerateRowids",HDConn);cmd1.CommandType = CommandType.StoredProcedure;// add parameters for Proccmd1.Parameters.Add("@TableName",SqlDbType.VarChar,50).Value= "MetricsMaster";cmd1.Parameters.Add("@ColumnName",SqlDbType.VarChar,50).Value= "MetricId";cmd1.Parameters.Add("@rowidval",SqlDbType.VarChar,30);cmd1.Parameters["@rowidval"].Direction = ParameterDirection.Output;try{cmd1.ExecuteNonQuery();//the below line does not print the valueResponse.Write(cmd1.Parameters["@rowidval"].Value.ToString());}My stored proc looks like :CREATE PROCEDURE dbo.GenerateRowids@TableName varchar(50),@ColumnName varchar(50),@rowidval varchar(30) outputASBegindeclare @sql nvarchar(4000), @Idval varchar(50) set @sql = N'select left(MAX(' + @ColumnName +') ,1)+ convert(varchar(5),Cast(SUBSTRING((MAX(' +@ColumnName+ ')),2,(len(MAX(' +@ColumnName+ '))))as int)+1) from ' + @TableNameexec sp_executesql @query = @sql,@params = N'@rowidval varchar OUTPUT', @rowidval = @rowidval OUTPUT ENDGO
View Replies !
{fn CURDATE()} Inside Stored Procedure
Hi,I created the stored procedure in SQL Server 2000CREATE PROCEDURE RsaTestASSELECT {fn CURDATE()}and to my surprise found out that it worked..To my surprise, because all the documentationthat I find suggests that {fn CURDATE()} isan ODBC escape clause..I can understand why/how ODBC escape clauseswork in queries, but not how this would workinside a stored procedure.I created the sp by running the above code inqueryanalyzer. sp_helptext 'RsaTest' shows thatthe {fn CURDATE()} is still in there, so itdoesn't appear to get transformed in to SQLServer specific code by whatever means queryanalyzer uses to connect to the database..Can anyone explain how this works? Or whereto find more info on {fn } syntax inside storedprocedures?Cheers,Rsa
View Replies !
If Statement Inside A Stored Procedure!!!!
hi all, i'm wondering if i can use one stored procedure in too cases, this is the senario: i have stored procedure for admin and another one for user, they have the same select everything is the same except that in admin SP i have where @admin = admin and for user i have where @user = user if there a way to use if and else to make this happen this is what i did so far: CREATE PROCEDURE [test] @admin INT, @user INT, @indexType INT as if @indexType = 1 begin SELECT * FROM table WHERE something IN (SELECT * FROM anothertable where admin = @admin) end else begin SELECT * FROM table WHERE user = @user end GO any suggestion will be very helpful thanks
View Replies !
|
TRACKER
