SQL For-loop Stored Procedures

Aug 23, 2006

i want to grant exec permissions on all of my user stored procedures.
but this process of writing 'grant exec sp_tblAction on public' for
each procedure would take years. how can i write a query that loops
through all stored procedures, checks if it is a user created
procedured, and then grant exec permission for that procedure? can it
be done?



tx, Wilmar

View 11 Replies


ADVERTISEMENT

Inserting Multiple Rows In Loop With A Sql Stored Procedures

Jun 4, 2008

I am trying to insert each record coming from my DataTable object to sql server database. The problem that I have is that I have my stored procedures within the loop and it work only for one record, because it complaing that there are too many parameters. Is there a way i can add up my parameters before the loop and avoid this issue?
 
Here is the code I am using:Public Sub WriteToDB(ByVal strDBConnection As String, ByVal strFileName As String, ByVal strTable As String)
'Fill in DataTable from AccessDim objConn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFileName)
Dim adapter As New OleDbDataAdapter()Dim command As New OleDbCommand
Dim DataTable As New DataTableDim sqlCommand = "SELECT * FROM " & strTableDim objDataTable As New DataTable
objConn.Open()
command.CommandType = CommandType.Text
command.Connection = objConn
command.CommandText = sqlCommandadapter = New OleDbDataAdapter(command)DataTable = New DataTable("NFS")
adapter.Fill(DataTable)
'Sql DB vars
'Dim dtToDBComm = "INSERT INTO NFS_Raw(Time, Exch, Status) VALUES ('test', 'test', 'test')"Dim sqlServerConn As New SqlConnection(strDBConnection)Dim sqlServerCommand As New SqlCommand
 sqlServerCommand = New SqlCommand("InsertFromAccess", sqlServerConn)
sqlServerCommand.CommandType = CommandType.StoredProcedure
sqlServerConn.Open()For Each dr As DataRow In DataTable.Rows
sqlServerCommand.Parameters.Add(New SqlParameter("@Time", dr.ItemArray(0).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@Exch", dr.ItemArray(1).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@Status", dr.ItemArray(2).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@Msg", dr.ItemArray(3).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@Action", dr.ItemArray(4).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@BS", dr.ItemArray(5).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@OC", dr.ItemArray(6).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@CP", dr.ItemArray(7).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@Qty", dr.ItemArray(8).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@Product", dr.ItemArray(9).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@MMMYY", dr.ItemArray(10).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@Strike", dr.ItemArray(11).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@Limit", dr.ItemArray(12).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@StopPrc", dr.ItemArray(13).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@Type", dr.ItemArray(14).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@Rstr", dr.ItemArray(15).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@TIF", dr.ItemArray(16).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@RstrQty", dr.ItemArray(17).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@ExecQty", dr.ItemArray(18).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@WorkQty", dr.ItemArray(19).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@CxlQty", dr.ItemArray(20).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@AccountNum", dr.ItemArray(21).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@ExchMbrID", dr.ItemArray(22).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@ExchGrpID", dr.ItemArray(23).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@ExchTrdID", dr.ItemArray(24).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@MemberID", dr.ItemArray(25).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@GroupID", dr.ItemArray(26).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@NTrdID", dr.ItemArray(27).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@Acct", dr.ItemArray(28).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@ExchTime", dr.ItemArray(29).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@ExchDate", dr.ItemArray(30).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@TimeSent", dr.ItemArray(31).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@TimeProcessed", dr.ItemArray(32).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@PA", dr.ItemArray(33).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@OrderNo", dr.ItemArray(34).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@TTOrderKey", dr.ItemArray(35).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@IP", dr.ItemArray(36).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@FFT3", dr.ItemArray(37).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@FFT2", dr.ItemArray(38).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@SubmitTime", dr.ItemArray(39).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@SubmitDate", dr.ItemArray(40).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@TransID", dr.ItemArray(41).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@SessionID", dr.ItemArray(42).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@SeriesKey", dr.ItemArray(43).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@ExchangeOrderID", dr.ItemArray(44).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@Destination", dr.ItemArray(45).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@FlowDeliveryUnit", (dr.ItemArray(46))))sqlServerCommand.Parameters.Add(New SqlParameter("@TimeReceived", dr.ItemArray(47).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@CallbackReceived", dr.ItemArray(48).ToString()))
 
sqlServerCommand.ExecuteNonQuery()Next
 
sqlServerConn.Close()
objConn.Close()
End Sub
 
 
Thanks for eveones input in advance.

View 4 Replies View Related

Inserting Multiple Rows In Loop With A Sql Stored Procedures

Jun 4, 2008

I am trying to insert each record coming from my DataTable object to sql server database. The problem that I have is that I have my stored procedures within the loop and it work only for one record, because it complaing that there are too many parameters. Is there a way i can add up my parameters before the loop and avoid this issue?
 
Here is the code I am using:Public Sub WriteToDB(ByVal strDBConnection As String, ByVal strFileName As String, ByVal strTable As String)
'Fill in DataTable from AccessDim objConn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFileName)
Dim adapter As New OleDbDataAdapter()Dim command As New OleDbCommand
Dim DataTable As New DataTableDim sqlCommand = "SELECT * FROM " & strTableDim objDataTable As New DataTable
objConn.Open()
command.CommandType = CommandType.Text
command.Connection = objConn
command.CommandText = sqlCommandadapter = New OleDbDataAdapter(command)DataTable = New DataTable("NFS")
adapter.Fill(DataTable)
'Sql DB vars
'Dim dtToDBComm = "INSERT INTO NFS_Raw(Time, Exch, Status) VALUES ('test', 'test', 'test')"Dim sqlServerConn As New SqlConnection(strDBConnection)Dim sqlServerCommand As New SqlCommand
 sqlServerCommand = New SqlCommand("InsertFromAccess", sqlServerConn)
sqlServerCommand.CommandType = CommandType.StoredProcedure
sqlServerConn.Open()For Each dr As DataRow In DataTable.Rows
sqlServerCommand.Parameters.Add(New SqlParameter("@Time", dr.ItemArray(0).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@Exch", dr.ItemArray(1).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@Status", dr.ItemArray(2).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@Msg", dr.ItemArray(3).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@Action", dr.ItemArray(4).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@BS", dr.ItemArray(5).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@OC", dr.ItemArray(6).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@CP", dr.ItemArray(7).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@Qty", dr.ItemArray(8).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@Product", dr.ItemArray(9).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@MMMYY", dr.ItemArray(10).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@Strike", dr.ItemArray(11).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@Limit", dr.ItemArray(12).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@StopPrc", dr.ItemArray(13).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@Type", dr.ItemArray(14).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@Rstr", dr.ItemArray(15).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@TIF", dr.ItemArray(16).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@RstrQty", dr.ItemArray(17).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@ExecQty", dr.ItemArray(18).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@WorkQty", dr.ItemArray(19).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@CxlQty", dr.ItemArray(20).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@AccountNum", dr.ItemArray(21).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@ExchMbrID", dr.ItemArray(22).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@ExchGrpID", dr.ItemArray(23).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@ExchTrdID", dr.ItemArray(24).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@MemberID", dr.ItemArray(25).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@GroupID", dr.ItemArray(26).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@NTrdID", dr.ItemArray(27).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@Acct", dr.ItemArray(28).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@ExchTime", dr.ItemArray(29).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@ExchDate", dr.ItemArray(30).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@TimeSent", dr.ItemArray(31).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@TimeProcessed", dr.ItemArray(32).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@PA", dr.ItemArray(33).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@OrderNo", dr.ItemArray(34).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@TTOrderKey", dr.ItemArray(35).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@IP", dr.ItemArray(36).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@FFT3", dr.ItemArray(37).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@FFT2", dr.ItemArray(38).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@SubmitTime", dr.ItemArray(39).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@SubmitDate", dr.ItemArray(40).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@TransID", dr.ItemArray(41).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@SessionID", dr.ItemArray(42).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@SeriesKey", dr.ItemArray(43).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@ExchangeOrderID", dr.ItemArray(44).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@Destination", dr.ItemArray(45).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@FlowDeliveryUnit", (dr.ItemArray(46))))sqlServerCommand.Parameters.Add(New SqlParameter("@TimeReceived", dr.ItemArray(47).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@CallbackReceived", dr.ItemArray(48).ToString()))
 
sqlServerCommand.ExecuteNonQuery()Next
 
sqlServerConn.Close()
objConn.Close()
End Sub
 
 
Thanks for eveones input in advance.

View 10 Replies View Related

Oracle Stored Procedures VERSUS SQL Server Stored Procedures

Jul 23, 2005

I want to know the differences between SQL Server 2000 storedprocedures and oracle stored procedures? Do they have differentsyntax? The concept should be the same that the stored proceduresexecute in the database server with better performance?Please advise good references for Oracle stored procedures also.thanks!!

View 11 Replies View Related

Stored Procedures 2005 Vs Stored Procedures 2000

Sep 30, 2006

Hi,



This Might be a really simple thing, however we have just installed SQL server 2005 on a new server, and are having difficulties with the set up of the Store Procedures. Every time we try to modify an existing stored procedure it attempts to save it as an SQL file, unlike in 2000 where it saved it as part of the database itself.



Thank you in advance for any help on this matter



View 1 Replies View Related

All My Stored Procedures Are Getting Created As System Procedures!

Nov 6, 2007



Using SQL 2005, SP2. All of a sudden, whenever I create any stored procedures in the master database, they get created as system stored procedures. Doesn't matter what I name them, and what they do.

For example, even this simple little guy:

CREATE PROCEDURE BOB

AS

PRINT 'BOB'

GO

Gets created as a system stored procedure.

Any ideas what would cause that and/or how to fix it?

Thanks,
Jason

View 16 Replies View Related

How To Search And List All Stored Procs In My Database. I Can Do This For Tables, But Need To Figure Out How To Do It For Stored Procedures

Apr 29, 2008

How do I search for and print all stored procedure names in a particular database? I can use the following query to search and print out all table names in a database. I just need to figure out how to modify the code below to search for stored procedure names. Can anyone help me out?
 SELECT TABLE_SCHEMA + '.' + TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'

View 1 Replies View Related

Using A Stored Procedure To Query Other Stored Procedures And Then Return The Results

Jun 13, 2007

Seems like I'm stealing all the threads here, : But I need to learn :) I have a StoredProcedure that needs to return values that other StoredProcedures return.Rather than have my DataAccess layer access the DB multiple times, I would like to call One stored Procedure, and have that stored procedure call the others to get the information I need. I think this way would be more efficient than accessing the DB  multiple times. One of my SP is:SELECT I.ItemDetailID, I.ItemDetailStatusID, I.ItemDetailTypeID, I.Archived,     I.Expired, I.ExpireDate, I.Deleted, S.Name AS 'StatusName', S.ItemDetailStatusID,    S.InProgress as 'StatusInProgress', S.Color AS 'StatusColor',T.[Name] AS 'TypeName',    T.Prefix, T.Name AS 'ItemDetailTypeName', T.ItemDetailTypeID    FROM [Item].ItemDetails I    INNER JOIN Item.ItemDetailStatus S ON I.ItemDetailStatusID = S.ItemDetailStatusID    INNER JOIN [Item].ItemDetailTypes T ON I.ItemDetailTypeID = T.ItemDetailTypeID However, I already have StoredProcedures that return the exact same data from the ItemDetailStatus table and ItemDetailTypes table.Would it be better to do it above, and have more code to change when a new column/field is added, or more checks, or do something like:(This is not propper SQL) SELECT I.ItemDetailID, I.ItemDetailStatusID, I.ItemDetailTypeID, I.Archived,     I.Expired, I.ExpireDate, I.Deleted, EXEC [Item].ItemDetailStatusInfo I.ItemDetailStatusID, EXEC [Item].ItemDetailTypeInfo I.ItemDetailTypeID    FROM [Item].ItemDetails IOr something like that... Any thoughts? 

View 3 Replies View Related

How To Save Stored Procedure To NON System Stored Procedures - Or My Database

May 13, 2008

Greetings:

I have MSSQL 2005. On earlier versions of MSSQL saving a stored procedure wasn't a confusing action. However, every time I try to save my completed stored procedure (parsed successfully ) I'm prompted to save it as a query on the hard drive.

How do I cause the 'Save' action to add the new stored procedure to my database's list of stored procedures?

Thanks!

View 5 Replies View Related

Stored Procedure Being Saved In System Stored Procedures

Apr 7, 2006

We recently upgraded to SQL Server 2005. We had several stored procedures in the master database and, rather than completely rewriting a lot of code, we just recreated these stored procedures in the new master database.

For some reason, some of these stored procedures are getting stored as "System Stored Procedures" rather than just as "Stored Procedures". Queries to sys.Objects and sys.Procedures shows that these procs are being saved with the is_ms_shipped field set to 1, even though they obviously were not shipped with the product.

I can't update the sys.Objects or sys.Procedures views in 2005.

What effect will this flag (is_ms_shipped = 1) have on my stored procedures?

Can I move these out of "System Stored Procedures" and into "Stored Procedures"?

Thanks!

View 24 Replies View Related

How Can I Call One Or More Stored Procedures Into Perticular One Stored Proc ?

Apr 23, 2008

Hello friends......How are you ? I want to ask you all that how can I do the following ?
I want to now that how many ways are there to do this ?



How can I call one or more stored procedures into perticular one Stored Proc ? in MS SQL Server 2000/05.

View 1 Replies View Related

SSIS And Stored Procedures Results Stored In #Tables

Mar 26, 2008

Hello
I'm start to work with SSIS.

We have a lot (many hundreds) of old (SQL Server2000) procedures on SQL 2005.
Most of the Stored Procedures ends with the following commands:


SET @SQLSTRING = 'SELECT * INTO ' + @OutputTableName + ' FROM #RESULTTABLE'

EXEC @RETVAL = sp_executeSQL @SQLSTRING


How can I use SSIS to move the complete #RESULTTABLE to Excel or to a Flat File? (e.g. as a *.csv -File)

I found a way but I think i'ts only a workaround:

1. Write the #Resulttable to DB (changed Prozedure)
2. create data flow task (ole DB Source - Data Conversion - Excel Destination)

Does anyone know a better way to transfer the #RESULTTABLE to Excel or Flat file?

Thanks for an early Answer
Chaepp

View 9 Replies View Related

Can You Do A FOR EACH Loop In T-Sql? Maybe In A Stored Procedure?

Jan 18, 2008

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?

View 9 Replies View Related

Need Help Using A Loop In A Stored Procedure

Feb 21, 2008

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! 
 
 
 
 
dim @duedatedim @email
while
 

View 4 Replies View Related

Using While Loop In Stored Procedure

Jul 31, 2014

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.

Example:
table1 data
101/03/2014
201/10/2014
301/17/2014
401/24/2014
501/31/2014
602/07/2014
702/14/2014
802/21/2014

table2 data should look after insert compelete.

col1 col2 col3
01/03/2014 01/10/2014 01/17/2014
01/24/2014 01/31/2014 02/07/2014
02/14/2014 02/21/2014

View 2 Replies View Related

Call Stored Procedure In Loop

Oct 26, 2006

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    

View 2 Replies View Related

Loop Through A Table In A Stored Procedure

May 17, 2008

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?

View 4 Replies View Related

Fetch Loop Stored Procedure

Jun 8, 2004

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

FETCH NEXT FROM crReqRec
into
@RRID
end

close crReqRec
deallocate crReqRec


GO

View 4 Replies View Related

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 appreciatedThanks
ALTER PROCEDURE dbo.OPA_GetMenuItemsASDeclare @i tinyint ,@tc tinyintSet @i = 1
/* Select for top level menu items*/
SELECT id, label, url, sortFROM mainNavORDER BY sort
Set @tc = @@rowcount
while @i <= @tc
beginSet @i = (@i + 1)
/* Select for submenu itemsSELECT id, label, url, sort, mainNavIdFROM SubNavWHERE (mainNavId = @i)ORDER BY mainNavId, sortend
RETURN
 
 

View 7 Replies View Related

How Do I Loop Thru 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

RETURN

View 6 Replies View Related

Double Loop In Stored Procedure

Mar 15, 2006

Dear All,

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.

Any ideas?

View 5 Replies View Related

Stored Procedure To Loop Through Databases

Mar 16, 2015

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

[code]....

View 7 Replies View Related

Loop Thru A SQL Table In Stored Proc?

Jul 20, 2005

Hello,Does anyone know of a way to loop thru a SQL table using code in a storedprocedure?I need to go thru each record in a small table and build a string usingvalues from the fields associated with a part number, and I can't find anyway to process each record individually. The string needs to be initializedwith the data associated with the 1st record's part number, and I need tobuild the string until a new part number is incurred. Once a new part numberis found in the table, the string is written to a different table and resetfor this next part number in the table. Need to repeat until all records inthe table have been processed.I use ADO in access 2000 to work thru local recordsets, I just can't findanyway to do this in a stored SQL procedure.Thanks for any suggestions, Eric.

View 1 Replies View Related

MS SQL Stored Procedures Inside Another Stored Procedure

Jun 16, 2007

Hi,
 Do you know how to write stored procedures inside another stored procedure in MS SQL.
 
Create procedure spMyProc inputData varchar(50)
AS
 ----- some logical
 
 procedure spMyProc inputInsideData varchar(10)
AS
   --- some logical
  ---  go
-------

View 5 Replies View Related

Calling Stored Procedures From Another Stored Procedure

May 8, 2008

I am writing a set of store procedures (around 30), most of them require the same basic logic to get an ID, I was thinking to add this logic into an stored procedure.

The question is: Would calling an stored procedure from within an stored procedure affect performance? I mean, would it need to create a separate db connection? am I better off copying and pasting the logic into all the store procedures (in terms of performance)?

Thanks in advance

John

View 5 Replies View Related

Calling A Stored Procedure Inside Another Stored Procedure (or Nested Stored Procedures)

Nov 1, 2007

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 1 Replies View Related

How To Call A Parameterized Stored Procedure Within A Loop In ASP.NET

Oct 13, 2007

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?
 
Thanks,
Carlos

View 4 Replies View Related

For Loop List Of Array In Stored Procedure

Aug 19, 2014

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

SET @counter = 1

[Code] ....

View 8 Replies View Related

Transact SQL :: Creating Stored Procedure With Cursor Loop

Sep 18, 2015

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

View 6 Replies View Related

Stored Procedure Loop Not Working, Please Advise, Code Attached

Apr 13, 2008

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

View 1 Replies View Related

Stored Procedure That Needs To Loop Through DataSet And Summarize Based On TypeCode

Apr 27, 2004

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

View 1 Replies View Related

In Stored Procedure How To Loop Through Rows In Table And Pass Parameter To EXEC SP

Apr 26, 2008

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?

View 7 Replies View Related

Only Functions And Extended Stored Procedures Can Be Executed From Within A Function. Sp_executesql Is A Extended Stored Prod

May 15, 2008

i have created the folowing function but keep geting an error.

Only functions and extended stored procedures can be executed from within a function.

Why am i getting this error!

Create Function myDateAdd
(@buildd nvarchar(4), @avdate as nvarchar(25))
Returns nvarchar(25)
as
Begin
declare @ret nvarchar(25)
declare @sqlval as nvarchar(3000)

set @sqlval = 'select ''@ret'' = max(realday) from (
select top '+ @buildd +' realday from v_caltable where realday >= '''+ @avdate +''' and prod = 1 )a'

execute sp_executesql @sqlval
return @ret
end

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved