Capturing The Output From Store Procedure And Use It
Jan 23, 2004
How do I call capture the output (not return value) from calling a store procedure from within a store procedure so I can use that data for further processing (say join it with another table)?
For example,
CREATE PROCEDURE dbo.sp_test AS
--- returns all words not in Mastery Level 0
EXEC sp_anothertest
--- use the data coming back from sp_test and join it with another table here and say insert them into tblFinalResults
Inside some TSQL programmable object (a SP/or a query in Management Studio)I have a parameter containing the name of a StoreProcedure+The required Argument for these SP. (for example it's between the brackets [])
I can't change thoses SP, (and i don't have a nice output param to cach, as i would need to change the SP Definition)All these SP 'return' a 'select' of 1 record the same datatype ie: NVARCHAR. Unfortunately there is no output param (it would have been so easy otherwise. So I am working on something like this but I 'can't find anything working
CREATE procedure test as select top 10 count (*) as Download, a.item_code as Item_Code, b.item_title as Item_Title into report..ten from tbl_statistics_archive a , shabox..tbl_items b where a.item_class = '10' and convert(char(10),a.created,111) >= convert(char(10),getdate()-7,111) and convert(char(10),a.created,111) <= convert(char(10),getdate()-1,111) and a.item_code = b.item_code and a.item_class = b.item_class group by a.item_code, b.item_title order by 1 desc GO
I have create the table called 'ten', and i schedule this SP, but when i start the job, there was nothing shown on 'ten' table, which part i have been doing wrong, kindly help me.
I'm trying to use a store procedure to add an item into the database and retrieve the id of such item within and output parameter but this is not happening. The item is added into the db but the output param is not modified. Here is the code: SP CREATE procedure dbo.AddItem(@Desc nvarchar(100),@intItemID int output)as insert into RR_Item ( desc ) values ( @Desc ) select @intItemID = SCOPE_IDENTITY()GO I have tried in the last line of the SP select @intItemID = SCOPE_IDENTITY() select @intItemID = @@IDENTITY select @intItemID = max(itemid) from RR_Item but nothing seems to work. I'm calling the store procedure as follows from asp.net: Dim intItemID As New SqlParameter("@intItemID", SqlDbType.Int) intItemID.Direction = ParameterDirection.Output SqlHelper.ExecuteDataset(objConn.ConnectionString, "AddItem", desc, intItemID) MsgBox(intItemID.Value.ToString)
ex: myprocedure(@Cusname varchar(50), @Cusid int output)as Insert into Customer(Cusname) values (@Cusname)SELECT @cusid = @@IDENTITY i add the query to my adapter called CreatCustomer (@Cusnam,@Cusid)private Merp_CusListTableAdapter _CuslistAdapter = null;protected Merp_CusListTableAdapter Adapter { get {if (_CuslistAdapter == null) _CuslistAdapter = new Merp_CusListTableAdapter();return _CuslistAdapter; } } Now how i write function in BLL to receive output paramter from creatcustomer function?
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.
My store procedure get the QuestionID (PK) from the page and then it's to return a few varchars but gives me the error that string can't be converted to int. ALTER PROCEDURE [dbo].[usp_getQuestionsforEditPopulateText]@QuestionID int,@QuestionDescription varchar(MAX) OUTPUT,@Option1 varchar(50) OUTPUT,@Option2 varchar(50) OUTPUT,@Option3 varchar(50) OUTPUT,@Option4 varchar(50) OUTPUT,@Option5 varchar(50) OUTPUT,@reference varchar(50) OUTPUT,@chb1 int OUTPUT,@chb2 int OUTPUT,@chb3 int OUTPUT,@chb4 int OUTPUT,@chb5 int OUTPUTAsSet @QuestionDescription =(Select questionDescription from QuestionsBank Where questionID = @QuestionID)Set @Option1 =(Select optionDescription from options Where questionID = @QuestionID and optionNumber = 1)Set @Option2 =(Select optionDescription from options Where questionID = @QuestionID and optionNumber = 2)Set @Option3 =(Select optionDescription from options Where questionID = @QuestionID and optionNumber = 3)Set @Option4 =(Select optionDescription from options Where questionID = @QuestionID and optionNumber = 4)Set @Option5 =(Select optionDescription from options Where questionID = @QuestionID and optionNumber = 5)Set @reference = (Select referencedescription from reference where questionID = @QuestionID)Set @chb1 = (Select correctOption from options where questionID = @QuestionID and optionNumber = 1)Set @chb2 = (Select correctOption from options where questionID = @QuestionID and optionNumber = 2)Set @chb3 = (Select correctOption from options where questionID = @QuestionID and optionNumber = 3)Set @chb4 = (Select correctOption from options where questionID = @QuestionID and optionNumber = 4)Set @chb5 = (Select correctOption from options where questionID = @QuestionID and optionNumber = 5) RETURN This is what the page callsDim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection("myconnectionstring ") Dim cmdUpdate As New Data.SqlClient.SqlCommand("usp_getQuestionsforEditPopulateText", dbConnection) cmdUpdate.CommandType = Data.CommandType.StoredProcedure cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@QuestionID", Data.SqlDbType.Int)) cmdUpdate.Parameters("@QuestionID").Value = QuestionID cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@QuestionDescription", Data.SqlDbType.VarChar)) cmdUpdate.Parameters("@QuestionDescription").Direction = Data.ParameterDirection.Output cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@Option1", Data.SqlDbType.VarChar)) cmdUpdate.Parameters("@Option1").Direction = Data.ParameterDirection.Output cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@Option2", Data.SqlDbType.VarChar)) cmdUpdate.Parameters("@Option2").Direction = Data.ParameterDirection.Output cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@Option3", Data.SqlDbType.VarChar)) cmdUpdate.Parameters("@Option3").Direction = Data.ParameterDirection.Output cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@Option4", Data.SqlDbType.VarChar)) cmdUpdate.Parameters("@Option4").Direction = Data.ParameterDirection.Output cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@Option5", Data.SqlDbType.VarChar)) cmdUpdate.Parameters("@Option5").Direction = Data.ParameterDirection.Output cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@reference", Data.SqlDbType.VarChar)) cmdUpdate.Parameters("@reference").Direction = Data.ParameterDirection.Output cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@chb1", Data.SqlDbType.Int)) cmdUpdate.Parameters("@chb1").Direction = Data.ParameterDirection.Output cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@chb2", Data.SqlDbType.Int)) cmdUpdate.Parameters("@chb2").Direction = Data.ParameterDirection.Output cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@chb3", Data.SqlDbType.Int)) cmdUpdate.Parameters("@chb3").Direction = Data.ParameterDirection.Output cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@chb4", Data.SqlDbType.Int)) cmdUpdate.Parameters("@chb4").Direction = Data.ParameterDirection.Output cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@chb5", Data.SqlDbType.Int)) cmdUpdate.Parameters("@chb5").Direction = Data.ParameterDirection.Output 'open connection dbConnection.Open() 'Execute non query cmdUpdate.ExecuteNonQuery() 'close connection dbConnection.Close()
I've a requirement to store the output of the stored procedure into temp. tables/ table varibles. I've 4 select statements as my output of the stored procedure. How do I store the results of all the 4 select stmnts into 4 different temp tables.
Simplified SP is as...
Create procedure usp_test as begin
select c1,c2 from table1 select c3,4 from table2 select c9,c8 from table3 select c5,c7 from Table4 end
I'm expecting something like this...
declare @table1 table (c1, c2) insert into @table1 Exec <Sp_Name>
select * from @table1
I know the above stmnt works, if my SP has only 1 select stmnt as output. Please help me to acheive this for multiple select statements.
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.
I have the following Query which gives the output in XML format. select * from <Table_Name> For XML auto I wanted to store this output in a .Xml file at my local machine. I would prefer to have a proc when called generates the xml and stores it on the local m/c. Any ideas ? Thanks,
I have designed a package that reads in rows from an Excel file into a recordset then loops through the recordset sending two parameters to a webservice task. This works fine however I now need to output the results of the webservice task to a file or recordset - I have tried outputting to a file however it only stores the last result as the file is overwritten each time - I am new to SSIS and am sure there must be a really simple way to do this but cannot find an help on how to do it !
I am using below code to get patient status as an out put to my form. not sure whats happening but each time I run this its not executing my last "IF" if set to "N" show me N if not show me "Y" but it is by passing my first "IF" condition and jumps to last?
The column alerts_ind shows only Y or N in the table patient_status.
The table patient_status_mstr show the description of the patient which "discharged". All I want to do is if the patient is flagged with "discharge" the columns "alerts_ind" shows "Y". but something wrong? below is the code.
Alter PROCEDURE GBCheckPatientStatus (@enc_id varchar(36), @data_ind Char(1) OUTPUT)
as begin declare @alerts_ind char(1); select @alerts_ind =pm.alerts_ind
The result is that I am able to parameterize the sql end execute with the right result. The only problem is that the value is not stored in the variable @count. I could get to the same result using managed code in sql 2005 but still I am curious to find out where the problem is ....
My store Procedure is not save in Strore Procedure folder at the time of saving it give me option to save in Project folder and file name default is SQLQuery6.sql when i save it after saving when i run my store procedure
exec [dbo].[SP_GetOrdersForCustomer] 'ALFKI'
I am getting below error :
Msg 2812, Level 16, State 62, Line 1 Could not find stored procedure 'dbo.SP_GetOrdersForCustomer'.
Hi guysi would really appreciate your help here. what am i doing wrong with this stored procedure ALTER PROCEDURE usp_CreateNewCustomer( @LName as varchar(50), @FName as varchar(50), @Dept as varchar(50)=NULL, @PhoneType as varchar(50)=NULL, @Complete as Bit=NULL, @CustomerID as Int OUTPUT, @FaxModel as varchar(50)=NULL, @FaxNumber as varchar(50)=NULL, )ASINSERT INTO [CustomerInfo] ([LName], [FName], [Dept], [PhoneType], [Complete]) VALUES (@LName, @FName, @Dept, @PhoneType, @Complete)SELECT SCOPE_IDENTITY()INSERT INTO Extras (CustomerID, FaxModel, FaxNumber) VALUES (@CustomerID, @FaxModel, @FaxNumber)RETURN It keeps on complaning "'usp_CreateNewCustomer' expects parameter '@CustomerID', which was not supplied."thanks
Hi all, I have a few question regarding SPROC. Firstly, I want to create a sp, that will return a multiple column of data, how do you achieve that. Secondly, I want to create a store procedure that will return a multiple columns in a cursor as an output variable. Any help will be much appreciated. Can you have more then 1 return parameters??? Thanks. Kabir
I have asp.net web application which interface with SQL server, I want to run store procedure query of SQL using my asp.net application. How to declare connectons strings, dataset, adapter etc to run my store procedure resides in sql server. for Instance Dim connections as string, Dim da as dataset, Dim adpt as dataadapter. etc if possible , then show me completely code so I can run store procedure using my button click event in my asp.net application thank you maxmax
I am not sure if it is right place to ask the question. I have a store procedure in which funcitons are called and several temp tables are created. When I use sql analyzer, it runs fast. But when I called it from asp.net app, it runs slow and it waits and waits to get data and insert into temp tables and return. Though I have used with (nolock), it still not imprvoed. Any suggestions? Thanks in advance. NetAdventure
Hello, I am very new to ASP.NET and SQL Server. I am a college student and just working on some project to self teaching myself them both. I am having a issue. I was wondering if anyone could help me. Ok, I am creating a web app that will help a company to help with inventory. The employee's have cretin equipment assign to them (more then one equipment can be assigned). I have a search by equipment and employee, to search by equipment the entire database even then equipment that isn't assigned with the employee's it shows only that employees and if they have any equipment. They can select a recorded to edit or see the details of the record. The problem i am having is that the Info page will not load right. I am redirected the Serial Number and EmployeeID to the info page. I got everything working except one thing. If a person has more then one equipment when i click on the select it redirect and only shows the first record not the one I selected. and when i change it, a employee that doesn't have equipment will not show up totally.
SELECT E.FirstName AS UserFirstName, E.LastName AS UserLastName, eqtype.Description AS UserEquipType, empeq.UserEquipID, E.EmployeeID, E.cwopa_id, eq.IPAddress, eq.Location, eq.DataLine3, eq.DataLine2, eq.Voice, eq.DataLine1, eq.FloorBox, eq.Comments, eq.SerialNo, eq.Model, eq.Brand, eq.TagNo, eq.PurchaseLease, eq.WarrantyExpDate, eq.PhoneType, eq.PhoneNum, eq.CellNumber, eq.DataNumber, eq.SIDNumber, eq.Carrier FROM dbo.EMPLOYEES AS E LEFT OUTER JOIN dbo.EMPLOYEES_EQUIP AS empeq ON empeq.EmployeeID = E.EmployeeID LEFT OUTER JOIN dbo.EQUIPMENT AS eq ON eq.EquipID = empeq.EquipID LEFT OUTER JOIN dbo.EQUIP_TYPE AS eqtype ON eqtype.EquipTypeID = eq.EquipTypeID WHERE E.EmployeeID = @EmployeeID and eq.SerialNo=@SerialNo EmployeeID and SerialNo are primary keys. I was thinking maybe create a store procedures, but I have no idea how to do it. I created this and no syntax error came up but it doesn't work CREATE PROCEDURE dbo.inventoryinfo AS DECLARE @EmployeeID int DECLARE @SerialNo varchar(50) IF( @SerialNo is NULL) SELECT E.FirstName AS UserFirstName, E.LastName AS UserLastName, eqtype.Description AS UserEquipType, empeq.UserEquipID, E.EmployeeID, E.cwopa_id, eq.IPAddress, eq.Location, eq.DataLine3, eq.DataLine2, eq.Voice, eq.DataLine1, eq.FloorBox, eq.Comments, eq.SerialNo, eq.Model, eq.Brand, eq.TagNo, eq.PurchaseLease, eq.WarrantyExpDate, eq.PhoneType, eq.PhoneNum, eq.CellNumber, eq.DataNumber, eq.SIDNumber, eq.Carrier FROM dbo.EMPLOYEES AS E LEFT OUTER JOIN dbo.EMPLOYEES_EQUIP AS empeq ON empeq.EmployeeID = E.EmployeeID LEFT OUTER JOIN dbo.EQUIPMENT AS eq ON eq.EquipID = empeq.EquipID LEFT OUTER JOIN dbo.EQUIP_TYPE AS eqtype ON eqtype.EquipTypeID = eq.EquipTypeID WHERE E.EmployeeID = @EmployeeID ELSE SELECT E.FirstName AS UserFirstName, E.LastName AS UserLastName, eqtype.Description AS UserEquipType, empeq.UserEquipID, E.EmployeeID, E.cwopa_id, eq.IPAddress, eq.Location, eq.DataLine3, eq.DataLine2, eq.Voice, eq.DataLine1, eq.FloorBox, eq.Comments, eq.SerialNo, eq.Model, eq.Brand, eq.TagNo, eq.PurchaseLease, eq.WarrantyExpDate, eq.PhoneType, eq.PhoneNum, eq.CellNumber, eq.DataNumber, eq.SIDNumber, eq.Carrier FROM dbo.EMPLOYEES AS E LEFT OUTER JOIN dbo.EMPLOYEES_EQUIP AS empeq ON empeq.EmployeeID = E.EmployeeID LEFT OUTER JOIN dbo.EQUIPMENT AS eq ON eq.EquipID = empeq.EquipID LEFT OUTER JOIN dbo.EQUIP_TYPE AS eqtype ON eqtype.EquipTypeID = eq.EquipTypeID WHERE E.EmployeeID = @EmployeeID and eq.SerialNo=@SerialNoGO Also, when a user selects the select button in either Employee search or Equipment search it redirects them to info.aspx. Should I create a different aspx page for both? But I don't believe I should do that. I don't know if I gave you enough information or not. But if you could give examples that would be wonderful! Thanks so much Nickie
Hi, All, I need to write a sales report which needs to seperate total from direct sale and agentsale. The report looks like this( in the table of query,we have agentnumber, productname, sales, month
Month salefromagent directsale total Jan 1100 2300 3400 Feb 800 500 1300 .......... Dec --------------------------------- I know we can handle this in the program use two queries. But is there a way to do it use store procedure and then pass the result of store procedure to the ASP program. I am trying to write my first store procedure, thanks for any clue. Betty
Hi all, I need to write a sales store procedure. The sales summary is basically group by agentCode. But the problem is some agents have subagents. i.e., in the sales table. one agent 123456 has many subagents whose agentCode start with 17. And I want to group sales for all subagents who have agentCode 17XXXX to agent 123456's sales. what should I do in the store procedure.
I am trying to create a store procedure that look at a table and only maintain 30 worth of data only forever. The data that fall outside the 30 days need to be deleted. Can anyone help me with this?
I would like to create a employee store procedure to insert a record and mean while i can retrieve the EmployeeID for the record just inserted. Can you show me how to create a store procedure like this. Now i have a insert store procedure in the following:
CREATE PROCEDURE AddEmployee (@efname nvarchar(20), @elname nvarchar(20) ) AS
insert into tblEmployeeName (EmployeeFName, EmployeeLName) values (@efname,@elname) GO
This is an Oracle store procedure. Can Any body help me to convert it into SQL Server stored Procedure
PROCEDURE CALC_PERC (DB_ID IN NUMBER, LAT_TYPE IN CHAR) IS Tot_work_all number(12,2); Bid_tot number(12,2); Ewo number(12,2); Overruns number(12,2); Underruns number(12,2); Contr_tot_all number(12,2); sContractType ae_contract.contr_type%type; BEGIN select sum(nvl(tamt_ret_item,0) + nvl(tamt_paid_item,0)) into Tot_work_all from valid_item Where db_contract = db_id; Select sum(Contq * Contr_Price) into Bid_tot From Valid_item Where nvl(New_Item,'N') <> 'Y' and db_contract = db_id; Select sum(Qtd * Contr_price) into Ewo From Valid_item Where nvl(New_item,'N') = 'Y' and db_contract = db_id; Select Sum((Qtd-Nvl(Projq,0))*Contr_Price) into Overruns From Valid_item Where Qtd > Nvl(Projq,0) and db_contract = db_id and nvl(New_Item,'N') = 'N'; IF LAT_type <> 'R' THEN Select Sum((Nvl(Projq,0)-Contq) * Contr_Price) into Underruns From Valid_item Where Nvl(Projq,0) < Contq and db_contract = db_id and nvl(New_Item,'N') = 'N'; ELSE Select Sum((Nvl(Qtd,0)-Contq) * Contr_Price) into Underruns From Valid_item Where Nvl(Qtd,0) < Contq and db_contract = db_id and nvl(New_Item,0) = 'N'; end if; Contr_tot_all:= NVL(Bid_tot,0) +NVL(ewo,0) +NVL(overruns,0) +NVL(underruns,0);
IF Contr_tot_all = 0 THEN
Select Contr_type into sContractType from ae_contract where db_contract = db_id;
IF sContractType = 'A' OR sContractType = 'T' THEN --If the divisor is zero here, it's not an error. update ae_contract set perc_compu = 0 where db_contract = db_id;
ELSE --If the divisor is zero here, it would be an error update ae_contract set perc_compu = 100 * tot_work_all/contr_tot_all where db_contract = db_id; END IF; Else --Here we have a real number to calculate, so go ahead and do your stuff! update ae_contract set perc_compu = 100 * tot_work_all/contr_tot_all where db_contract = db_id; END IF; END;
hello iam new to store procedures.can anyone tell me how do i put in this sql statement into store procedure SELECT * FROM PortMaster WHERE PortCode= '" & Request.QueryString("PortCode") & "' "
TheProblem is coming in ,how do i put "Request.QueryString("PortCode")" into sql queranalyzer.iam using this in .aspx page. depending on querystring the record is selected.
hello iam using store procedure to open the the selected rows in datagrid into Excel.but iam getting blank spreadsheet.the querystrings are getting passed but the rows turn out to be blank. if i do not use store procedure than everything is ok.the querystring contains multiple strings so as to display it in excel. the stroe procedure is
CREATE PROCEDURE Employee_ExcelDisplayHO(@ID varchar(100)) AS SELECT * FROM employee_master WHERE empuser_id IN (@ID)
and this is the statement that i use in my .aspxpage to call store procedure
Dim Cmd As New SqlDataAdapter("Employee_ExcelDisplayHO", myconn) cmd.SelectCommand.CommandType = CommandType.StoredProcedure cmd.SelectCommand.Parameters.Add(New SqlParameter("ID", SqlDbType.VarChar, 100)) cmd.SelectCommand.Parameters("@ID").Value = Request.QueryString("ID")
