How Do I Loop Through A Record Set In A Stored Procedure?
Jan 17, 2006
Below is a stored procedure that designed to populate a drop down menu system on a website. It works fine as long as the 'id's in the first select start at 1 and are sequential. It fails to grab all the sub tables if the ids are not sequential. So, how do I structure the loop so that the WHERE clause uses not the loop iterator, but rather, the ids from the first Select statement.
Alternatively, is there a more elgant approach that will return the same set of recordsets?
Any help would be much appreciated
Thanks
ALTER PROCEDURE dbo.OPA_GetMenuItems
AS
Declare @i tinyint ,
@tc tinyint
Set @i = 1
/* Select for top level menu items*/
SELECT id, label, url, sort
FROM mainNav
ORDER BY sort
Set @tc = @@rowcount
while @i <= @tc
begin
Set @i = (@i + 1)
/* Select for submenu items
SELECT id, label, url, sort, mainNavId
FROM SubNav
WHERE (mainNavId = @i)
ORDER BY mainNavId, sort
end
Below is a stored procedure that designed to populate a drop down menu system on a website. It works fine as long as the 'id's in the first select start at 1 and are sequential. It fails to grab all the sub tables if the ids are not sequential. So, how do I structure the loop so that the WHERE clause uses not the loop iterator, but rather, the ids from the first Select statement.
Alternatively, is there a more elgant approach that will return the same set of recordsets?
Any help would be much appreciated Thanks
ALTER PROCEDURE dbo.OPA_GetMenuItems AS Declare @i tinyint , @tc tinyint Set @i = 1
/* Select for top level menu items*/
SELECT id, label, url, sort FROM mainNav ORDER BY sort
Set @tc = @@rowcount
while @i <= @tc
begin Set @i = (@i + 1)
/* Select for submenu items*/ SELECT id, label, url, sort, mainNavId FROM SubNav WHERE (mainNavId = @i) ORDER BY mainNavId, sort end
hi all, I want to execute a store procedure but my query returns more than one value. how can I asign value for that. Here is the code. Please help me out.
problem is @aaa where i cannot set the value.
Thanks Regards Sudadg
CREATE PROCEDURE movetable @id int
AS declare @VType varchar(10) declare @count int declare @aaa varchar(50) set nocount on create table #temp1(id int identity(1,1),Ac_Code varchar(50))
select count(*) from Journal_Table where Voucher_No=@id set @count=1 set @count=@@rowcount
while @count <>0 begin set @aaa=(select Ac_Code from Journal_Table where Voucher_No =@id) insert into #temp1(Ac_Code) values(@aaa)
I have a table that is basically set up so there is an employee_id, field_id, and a value... it looks sort of like this (in reality there are over 450 different employee ID's) employee_id field_id value 100 1 Brian 100 2 617-555-5555 100 3 Boston Office 101 1 Mary 102 2 617-666-6666 101 3 New york office
I want to loop thru this table so "for each" distinct employee ID, I can do an insert statement into another table where it is setup a little cleaner I know how to declare the variables, and set each variable = to the correct value, and how to do the INSERT once, but I am not 100% sure how to set up the loop so it will do each individual employee_ID. Any suggestions?
How do I write a stored procedure to loop through all the records to see if duedate is passed today's date then send an email to those users Dim duedateDim @emailDim ocdoEmail As New Object while duedate < DATEADD(day, DATEDIFF(day, '20010101', CURRENT_TIMESTAMP), '20010101') (sending the email)ocdoEmail = Server.CreateObject("CDO.Message") ocdoEmail.To = @email ocdoEmail.From = ocdoEmail.CC = ocdoEmail.Subject = "Your Item is passed due"ocdoEmail.HTMLBody = " was shipped to you on " & ShipDateTxt.Text & " and due on " & DueDateTxt.Text ocdoEmail.send() Don't know which loop I should use and how to set it up. Thanks!
I have two tables one hold all Friday dates for given year (col1 ID, col2 date), second table have three column col1, col22, col3. What I need to do is insert the first Friday date into the second table for col1 and second Friday into col2, third Friday in col3, fourth Friday in col1 and the process repeat until all Friday dates are inserted. I would like to use While loop but stuck on how to iterate through table one to get the data.
I have gridview display a list of users. I have added a column for a check box. If the box is checked I move the users to another table.I need to pass some parameter of or each row to a stored proc. My question. In the loop where I check if the checkbox is selected I need to call the stored procedure.Currently I do an open and closed inside the loop.What is best and most effficent method of doing this should I open and close the connection outside the loop and change the procs parameters as loop through. System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection( System.Configuration.ConfigurationManager.ConnectionStrings["connString"].ConnectionString); System.Data.SqlClient.SqlCommand commChk = new System.Data.SqlClient.SqlCommand("storedProc", conn); commChk.CommandType = System.Data.CommandType.StoredProcedure; commChk.Parameters.AddWithValue("@mUID", ddMainUser.SelectedValue.ToString()); commChk.Parameters.AddWithValue("@sUId", gvUsers.Rows[i].Cells[2].Text); commChk.Connection.Open(); commChk.ExecuteNonQuery(); conn.Close(); If so exactly how do I do this? How do I reset the parmaters for the proc? I haven't done this before where I need to loop through passing parameter to the same proc. thanks
Hi, how do I loop through a table in a store procedure? I need to check the all the record in a table and do some logic and then insert or update another table base on the logic?
What is wrong with this stored procedure? This should work right?
Create PROCEDURE UpdRequestRecordFwd
@oldITIDint , @newITID int AS Declare @RRID int Declare @APID int Declare crReqRec cursor for select RRID from RequestRecords where ITID = @oldITID open crReqRec fetch next from crReqRec into @RRID while @@fetch_status = 0 Begin
Update RequestRecords set ITID = @newITID where RRID = @RRID
I’m working on a stored procedure that meant to mail out users some of their action items daily.
The procedure has a double loop, first the user ids and user email addresses are selected into a table, then the outer loop cycles through the user ids and selects relevant action items to another table. The inner loop then cycles through these action items and at the end of each outer loop a string is mailed out.
Problem is that as the outer loop selects the relevant items for a user, the table holding the action items basically gets filled with more and more records and the inner loop then adds every item in the table to the string that gets mailed out, ending up with more and more items going to all the users.
I have tried to delete all records from the actionItems table at the end of each outer loop after the content of the action Items are mailed out, however this seems to keep the actionItems table empty at all times.
Not sure if this description is clear enough but I can’t see where I’m going wrong in terms of approach.
We're running SQL Server 2008 and have run into a bit of a situation. We have 5 databases all with the same tables and we are trying to create a query that will loop through the different databases and output the results per company database. I originally did a cursor, but my boss wants the query to be in a more readable format:
His ideal wish would be the query in a stored procedure and the cursor to create the input parameter for the stored procedure for the different databases.I've tried looking through some forums and googling some possibilities but can't seem to make any sense of them.
declare @dbname varchar(100) ,@sql varchar(max) createtable #TempDBs ( dbname nvarchar(100) , Orig_Jnl int , BaseRef int , Posting_Date date
I would like to know what are the ways to call a parameterized stored procedure within a loop in ASP.NET. Scenario: I have a loop through a dataSet and for each record I am going to execute a stored procedure to insert data in many tables. I can not use the following syntax: cmd.CommandType = CommandType.StoredProcedure cmd.CommandText = "dbo.storedprocedurename" With cmd .Parameters.Add(New SqlClient.SqlParameter("@param1", paramValue1)) .Parameters.Add(New SqlClient.SqlParameter("@param2", paramValue2)) End With What are the other ways to execute the parameterized stored procedures within a loop in ASP.NET?
I would like to write a store prodecure to return a month:
My output: Wk1 = July Wk2 = July Wk3 = July Wk4 = July Wk5 = Aug
and so on..
then i create list of array like below:
The counter for insert the week one by one
DECLARE @TotalWeek INT, @counter INT DECLARE @WeekNo varchar, @Month varchar SET @WeekNo = '4,9,14,18,22,27,31,35,40,44,48,53' --this is weekno,if less than 4, month is july, lf less than 9, month is august and so on SET @TotalWeek = 53
I appear to be having an issue where the @LetterVal and @Numeric variables aren't resetting for each loop iteration, so if no results are found, it just returns the previous loops values since they aren't overwritten. Â Below is the stored procedure I've created:
ALTER PROCEDURE [dbo].[ap_CalcGrade]Â -- Add the parameters for the stored procedure here @studId int, @secId int, @grdTyCd char(2), @grdCdOcc int, @Numeric int output,
[Code] ....
And below is the "test query" I'm using:Â
--  *** Test Program *** Declare @LetterVal varchar(2), -- Letter Grade     @Numeric  int,     -- Numeric Grade     @Result   int     -- Procedure Status (0 = OK) Execute @Result = dbo.ap_CalcGrade 102, 86, 'QZ', 3,Â
[Code] ....
This is resulting in an output of:Â
A+ 97 A+ 97 C- 72
but it should be returning the output below due to the 2nd data set not being valid/found in the sp query: Â A+ 97 No Find C- 72
I'm sure this is sloppy and not the most efficient way of doing this, so whats causing the errant results, and if there is any better way I should be writing it. Â Below is the assignment requirements:
Create a stored procedure using the STUDENT database called ap_CalcGrade that does the following:
1. Accepts as input STUDENT_ID, SECTION_ID, GRADE_TYPE_CODE, and GRADE_CODE_OCCURRENCE 2. Outputs the numeric grade and the letter grade back to the user 3. If the numeric grade is found, return 0, otherwise return 1 4. You must use a cursor to loop through the GRADE_CONVERSION table to find the letter grade
This is my first attempt at a loop in a stored procedure, and it is not working, but the rest of the sp works fine. Could anyone please provide me with some feedback. I am not sure if I need to execute the loop first or all the sp at once ? Thanks so much.CREATE PROCEDURE Table_1TT_1T (@PartNo varchar(20), @Wkorder varchar(10), @Setup datetime, @Line smallint, @TT integer, @Tester smallint, @LT1 integer, @LT2 integer, @LT3 integer, @LT4 integer, @LT5 integer, @LT6 integer, @LT7 integer, @LT8 integer, @LT9 integer, @LT10 integer, @LT11 integer, @LT12 integer, @LT13 integer, @LT14 integer, @LT15 integer, @LT16 integer, @LT17 integer, @LT18 integer, @LT19 integer, @LT20 integer, @LT21 integer, @LT22 integer, @LT23 integer, @LT24 integer, @LT25 integer, @LT26 integer, @LT27 integer, @LT28 integer, @LT29 integer, @LT30 integer, @LT31 integer, @LT32 integer, @LT33 integer, @LT34 integer, @LT35 integer, @LT36 integer, @UnitFound integer OUT, @parameters_LamType varchar(50) OUT, @parameters_Shunt real OUT, @parameters_ShuType varchar(50) OUT, @parameters_Stack real OUT, @parameters_Steel varchar(50) OUT, @Partno11 varchar(20) OUT, @Wkorder11 varchar(10) OUT, @Partno12 varchar(20) OUT, @Wkorder12 varchar(10) OUT, @Partno24 varchar(20) OUT, @Wkorder24 varchar(10) OUT, @Partno29 varchar(20) OUT, @Wkorder29 varchar(10) OUT, @Partno34 varchar(20) OUT, @Wkorder34 varchar(10) OUT, --@DL1 integer OUT, --@DL2 integer OUT, --@DL3 integer OUT, --@DL4 integer OUT, --@DL5 integer OUT, --@DL6 integer OUT, --@DL7 integer OUT, --@DL8 integer OUT, --@DL9 integer OUT, --@DL10 integer OUT, @DL11 integer OUT, @DL12 integer OUT, --@DL13 integer OUT, --@DL14 integer OUT, --@DL15 integer OUT, --@DL16 integer OUT, --@DL17 integer OUT, --@DL18 integer OUT, --@DL19 integer OUT, --@DL20 integer OUT, --@DL21 integer OUT, --@DL22 integer OUT, --@DL23 integer OUT, @DL24 integer OUT, --@DL25 integer OUT, --@DL26 integer OUT, --@DL27 integer OUT, --@DL28 integer OUT, @DL29 integer OUT, --@DL30 integer OUT, --@DL31 integer OUT, --@DL32 integer OUT, --@DL33 integer OUT, @DL34 integer OUT) --@DL35 integer OUT, --@DL36 integer OUT)ASSET @Tester = 1WHILE @Tester < 36 BEGIN Set @Line = (Select Line from dbo.location where Tester = @Tester) IF @Line = 453 BEGIN If @Tester = 1 BEGIN SET @LT1 = 453 END If @Tester = 2 BEGIN SET @LT2 = 453 END If @Tester = 3 BEGIN SET @LT3 = 453 END If @Tester = 4 BEGIN SET @LT4 = 453 END If @Tester = 5 BEGIN SET @LT5 = 453 END If @Tester = 6 BEGIN SET @LT6 = 453 END If @Tester = 7 BEGIN SET @LT7 = 453 END If @Tester = 8 BEGIN SET @LT8 = 453 END If @Tester = 9 BEGIN SET @LT9 = 453 END If @Tester = 10 BEGIN SET @LT10 = 453 END If @Tester = 11 BEGIN SET @LT11 = 453 END If @Tester = 12 BEGIN SET @LT12 = 453 END If @Tester = 13 BEGIN SET @LT13 = 453 END If @Tester = 14 BEGIN SET @LT14 = 453 END If @Tester = 15 BEGIN SET @LT15 = 453 END If @Tester = 16 BEGIN SET @LT16 = 453 END If @Tester = 17 BEGIN SET @LT17 = 453 END If @Tester = 18 BEGIN SET @LT18 = 453 END If @Tester = 19 BEGIN SET @LT19 = 453 END If @Tester = 20 BEGIN SET @LT20 = 453 END If @Tester = 21 BEGIN SET @LT21 = 453 END If @Tester = 22 BEGIN SET @LT22 = 453 END If @Tester = 23 BEGIN SET @LT23 = 453 END If @Tester = 24 BEGIN SET @LT24 = 453 END If @Tester = 25 BEGIN SET @LT25 = 453 END If @Tester = 26 BEGIN SET @LT26 = 453 END If @Tester = 27 BEGIN SET @LT27 = 453 END If @Tester = 28 BEGIN SET @LT28 = 453 END If @Tester = 29 BEGIN SET @LT29 = 453 END If @Tester = 30 BEGIN SET @LT30 = 453 END If @Tester = 31 BEGIN SET @LT31 = 453 END If @Tester = 32 BEGIN SET @LT32 = 453 END If @Tester = 33 BEGIN SET @LT33 = 453 END If @Tester = 34 BEGIN SET @LT34 = 453 END If @Tester = 35 BEGIN SET @LT35 = 453 END END SET @Tester = @Tester + 1 ENDSELECT @parameters_LAMTYPE = LAMTYPE, @parameters_SHUNT = SHUNT, @parameters_SHUTYPE = SHUTYPE, @parameters_STACK = STACK, @parameters_STEEL = STEEL FROM DBO.PARAMETERS A INNER JOIN .DBO.XREF B ON A.PARTNO = B.XREF WHERE B.PARTNO = @PARTNO SET @UnitFound = @@rowcountIF @UnitFound = 0 BEGIN SELECT @parameters_LAMTYPE = LAMTYPE, @parameters_SHUNT = SHUNT, @parameters_SHUTYPE = SHUTYPE, @parameters_STACK = STACK, @parameters_STEEL = STEEL FROM DBO.PARAMETERS WHERE PARTNO = @PARTNO SET @UnitFound = @@rowcount END --IF @LT1 = @Line BEGIN SET @DL1 = 1 END --IF @LT2 = @Line BEGIN SET @DL2 = 1 END --IF @LT3 = @Line BEGIN SET @DL3 = 1 END --IF @LT4 = @Line BEGIN SET @DL4 = 1 END --IF @LT5 = @Line BEGIN SET @DL5 = 1 END --IF @LT6 = @Line BEGIN SET @DL6 = 1 END --IF @LT7 = @Line BEGIN SET @DL7 = 1 END --IF @LT8 = @Line BEGIN SET @DL8 = 1 END --IF @LT9 = @Line BEGIN SET @DL9 = 1 END --IF @LT10 = @Line BEGIN SET @DL10 = 1 END IF @LT11 = 453 BEGIN SET @Partno11 = @Partno SET @Wkorder11 = @Wkorder SET @DL11 = 1 END --IF @LT11 = @Line BEGIN SET @DL11 = 1 END IF @LT12 = 453 BEGIN SET @Partno12 = @Partno SET @Wkorder12 = @Wkorder SET @DL12 = 1 END --IF @LT13 = @Line BEGIN SET @DL13 = 1 END --IF @LT14 = @Line BEGIN SET @DL14 = 1 END --IF @LT15 = @Line BEGIN SET @DL15 = 1 END --IF @LT16 = @Line BEGIN SET @DL16 = 1 END --IF @LT17 = @Line BEGIN SET @DL17 = 1 END --IF @LT18 = @Line BEGIN SET @DL18 = 1 END --IF @LT19 = @Line BEGIN SET @DL19 = 1 END --IF @LT20 = @Line BEGIN SET @DL20 = 1 END --IF @LT21 = @Line BEGIN SET @DL21 = 1 END --IF @LT22 = @Line BEGIN SET @DL22 = 1 END --IF @LT23 = @Line BEGIN SET @DL23 = 1 END IF @LT24 = 453 BEGIN SET @Partno24 = @Partno SET @Wkorder24 = @Wkorder SET @DL24 = 1 END --IF @LT25 = @Line BEGIN SET @DL25 = 1 END --IF @LT26 = @Line BEGIN SET @DL26 = 1 END --IF @LT27 = @Line BEGIN SET @DL27 = 1 END --IF @LT28 = @Line BEGIN SET @DL28 = 1 END IF @LT29 = 453 BEGIN SET @Partno29 = @Partno SET @Wkorder29 = @Wkorder SET @DL29 = 1 END --IF @LT30 = @Line BEGIN SET @DL30 = 1 END --IF @LT31 = @Line BEGIN SET @DL31 = 1 END --IF @LT32 = @Line BEGIN SET @DL32 = 1 END --IF @LT33 = @Line BEGIN SET @DL33 = 1 END IF @LT34 = 453 BEGIN SET @Partno34 = @Partno SET @Wkorder34 = @Wkorder SET @DL34 = 1 END --IF @LT35 = @Line BEGIN SET @DL35 = 1 END --IF @LT36 = @Line BEGIN SET @DL36 = 1 ENDGO
Help! I'm very tired (and new at this) and have looked for a solution in many places. I have an Employee table with a one to many Revenue table. All revenue types are in this table. I need the goals and actuals (two different revenue types) for a datagrid.
This is the result. Because I am looking at two revenue types, the result is providing 2 rows of data instead of one. what is the best way to combine this.
Region FullName SHARP Year Ann Goal YTD Goal YTDActual Region1 Doe10, John X 2003 20400 5100 0 Select Region1 Doe10, John X 2003 0 0 3987 Select Region1 Doe11, John X 2003 29645 7411.25 0 Select Region1 Doe11, John X 2003 0 0 5377 Select
Here's my stored procedure:
CREATE PROCEDURE spFilterRegion
@RIDsent As Integer, @StatusSent As Integer, @SelectedRegion As NVARCHAR (50) Output
AS SELECT Region.CountryID, Employee.RegionID, Employee.StatusID, Employee.SHARP, CASE When Employee.SHARP = 1 THEN "X" ELSE "" END AS SHARPresult, Employee.LastName, Employee.FirstName, Employee.LastName + ', ' + FirstName AS FullName, Employee.EmployeeID, Region.RegionName, ProducerRevenue.RevenueTypeID, CASE When ProducerRevenue.RevenueTypeID = 1 Then SUM(ProducerRevenue.Revenue) ELSE 0 END AS AnnGoal, CASE When ProducerRevenue.RevenueTypeID = 1 Then SUM(ProducerRevenue.Revenue)/DATEPART(mm, GETDATE()) ELSE 0 END AS YTDGoal, CASE When ProducerRevenue.RevenueTypeID = 2 Then SUM(ProducerRevenue.Revenue) ELSE 0 END AS Actual, ProducerRevenue.YearID FROM Employee LEFT OUTER JOIN ProducerRevenue ON Employee.EmployeeID = ProducerRevenue.EmployeeID AND ProducerRevenue.YearID = DATEPART(yy, GETDATE()) - 1 AND ProducerRevenue.MonthID < DATEPART(mm, GETDATE()) AND ProducerRevenue.StatusID = 1 AND ProducerRevenue.RevenueTypeID <= 2 LEFT OUTER JOIN Region ON Employee.RegionID = Region.RegionID WHERE Employee.StatusID = @StatusSent AND Employee.RegionID = @RIDsent AND Employee.RoleID = 1 GROUP BY Region.CountryID, Employee.RegionID, Region.RegionName, Employee.RoleID, Employee.StatusID, Employee.SHARP, Employee.LastName, Employee.FirstName, Employee.EmployeeID, ProducerRevenue.RevenueTypeID, ProducerRevenue.YearID ORDER BY Region.CountryID, Employee.RegionID, Employee.RoleID, Employee.StatusID, Employee.SHARP, Employee.LastName, Employee.FirstName, Employee.EmployeeID, ProducerRevenue.RevenueTypeID, ProducerRevenue.YearID
I have a temporary table with multiple records and a Stored Procedure requiring a value. In a Stored Procedure, I want to loop through records in the table and use the value from each record read as input to another Stored Procedure. How do I do this?
Now with the above result, On every record I have to fire a query Select SUM(sale), SUM(scrap), SUM(Production) from tableB where ProdID= ["ProdID from above query"].How to write this query in a Stored Procedure so that I can get the required SUM columns for all the ProdID's from first query?
I have three tables in this shipping scenario -- one to record the Sender's Info, one to record the Recipient's Info, and one to record the Shipment Info, and I perform the inserts in that order. I want to get the last inserted USERID (which is an INT, and the PK) from both the Sender's table and the Recipient's table so that I can record that info as a foreign key in my Shipment table. Everything compiles and runs. However, every time I run it, "2" is inserted in my foreign key columns in the Shipment Info table. I can't figure out where that 2 is coming from. So I analyzed the SP in query analyzer, and when I run it it shows: "(0 row(s) returned)@RETURN_VALUE=0" Can anyone help me with this? I'm so close I think: First, my SP's: *********************************************************** ALTER PROCEDURE dbo.INSERT_NEW_SENDER(@firstname nvarchar(50), @lastname nvarchar(50), @email nvarchar(50),@phone nvarchar(50))ASBEGIN TRANSACTIONDECLARE @NewID intSET NOCOUNT ONINSERT INTO SHP_USER(email, firstname, lastname, phone) VALUES(@email, @firstname, @lastname, @phone)SET @NewID = CONVERT(int, SCOPE_IDENTITY())COMMIT TRANSACTIONGO ************************************************************** ALTER PROCEDURE dbo.SP_INSERT_NEW_RECIPIENT(@firstname nvarchar(50),@lastname nvarchar(50),@company nvarchar(50),@division nvarchar(50),@address1 nvarchar(50),@address2 nvarchar(50),@city nvarchar (50),@state nvarchar (50),@zip nvarchar (50),@country nvarchar(50),@phone nvarchar(50),@fax nvarchar(50),@email nvarchar(50),@address_type nvarchar(50),@recipient_type nvarchar(50),@NewID int OUTPUT)AS/* SET NOCOUNT ON */ INSERT INTO SHP_recipient(firstname, lastname, company, division, address1, address2, city, state, zip, country, phone, fax, email, address_type, recipient_type) VALUES(@firstname, @lastname, @company, @division, @address1, @address2, @city, @state, @zip, @country, @phone, @fax, @email, @address_type, @recipient_type)SET @NewID = CONVERT(int, SCOPE_IDENTITY())RETURN ********************************************************************** And my .NET stuff: //NEW USER INSERT SqlCommand myCommand; SqlDataReader myReader; int sender_userid = 0; int recipient_userid = 0; //int NewID; myCommand = new SqlCommand("INSERT_NEW_SENDER", conn); myCommand.CommandType = CommandType.StoredProcedure;myCommand.Parameters.Add(new SqlParameter("@firstname", str_firstname)); myCommand.Parameters.Add(new SqlParameter("@lastname", str_lastname));myCommand.Parameters.Add(new SqlParameter("@phone", str_phone)); myCommand.Parameters.Add(new SqlParameter("@email", str_email));myCommand.Parameters.Add(new SqlParameter("@NewID", ParameterDirection.Output));myCommand.UpdatedRowSource = UpdateRowSource.OutputParameters; myCommand.Connection.Open(); myReader = myCommand.ExecuteReader();sender_userid = (int)myCommand.Parameters["@NewID"].Value; myCommand.Connection.Close(); //RECIPIENT INSERT SqlCommand myCommand2; SqlDataReader myReader2; myCommand2 = new SqlCommand("SP_INSERT_NEW_RECIPIENT", conn); myCommand2.CommandType = CommandType.StoredProcedure; //myCommand2.Parameters.Add(new SqlParameter("@userid", recipient_userid));myCommand2.Parameters.Add(new SqlParameter("@firstname", str_recip_firstname)); myCommand2.Parameters.Add(new SqlParameter("@lastname", str_recip_lastname));myCommand2.Parameters.Add(new SqlParameter("@company", str_company)); myCommand2.Parameters.Add(new SqlParameter("@division", str_division));myCommand2.Parameters.Add(new SqlParameter("@address1", str_recip_addr1)); myCommand2.Parameters.Add(new SqlParameter("@address2", str_recip_addr2));myCommand2.Parameters.Add(new SqlParameter("@city", str_city)); myCommand2.Parameters.Add(new SqlParameter("@state", str_state));myCommand2.Parameters.Add(new SqlParameter("@zip", str_zip)); myCommand2.Parameters.Add(new SqlParameter("@country", str_country));myCommand2.Parameters.Add(new SqlParameter("@phone", str_recip_phone)); myCommand2.Parameters.Add(new SqlParameter("@fax", str_fax));myCommand2.Parameters.Add(new SqlParameter("@email", str_recip_email)); myCommand2.Parameters.Add(new SqlParameter("@recipient_type", str_recipient_type));myCommand2.Parameters.Add(new SqlParameter("@address_type", str_Address_Type)); myCommand2.Parameters.Add(new SqlParameter("@NewID", ParameterDirection.Output));myCommand2.UpdatedRowSource = UpdateRowSource.OutputParameters; myCommand2.Connection.Open(); myReader2 = myCommand2.ExecuteReader();recipient_userid = (int)myCommand2.Parameters["@NewID"].Value; myCommand2.Connection.Close(); try { conn.Open(); //INSERT data into the SHP_SHIPMENTS tableSqlCommand cmd = new SqlCommand("INSERT INTO SHP_SHIPMENTS(sender, recipient, ship_method, rma, payment_method, must_pay, project, business_unity, office, attachments, shipment_description,shipment_notes,date_created) VALUES(@sender_userid, @recipient_userid, @ship_method,@rma,@payment_method,@must_pay,@project,@business_unity,@office,@attachments,@shipment_description,@shipment_notes, GetDate())", conn); cmd.Parameters.Add(new SqlParameter("@sender_userid", sender_userid)); cmd.Parameters.Add(new SqlParameter("@recipient_userid", recipient_userid));cmd.Parameters.Add(new SqlParameter("@ship_method", str_ship_method)); cmd.Parameters.Add(new SqlParameter("@rma", str_rma));cmd.Parameters.Add(new SqlParameter("@payment_method", str_payment_method)); cmd.Parameters.Add(new SqlParameter("@must_pay", str_must_pay));cmd.Parameters.Add(new SqlParameter("@project", str_project)); cmd.Parameters.Add(new SqlParameter("@business_unity", str_business_unit));cmd.Parameters.Add(new SqlParameter("@office", str_office)); cmd.Parameters.Add(new SqlParameter("@attachments", str_attachments));cmd.Parameters.Add(new SqlParameter("@shipment_description", str_shipment_description));cmd.Parameters.Add(new SqlParameter("@shipment_notes", str_shipment_notes)); cmd.ExecuteNonQuery();
ok I have a stored procedure...... I pass in the variables that are requried....What is the best way to add a record using my stored procedure in VB.net code in a button click event...... How might i do this with a data reader,,data adapter.....OR What.......................Do I need to declare all my varaibles I am adding to this new record in the line after POSCODE or can vb.net do this without a parameter statemetn
CREATE procedure dbo.Appt_AddAppt ( @ClinicID int, @AccountNum nvarchar(10), @DOS nvarchar(12), @POSCODE nvarchar(5) ) as Insert into Clinic_Appointments (ClinicID,AcctNumber,DateOfService,PlaceOfService,PlaceOfServiceID) Values (@ClinicID,@AccountNum,@DOS,@POSCODE,@ClinicID)
I'm calling the stored procedure below to insert a record but every record is inserted into my table twice. I can't figure out why. I'm using Sql Server 2000. Thanks.CREATE PROCEDURE sp_AddUserLog(@Username varchar(100),@IP varchar(50))AS SET NOCOUNT ONINSERT INTO TUserLogs (Username, IP) VALUES (@Username, @IP)GO Sub AddUserLog(ByVal Username As String) Dim SqlText As String Dim cmd As SqlCommand Dim strIPAddress As String
'Get the users IP address strIPAddress = Request.UserHostAddress
Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("MyConnectionString").ConnectionString) SqlText = "sp_AddUserLog" cmd = New SqlCommand(SqlText) cmd.CommandType = CommandType.StoredProcedure cmd.Connection = con
Hi, I was wondering if anyone could offer me some advice. I am currently using a stored procedure to insert records into a database. I want to be able to retrieve the ID (primar key) from the item that has just been inserted using the stored procedure. The ID I want to get back is Meter_ID This is my stored procedure:ALTER PROCEDURE dbo.quote
Hello, I'm trying to accomplish 3 things with one stored procedure.I'm trying to search for a record in table X, use the outcome of thatsearch to insert another record in table Y and then exec another storedprocedure and use the outcome of that stored procedure to update therecord in table Y.I have this stored procedure (stA)CREATE PROCEDURE procstA (@SSNum varchar(9) = NULL)ASSET NOCOUNT ONSELECT OType, Status, SSN, FName, LNameFROM CustomersWHERE (OType = 'D') AND (Status = 'Completed') AND (SSN = @SSNum)GO.Then, I need to create a new record in another table (Y) using the SSN,FName and Lname fields from this stored procedure.After doing so, I need to run the second stored procedure (stB) Here itis:CREATE PROCEDURE procstB( @SSNum varchar(9) = NULL)ASSET NOCOUNT ON-- select the recordSELECT OrderID, OrderDate, SSNFROM OrdersGROUP BY OrderID, OrderDate, SSNHAVING (ProductType = 'VVSS') AND (MIN(SSN) = @SSNum)GO.After running this, I need to update the record I created a moment agoin table Y with the OrderDate and OrderID from the second storedprocedure.Do you guys think that it can be done within a single stored procedure?Like for example, at the end of store procedure A creating an insertstatement for the new record, and then placing something like execprocstB 'SSN value'? to run stored procedure B and then having aupdate statement to update that new record?Thanks for all your help.
I can not get this stored procedure to delete my records...
I have a contact table RecordID FirstName LastName etc
and a Address table
RecordID Street Zip Town Country
And a Relation table
RecordID ContactID AddressID CreateDate
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[bc_Contact_Delete]
@ContactID int
AS
--SET NOCOUNT ON
BEGIN TRY
BEGIN TRANSACTION -- Start the transaction
-- Delete all Adresses
DELETE FROM [Address]
WHERE
RecordId in (SELECT ca.AdressId from [ContactAddress] ca
where
ca.ContactID = @ContactID)
-- Delete all Relations
DELETE FROM [ContactAdress]
WHERE ContactID = @ContactID
--- Delete Kontakt
DELETE FROM [Contact] WHERE (([RecordId] = @ContactID))
COMMIT TRANSACTION
END TRY
BEGIN CATCH
-- Whoops, there was an error
ROLLBACK TRANSACTION
-- Raise an error with the
-- details of the exception
DECLARE @ErrMsg nvarchar(4000),
@ErrSeverity int
SELECT @ErrMsg = ERROR_MESSAGE(),
@ErrSeverity = ERROR_SEVERITY()
RAISERROR(@ErrMsg, @ErrSeverity, 1)
END CATCH
RETURN
My Errormessage is
The DELETE statement conflicted with the REFERENCE constraint "FK_bc_ContactAdress_bc_Address". The conflict occurred in database "bContacts", table "dbo.ContactAddress", column 'AdressID'.
I need to essentially do 2 loops. One loops through each record and then inside each record row, I want to perform an insert on each column.
Something like this maybe using a cursor or something else:
For each record in my table (I'll just use the cursor) For each column in current record for cursor perform some sql based on the current column value Next Next
So below, all I need to do is figure out how to loop through each column for the current record in the cursor
I am trying to save user data to a sql table but keep getting the following error; Unable to cast object of type 'System.Boolean' to type 'System.Data.SqlClient.SqlParameter'. I am using VWD Express Edition with .NET 2.0 and a SQL 2000 database. The code that generates the error is as follows; Public Function chkUser(ByVal strUser As String) As Stringdim MyConnection As SqlConnectionDim MyCommand As SqlCommandDim ReturnString As StringDim params As SqlParameterDim SelectCmd As String = "wm_CheckUser"MyConnection = New SqlConnection(myConnectionString)MyCommand = New SqlCommand(SelectCmd, MyConnection)MyCommand.CommandType = CommandType.StoredProcedure Tryparams = MyCommand.Parameters.Add("@parUser", SqlDbType.VarChar, 100).Value = strUserMyCommand.Connection.Open()MyCommand.ExecuteNonQuery()ReturnString = "Record Exists!"Catch Exp As SqlExceptionReturnString = ReturnError(Exp.Number, "Users", Exp.Message)End TryMyCommand.Connection.Close()Return ReturnString End Function Basically, I'm traying to check to see if a user id already exists in the database before saving the data the user entered. If the email address entered by the user is already in the database I want a message to be shown to the user. If the email address does not exist then the data entered by the user is saved and the form goes to step two (2) of the user registration process. Any help with this would be greately appreciated. I can't seem to see what is wrong here. Please someone help. Thanks, Jaime
I seem to be able to see where a procedure is being recompiled, but not the actual statement that was executing the procedure.
Note, with 2008 there is a DMV called dm_exec_procedure_stats , which is not present in 2005
USE YourDb;
SELECT qt.[text] AS [SP Name], qs.last_execution_time, qs.execution_count AS [Execution Count] FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt WHERE qt.dbid = DB_ID() AND objectid = OBJECT_ID('YourProc')
The above shows results that include the CREATE PROCEDURE statements for the procedure in question, but this only indicates that the procedure was being recompiled, not necessarily that it was being executed?
Dear all, I am using C# , asp.net and sql server 2005. Let me explain the situation. I have written procedure to insert data into the table and return last inserted value by @@identity variable. Now my question is how do I execute this process so that I can Get last inserted variable values Please help thanks
I am calling stored procedure called GetCommonItemCount within another stored procedure called CheckBoxAvailability, the first stored procedure should return a count to second stored procedure and based on that some logic will be executed.
I have 2 problems in that
1. The result is not coming from first stored so the variable called @Cnt is always 0 although it should be 18 2. At the end i need to see in the output the result from second stored procedure only while now i am seeing multiple results of record sets coming.
I have attached the scripts also, the line i described in step1 is
Is there a way using a stored procedure in a local database to add a record to a database executing in a cloud environment when both entities reside in different domains?
