SEARCH YOUR SOLUTION HERE  

Iterations in stored procedures!!!



Can somebody give me an example of a loop in sql stored procedure.

say this is what i want to do,

for i = 1 to 10
call procedure1 i
next

Is there anything equivalent to this kind of iterations in stored procedures.

Posted On: Tuesday 20th of November 2012 02:58:58 AM Total Views:  372
View Complete with Replies




Related Messages:

errors and stored procedures   (180 Views)
Originally posted by : Gulliver (gulliver@vi.is)Hi I am working with stored procedures a lot. Some of the storedprocedures are usingother stored procedures. When an error occure I want the name of the procedure that caused the errorto be returned with the line number where the error occured. Is this possible in SQL-Server Thank youGulliver
SQL stored procedure Loop Help   (124 Views)
, I have the following statements: CREATE TABLE CVPGFIDAYCOUNT6IMPORT AS (SELECT * FROM CMAMASTERIMPORT WHERE GFIDAYCOUNT =(SELECT DAYCOUNTLIST FROM CVPDAYCOUNTLIST WHERE TWENTYDAYCOUNT='3')); CREATE TABLE CVPGFIDAYCOUNT5IMPORT AS (SELECT * FROM CMAMASTERIMPORT WHERE GFIDAYCOUNT =(SELECT DAYCOUNTLIST FROM CVPDAYCOUNTLIST WHERE TWENTYDAYCOUNT='2')); CREATE TABLE CVPGFIDAYCOUNT4IMPORT AS (SELECT * FROM CMAMASTERIMPORT WHERE GFIDAYCOUNT =(SELECT DAYCOUNTLIST FROM CVPDAYCOUNTLIST WHERE TWENTYDAYCOUNT='1')); CREATE TABLE CVPGFIDAYCOUNT3IMPORT AS (SELECT * FROM CMAMASTERIMPORT WHERE GFIDAYCOUNT =(SELECT DAYCOUNTLIST FROM CVPDAYCOUNTLIST WHERE TWENTYDAYCOUNT='0')); I am trying to make the above more efficient. I am trying to apply a stored procedue statemnt(necessary) which can list one set of the above, and decrement the tables 'CVPGFIDAYCOUNTnnIMPORT' from 6 to 3, as well as TWENTYDAYCOUNT='n' from 3 down to 0. I have attempted the following declare @counter1 int set @counter1 = 6 while @counter1 > 3 begin set @counter = @counter1 - 1 declare @counter2 int set @counter2 = 3 while @counter2 > 0 begin set @counter2 = @counter2 - 1 CREATE TABLE CVPGFIDAYCOUNT&"counter1"&IMPORT AS (SELECT * FROM CMAMASTERIMPORT WHERE GFIDAYCOUNT =(SELECT DAYCOUNTLIST FROM CVPDAYCOUNTLIST WHERE TWENTYDAYCOUNT='"counter2"')); end end could someone please help!!!
HELP! ADO won't return my stored procedure's return values   (120 Views)
Originally posted by : Brad (BTorchin@aol.com)Environment: InterDEV 6.0 / MS SQLServer v7.0 / IIS 4.0I am trying to return a status code from a stored proc, but all I get are empty values (RETURN_VALUE and @Err are blank). The SP returns the values when called from another stored proc, so I know the problem is either my ADO code, some environment issue, or perhaps just bad karma. What am I doing wrongI tried different ways to return values from the stored proc using. I tried ordinals and explicitly naming parameters, either way the values returned were empty. The code below displayed blank values.--------------------ASP Code:Set conn = Server.CreateObject("ADODB.Connection")conn.ConnectionTimeout = Application("Arp_ConnectionTimeout")conn.CommandTimeout = Application("Arp_CommandTimeout")conn.Open Application("Arp_ConnectionString"), _Application("Arp_RuntimeUserName"), _Application("Arp_RuntimePassword")Set cmd = Server.CreateObject("ADODB.Command")cmd.CommandText = "dbo.DeleteCheck"cmd.CommandType = adCmdStoredProccmd.ActiveConnection = connset mParm = cmd.CreateParameter("RETURN_VALUE", adInteger ,adParamReturnValue)cmd.Parameters.Append mParmset mParm = cmd.CreateParameter("@CheckNo", adChar,adParamInput,10,CheckToDelete)cmd.Parameters.Append mParmset mParm = cmd.CreateParameter("@Outval", adInteger,adParamOutput)cmd.Parameters.Append mParmSet rs = Server.CreateObject ("ADODB.Recordset")Set rs = cmd.ExecuteResponse.Write "Parm 0=" & cmd.Parameters(0).Value Response.Write "Parm 2=" & cmd.Parameters(2).Value Response.Write "Parm @Outval=" & cmd.Parameters("@Outval").Value Response.Write "RETURN_VALUE=" & cmd.Parameters("RETURN_VALUE").Value -------------------SQL Codedeclare @rows int, @BatchStatus char(11), @Err intSELECT @Err = 3SELECT Trans.CheckNo, Trans.Amount, Trans.GroupOutID, Trans.IssueDate, GroupOut.BatchOutID, BatchOut.BatchOutStatus, Account.AccountDescFROM Account INNER JOIN GroupOut ON Account.AccountID = GroupOut.AccountID INNER JOIN BatchOut ON GroupOut.BatchOutID = BatchOut.BatchOutID INNER JOIN Trans ON GroupOut.GroupOutID = Trans.GroupOutID WHERE (Trans.CheckNo = @CheckNo) and (BatchOutStatus 'Closed')SELECT @rows = @@rowcountif @rows = 1 beginDELETE Trans WHERE CheckNo = @CheckNo SELECT @Err = 0end elsebegin if @rows < 1SELECT @Err = 1else SELECT @Err = 2end SELECT @Outval = @ErrRETURN @Er
Select stored procedure   (113 Views)
, i'm a beginner using SQL 2005 on a SQL 2003 server. I've created a .Net application that connect to a SQL database. I'm needing to create a select stored procedure to select all data between a date range. My boss tells I need to figure this out on my own and i'm not finding much to my question online. My data type is set now as a varch(50) its for a fiscal year. The user will mostly enter in 05/2008 to 04/2009 or sometime they put in 08 to 09 in that field, that is why I just made it a varch(50). I'm researching on how to create a stored procedure to select all between whats entered into that field. Can anyone give me any suggstions on how I might code something like this Below is what I've started, I appreciate the help. My code may very amateur, but that is why i'm looking help.
how to update 2 tables using using stored procedure   (196 Views)
Originally posted by : asiya (asiya@talk21.com)hi and help me plsi am trying to insert data into 2 tables from an asp page using sql stored procedure, the problem am having is, i want to set the first table primary key as a foreign key in the second tablecan you help me pl
How do i load images stored in a access database?   (169 Views)
Originally posted by : Luciano (luciano@asas.net)What do i have to do to load an image stored in an OLE field in a access DB to show it in an asp document
IN predicate & passing input parm to its stored proc.   (128 Views)
Originally posted by : B. Bora Bali (bora@sprynet.com)Hi folks,I have a query like the following one. This is going to be placed in a stored procedure. I want to pass an input parameter, that is determined in an ASP script, to the IN predicate of the stored procedure's query statement: SELECT text FROM table WHERE id IN (k_1, k_2, k_3, ..., k_n)I want to pass the set of k_x as an input parm to the proc. (Is this possible) Note: id is an integer. If possible, how would the stored procedure for this query look like I really appreciate your inputs.(I am running MS SQL Server Version 6.5)-B. Bora Bali
Execute 1 stored procedure from another   (118 Views)
Just general question and some advice, can i execute one stored procedure from another and in order to get the results from it, and then do a count on these results. I was thinking of having the results put into the temp table and then doing a count on this temp table. Would that work
Call one stored procedure into another.   (110 Views)
Ok i have one stored procedure which generates a table of data. However i now need to do a count on some of the data and also need to find min value as well. I decided to call this stored proecure into another one and do the count and min calls from here as wasnt sure how to do it in the main query. I have managed to call the stored procedure and create a temp table for my new data, however i dont know how to populate the new table with values from the called stored procedure. I know i need to delcare them like i have done and then give them a value using the select, or i think i do, but then not sure how to actually get the value into the select. Does anyone know how to do this or even what i am going on about!!!!!! This is what i have so far Code: ALTER PROCEDURE sp_EngineerTimeslotsScheduleCount @STARTDATE DATETIME, @CONTRACT VARCHAR(3), @ENGINEER VARCHAR(20) AS DECLARE @BookedFromTime DATETIME DECLARE @BookedToTime DATETIME DECLARE @AMAppointments INT DECLARE @PMAppointments INT Select @BookedFromTime = '18/dec/2007' Select @BookedToTime = '18/dec/2007' Select @AMAppointments = '3' Select @PMAppointments = '5' EXEC sp_EngineerTimeslotsSchedule @STARTDATE, @CONTRACT, @ENGINEER CREATE TABLE #TempSchedule (TempAMTime Datetime, TempPMTime Datetime, TempAMSlots Int, TempPMSlots Int) INSERT #TempSchedule (TempAMTime, TempPMTime, TempAMSlots, TempPMSlots) VALUES (@BookedFromTime, @BookedToTime, @AMAppointments, @PMAppointments) SELECT * FROM #TempSchedule
Help with stored procedures in sql 2000   (141 Views)
HI , I was hoping someone would be able to help me with some thing. I am learning ASP.NEt v 2.0 and am using SQL server 2000 developerd edition to build an exommerce website using the Apress booking Beginning E-Commerce in C# 2005. I have been managing fine up to now but the book is building stored procedures for retrieving products lists from the database. The book is written for SQL 2005 express edition but says that with some extra woork there is no problems with getting the code to work in 2000. I have two procedures that implement paging which the book says doesn't work in sql 2000 and gives a very brief solution which doesn't make sense to me. Would anybody be able to help me convert these so i can use them in sql 2000 I get the rough idea but am having problems re-writing this and I am now holding my hands and admitting defeat. The procedures are as follows: Code: CREATE PROCEDURE GetProductsOnDepartmentPromotion (@DepartmentID INT, @DescriptionLength INT, @PageNumber INT, @ProductsPerPage INT, @HowManyProducts INT OUTPUT) AS -- declare a new TABLE variable DECLARE @Products TABLE (RowNumber INT, ProductID INT, Name VARCHAR(50), Description VARCHAR(5000), Price MONEY, Image1FileName VARCHAR(50), Image2FileName VARCHAR(50), OnDepartmentPromotion BIT, OnCatalogPromotion BIT) -- populate the table variable with the complete list of products INSERT INTO @Products SELECT ROW_NUMBER() OVER (ORDER BY ProductID) AS Row, ProductID, Name, SUBSTRING(Description, 1, @DescriptionLength) + '...' AS Description, Price, Image1FileName, Image2FileName, OnDepartmentPromotion, OnCatalogPromotion FROM (SELECT DISTINCT Product.ProductID, Product.Name, SUBSTRING(Product.Description, 1, @DescriptionLength) + '...' AS Description, Price, Image1FileName, Image2FileName, OnDepartmentPromotion, OnCatalogPromotion FROM Product INNER JOIN ProductCategory ON Product.ProductID = ProductCategory.ProductID INNER JOIN Category ON ProductCategory.CategoryID = Category.CategoryID WHERE Product.OnDepartmentPromotion = 1 AND Category.DepartmentID = @DepartmentID ) AS ProductOnDepPr -- return the total number of products using an OUTPUT variable SELECT @HowManyProducts = COUNT(ProductID) FROM @Products -- extract the requested page of products SELECT ProductID, Name, Description, Price, Image1FileName, Image2FileName, OnDepartmentPromotion, OnCatalogPromotion FROM @Products WHERE RowNumber > (@PageNumber - 1) * @ProductsPerPage AND RowNumber (@PageNumber - 1) * @ProductsPerPage AND RowNumber (@PageNumber - 1) * @ProductsPerPage AND RowNumber
insert stored procedure values in a table   (135 Views)
hi i have a stored procedure which is selecting a table from a linked server which is in my sql server database. the linked server is linked to an oracle database... my stored procedure is below as follows: Code: CREATE PROCEDURE sp_OracleStudentTbl AS SELECT * FROM OPENQUERY(XXXXXX, 'SELECT * FROM vw_student_details_forsurvey') GO it works fine .. when i try it in query analyzer. meaning it retrieves back results... i would like too insert this view into a table. which i have in my database where my stored procedure is . meaning i would like to create a table everytime i run this stored procedure and insert the data it has in it into a the table i am creating... the table will only be created twice a year.. so it will not be happening constantly....
Select query returns far more results when put in stored procedure (urgent please)   (161 Views)
I have a select query like this SELECT master_feed_product.id as MasterFeedProductId ,ISNULL(MasterTaxonomy.Id , -1) AS MasterCategoryId ,ISNULL(MasterTaxonomy.CategoryName, 'Unmapped') AS MasterCategory ,[mf_product_title] AS ProductTitle ,[mf_product_sku] AS ProductSku ,SUM(partner_click.p_click_cost) AS TotalCost ,SUM(ISNULL(product_checkout.total, 0)) AS SalesRevenue ,COUNT(product_checkout.itemcount) AS OrderCount ,[TopProduct] AS TopProduct ,[price_col_value] AS Price ,master_feed_product.[created]AS CreatedDate FROM [onefeed].[dbo].[master_feed_product] LEFT OUTER JOIN MasterTaxonomy ON MasterTaxonomy.Id = master_feed_product.TaxonomyId INNER JOIN feeds ON feeds.id = master_feed_product.feed_id INNER JOIN brands ON brands.id = feeds.brand_id AND (@localMerchantId IS NULL OR brands.merchant_id = @localMerchantId) AND (@localBrandId IS NULL OR brands.id = @localBrandId) full outer join partner_click On master_feed_product.id = partner_click.product_id full outer join product_checkout On partner_click.merchant_id = product_checkout.merchant_id AND partner_click.product_id = product_checkout.product_id AND partner_click.click_id = product_checkout.click_id where (@localActive IS NULL OR master_feed_product.Active = @localActive) and (ISNULL(@localMasterCategory, '') = '' OR MasterTaxonomy.CategoryName like @localMasterCategory) And (@localDateFrom IS NULL OR master_feed_product.created >= @localDateFrom) AND (@localDateTo IS NULL OR master_feed_product.created
How to schedule stored procedure from the web?   (158 Views)
We need to allow users to set filters and schedule to run their queries daily, weekly, monthly online (ASP.NET) What is the best way to do it if there is any
Calling a COM object in a stored procedure   (165 Views)
Originally posted by : Dino (ddamalas@brtrc.com)Does anyone know if it is possible to call a COM object from a stored procedure in SQL server 7.0 If so, ho
If statement stored procedure   (203 Views)
its peebman2000 again, i'm having trouble with a stored procedure. I have learned and found a way to set up reminder emails using sql 2005 job scheduleing tool. The email works, but I need to write the stored procedure correctly to execute correctly. Below is my stored procedure i'm trying to write and it's not working. Basically I'll have an email address and a deadline date column: Table: Deadline(Id, email, deadline) So I'll schedule SQL to check the table everyday at noon, and to select the email and deadline date. If the deadline date = system date then send email to the email address. And that were i'm struggling at. Can anyone give me any assistance on writing this type of stored procedure, i'm not too familiar with the if statement in SQL, I've done case statement but not IF statement.
Writing stored procedures to accept servername as a variable   (275 Views)
Ok, I want to create a stored procedure that accepts the servername that I want to query. That is, we have several servers around the country, and I want run the query on which ever one I choose by sending the stored procedure a variable which contains the server name. These queries work ok when I run them hardcoded as servername.databasename.dbo.tablename...but I get errors creating the stored procedure as below... Must declare the table variable "@servername". Any ideas Code: create procedure show_unsold_items @servername varchar(50) as SELECT orderdate as "Date Ordered", COUNT(ordno) as "Orders still in database" FROM @servername WHERE DATEPART(week, orderdate) = DATEPART(week, GETDATE()) AND DATEPART(year, orderdate) = DATEPART(year, GETDATE()) GROUP BY...
How to use parameters inside a view of a stored procedure   (174 Views)
, I am trying to write a stored procedure with view in it, the problem is the parameter of the stored procedure need to be used in the view, I tried a lots ways, but still got error say"@dtStartDate need to be declared", any suggestions CREATE PROCEDURE Information @dtStartDate DateTime, @dtEndDate DateTime AS Begin exec('Create View WorkerProduct As SELECT wp.sName AS worker, p.sName FROM tblWorkerProduct wp INNER JOIN tblProduct p ON wp.iProductID = p.iOID Where wp.dtDate>=@dtStartDate AND p.dtDate
Proper way to reference a stored proc.   (108 Views)
Ive got a stored procedure that Im trying to reference in my VB code. Ive tested it in the Query Analyzer and it works fine, but when I run it in my program it bombs. Heres my proc: CREATE PROCEDURE deleteContactFeedback @contactID_pk int ASDELETE FROM contactUs WHERE contactID_pk = @contactID_pkGOAnd heres my VB code: Dim objSQLMsgInfo As New SQLComponent()Dim objSqlUserInfoReturnObject As New SQLReturnObject()objSqlUserInfoReturnObject = objSQLMsgInfo.ExecuteSqlStatement("sales", "EXEC deleteContactFeedback '@contactID_pk'")Can anyone determine whats wrong
Cannot find domain from my .asp stored in SQL 7.0   (122 Views)
Originally posted by : Tammy (tlnet2000@yahoo.com)I re-install WIndows NT 4.0, SQL 7.0. Then try to run my company's web site (intranet) which is written by asp. However, in my main page, the asp program cannot find domain name.Anybody knows what the problem is Is it related SQL 7.0 or NT problem I don't know.
Adding date through stored procedure   (214 Views)
I have a stored procedure which copies a particular record from one table to another using the following code. Code: CREATE PROCEDURE Deleted_Channels_History @id int, @DateTime datetime AS Insert Into Deleted_Channels (Channel_ID, Channel_Name, Channel_Desc. Channel_Pic) Select Channel_ID, Channel_Name, Channel_Desc. Channel_Pic from Channels Where Channel_ID= @id GO Both the tables have the same fields except one i.e the date and time field in the table in which the records are$ moved. How can I add that datetime in the other table. Also the other table has an additional field called Record_ID. This field has datatype uniqueidentifier. When I try to create this stored procedure, it gives an error that one table has more columns in it that the others (something like that). So how do we let the stored procedure add a uniqueidentifier by it self IS uniqueidentifier is like autonumber in MS ACCESS which is generated automatically CB