SEARCH YOUR SOLUTION HERE  

Dead-Man switch for stored procedure

I probably should know the answer to this, but it is eluding me.

I have a stored procedure called Morning_Refresh something like this:

CREATE PROCEDURE Morning_Refresh AS
-- create snapshot
DELETE FROM Static1;
INSERT INTO Static1 Select * From Table1;
-- updates in system
UPDATE TABLE2 SET X = (Select Y FROM Table1);
UPDATE TABLE3 SET X = 'C' WHERE ID NOT IN (SELECT ID FROM TABLE1);

And so forth.

Now, all is well, there is a real chance that that Table1 that I am updating everything with could be bad. I want to add some sort of sanity check to the procedure, so that if Table1 does not meet certain requirement, NOTHING gets changed. For now, the check will simply be that there in fact are records in Table1.

How can I modify the stored procedure so that it will do nothing if there are no records in Table1

Posted On: Tuesday 20th of November 2012 12:14:53 AM Total Views:  286
View Complete with Replies




Related Messages:

Update multiple records in storedprocedure!!!   (248 Views)
I would like to update multiple records with one stored procedure call. I have an asp page that has fields that correspond to multiple rows in a table. I would like to be able to pass that information to the stored procedure and have it update all the necessary rows in one call. I don't know how many rows the user will update. Can anyone tell me how to do this
storedprocedure parameter   (135 Views)
hi i hava a stored procedure which accepts some parameters... previousy i was working with sqlconnection in asp.net to call this storeprocedure.......every thing was working fine but now when i started to use an odbc connection, when ever i call that stored procedure it give me following error msg... Although i have provided all the parameters it recuires........code is also given below...... plz help soon Server Error in '/OnlineResume' Application. ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Procedure 'login' expects parameter '@loginid', which was not supplied. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.Odbc.OdbcException: ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Procedure 'login' expects parameter '@loginid', which was not supplied. Source Error: Line 29: sqlcom.Parameters["@loginstatus"].Direction = ParameterDirection.Output;Line 30: sqlcon.Open();Line 31: sqlcom.ExecuteNonQuery();Line 32: int count=Convert.ToInt32(sqlcom.Parameters["@loginstatus"].Value);Line 33: sqlcon.Close(); Source File: f:\onlineresume\onlineresume\jps\users.cs Line: 31 Stack Trace: code to call is OdbcCommand sqlcom=new OdbcCommand("login",sqlcon); sqlcom.CommandType=CommandType.StoredProcedure; sqlcom.Parameters.Add("@loginid",OdbcType.VarChar) ; sqlcom.Parameters["@loginid"].Value=id; sqlcom.Parameters.Add("@password",OdbcType.VarChar ); sqlcom.Parameters["@password"].Value=password; sqlcom.Parameters.Add("@loginstatus", OdbcType.Int); sqlcom.Parameters["@loginstatus"].Direction = ParameterDirection.Output; sqlcon.Open(); sqlcom.ExecuteNonQuery(); int count=Convert.ToInt32(sqlcom.Parameters["@loginstatus"].Value);
Calling SQL jobs in a stored proc   (101 Views)
Is it possible to call a SQL agent job in a stored procedure. I can't find anything on the web or this forum. I didn't how to call the stored proc in ASP.net that calls the job. So, I guess my question is can some one explain to me how. thx
Looping through records in a stored procedure   (97 Views)
This is probably really easy but in an MS SQL Server 2000 database, I need to select all records from tableA and insert several records into tableB for each record in tableA, using a stored procedure. I have had no luck finding a solution to this and am in danger of putting my fist through my monitor. If anyone can save me the expense of a new monitor, please help
how to call dll from stored procedure   (106 Views)
, can you tell me how to call a DLL file from a stored procedure. need to know urgently rajesh
Unable to email from a stored procedure   (112 Views)
Hi , I'm having real big difficulties here. I developed a stored procedure that send out emails this works fine on my local machine. When I realised it to a server it didn't work, no errors were shown - but it's just simply not sending any emails. I've tried researching this loads, I also installed Outlook onto the server as reading about MAPI etc. But it's still not sending. Can anybody please help me Cheers Shane
How flexible are stored procedures?   (133 Views)
Just a quick question. In the past I have used Oracle PL/SQL to write procedures to do all sorts of things. It is possible to use if statements and loops and variables etc to manipulate your data however you want. I just want to know if this is possible in a SQL Server stored procedure I'm trying to write one but the more I get into it the more I realise it is going to need variables to store info in, a cursor that can be looped around and some if statements. Is this feasible or should I be writing this in vb or something
a stored procedure question?   (115 Views)
I have two tables.the structures like this table one (total student table) id student_id 1 10 2 11 3 12 4 13 5 14 table two (graduated student table) id student_id 1 10 2 12 I want to get all of the student id who have not graduated. that is the records in table one but not in table two.the result like this. id student_id 2 11 4 13 5 14 I want to write a stored procedure to do it.please give me a idea how to write the stored procedure.
stored procedure   (115 Views)
does anyone see anything wrong with my stored procedure, its suppose to update the terminations table through an insert. I need it to pull the data from another table the EmployeeGamingLicense_tbl and insert it into the Terminations table Code: CREATE PROCEDURE [insert_TERMINATION_TestSp] (@TM_#_1 [int], @FirstName_2 [nvarchar](50), @LastName_3 [nvarchar](50), @SocialSecurityNumber_4 [int], @DateHired_5 [datetime], @Status_6 [nvarchar](50), @Title_7 [nvarchar](50), @DepartmentName_8 [nvarchar](50)) AS INSERT INTO [GamingCommissiondb].[dbo].[TERMINATION] ( [TM #], [FirstName], [LastName], [SocialSecurityNumber], [DateHired], [Status], [Title], [DepartmentName]) VALUES ( @TM_#_1, @FirstName_2, @LastName_3, @SocialSecurityNumber_4, @DateHired_5, @Status_6, @Title_7, @DepartmentName_8) GO
Using arrays in stored procedures   (140 Views)
i want to call a stored procedure sending array of integers as parameter now in stored procedure i want to xecuute Code: select * from Products where Prod_Code IN (sent array) means "sent array" stores values i want to check in query please help
Debugging of a stored Procedure   (102 Views)
I have a stored procedure which does not have any parameters. This procedure takes data from a base table and build a tree and a stack table. Stored procedure runs fine. But still I would like to debug it using the Debugger with the SQL Server 2000 Query Analyzer. On looking around, I noticed that the stored procedure to be debugged need to have output parameters. I have 2 while loops - I would like to go row to row in the debugger and see what values are being inserted into the Tree and Stack table. It helps me because the same logic does not work when I do a massive insert of data. Please suggest what parameters I need to create, how to use them. My code is: Code: My sample stored procedure is as follows: IF Exists(Select * From Information_Schema.Tables Where Table_Name = 'Tree' and TABLE_TYPE ='BASE TABLE') Begin drop table [dbo].[Tree] End create table Tree ( OwnerPayee integer Not Null, Emp integer Not Null, Boss integer) IF Exists(Select * From Information_Schema.Tables Where Table_Name = 'Stack' and TABLE_TYPE ='BASE TABLE') BEGIN drop table [dbo].[Stack] END CREATE TABLE Stack (Stack_top INTEGER Not Null, OP Integer not Null, employee Char(10) Not Null, Lft Integer, Rgt Integer, Indentation Integer) --Insert Each Owner_Payee with a null boss into the Tree table --Grab the distinct Owner_Payee INSERT INTO Tree (OwnerPayee, Emp, Boss) Values (1472,1472, NULL) INSERT INTO Tree (OwnerPayee, Emp, Boss) Values (862336,862336, NULL) --Insert data for the subordinates INSERT INTO Tree (OwnerPayee, Emp, Boss) Values (1472,1715,1472) INSERT INTO Tree (OwnerPayee, Emp, Boss) Values (1472,1703,1472) INSERT INTO Tree (OwnerPayee, Emp, Boss) Values (1472,2109922,1472) INSERT INTO Tree (OwnerPayee, Emp, Boss) Values (1472,2109728,1703) INSERT INTO Tree (OwnerPayee, Emp, Boss) Values (1472,4033,1703) INSERT INTO Tree (OwnerPayee, Emp, Boss) Values (1472,1508,1703) INSERT INTO Tree (OwnerPayee, Emp, Boss) Values (1472,1676,1703) INSERT INTO Tree (OwnerPayee, Emp, Boss) Values (1472,408653,1703) INSERT INTO Tree (OwnerPayee, Emp, Boss) Values (1472,3290,1703) INSERT INTO Tree (OwnerPayee, Emp, Boss) Values (1472,3008,1703) INSERT INTO Tree (OwnerPayee, Emp, Boss) Values (1472,1691,1703) INSERT INTO Tree (OwnerPayee, Emp, Boss) Values (1472,2109859,1703) INSERT INTO Tree (OwnerPayee, Emp, Boss) Values (1472,2880,1703) INSERT INTO Tree (OwnerPayee, Emp, Boss) Values (1472,1679,1703) INSERT INTO Tree (OwnerPayee, Emp, Boss) Values (1472,3011,1703) INSERT INTO Tree (OwnerPayee, Emp, Boss) Values (1472,1685,1703) INSERT INTO Tree (OwnerPayee, Emp, Boss) Values (1472,2109837,2109922) INSERT INTO Tree (OwnerPayee, Emp, Boss) Values (1472,2109840,2109922) INSERT INTO Tree (OwnerPayee, Emp, Boss) Values (1472,2883,2109922) INSERT INTO Tree (OwnerPayee, Emp, Boss) Values (1472,3800,2109922) INSERT INTO Tree (OwnerPayee, Emp, Boss) Values (1472,1469,2109922) INSERT INTO Tree (OwnerPayee, Emp, Boss) Values (1472,2131854,2109922) INSERT INTO Tree (OwnerPayee, Emp, Boss) Values (1472,3402,1472) INSERT INTO Tree (OwnerPayee, Emp, Boss) Values (1472,1688,1472) INSERT INTO Tree (OwnerPayee, Emp, Boss) Values (1472,179232,1703) INSERT INTO Tree (OwnerPayee, Emp, Boss) Values (862336,862362,862336) INSERT INTO Tree (OwnerPayee, Emp, Boss) Values (862336,862339,862336) INSERT INTO Tree (OwnerPayee, Emp, Boss) Values (862336,862337,862336) INSERT INTO Tree (OwnerPayee, Emp, Boss) Values (862336,862363,862336) INSERT INTO Tree (OwnerPayee, Emp, Boss) Values (862336,2356682,862336) INSERT INTO Tree (OwnerPayee, Emp, Boss) Values (862336,627298,862336) --Get the min OwnerPayee DECLARE @minOP INTEGER Set @minOP = (Select min(OwnerPayee) from Tree) --While EXISTS(Select min(OwnerPayee) from Tree) While EXISTS(Select 1 from Tree where OwnerPayee = @minOP) BEGIN --Get the min OwnerPayee within the loop --DECLARE @minOP INTEGER Set @minOP = (Select min(OwnerPayee) from Tree) DECLARE @Stack_pointer INTEGER Set @Stack_pointer = 1 --Find the no. of rows for that OwnerPayee Declare @OP_max_lft_rgt INTEGER Set @OP_max_lft_rgt = 2 * (Select Count(*) from Tree where OwnerPayee = @minOP) print @minOP print @OP_max_lft_rgt DECLARE @lft_rgt INTEGER Set @lft_rgt = 2 DECLARE @indent integer Set @indent = 1 Print 'Before 2nd While Loop' Print @Stack_pointer Insert into Stack (Stack_Top, OP, Employee, Lft, Rgt, Indentation) Select 1, @minOP, @minOP, 1, @OP_max_lft_rgt, @indent From Tree where OwnerPayee = @minOP And Boss is null Select * from Tree where OwnerPayee = @minOP order by ownerpayee --AND Emp = (Select Employee from Stack where Stack_Top = @Stack_pointer + 1) --Start building the left and right value for each subordinate, indentation for that subordinate While @lft_rgt
Crystal report question - how to lookup data stored in a different table   (151 Views)
I am trying to add data to a Crystal report. The report runs off an SQL database so I hope someone here can help. Currently my report selects all records of a particular transaction type (DRST.TRANS_TYPE = "CBREC") and returns various data. The information I want to add is stored for a different transaction type (DRST.TRANS_TYPE = "DRINV") and so I am not sure how to retrieve it. For transaction type CBREC the DRST.REFERENCE is a unique number from the cash module, the DRST.APPLY_REFERENCE is the invoice being paid and the DRST.SECOND_REF is the name of a bank. For transaction type DRINV the DRST.REFERENCE is a unique invoice number and the DRST.SECOND_REF is an information field where we enter a second invoice number. So what I want to do is tell Crystal to take the DRST.APPLY_REFERENCE from the payment transaction record and go and find the invoice transaction record where the DRST.REFERENCE equals that DRST.APPLY_REFERENCE then bring back the DRST.SECOND_REFERENCE from that record. this information is stored in the DRST table which is the debtors transaction database. If this were excel I would use a lookup formula (which is how I am doing it currently running two separate Crystal reports). Is there anyway to do this with Crystal formulas
Deleting data by calling the stored procedure in the .NET   (164 Views)
does anyone know how to delete data from the SQL database by calling the stored procedure in the Visual Basic.NET Because I did the Delete hyperlink bounded inside a datagrid. I have already displayed the appointment date, time in the datagrid so I do not have to input any values inside it. I am having trouble calling from the .NET. The stored procedure itself can work in the SQL Server database. Can someone pls help
How to insert code into every stored procedure in a database   (128 Views)
I'm working with SQL Server 2008 and now I need to insert 3 lines code into the beginning of every stored procedure in database. (The purpose to do this is to track execution history) There are about 3000 stored procedures so definitely I dont wanna do it manually. Here is an example about how I wanna the sp looks like: ALTER PROCEDURE [dbo].[StoredProcudureSample] @variable int AS SET NOCOUNT ON DECLARE @ProcName VARCHAR(200) SET @ProcName = OBJECT_NAME(@@PROCID) EXEC [dbo].[Sproc_SaveStoredProcedureExecute] @StoredProcedureName = @ProcName --original stored procedure body ... Does anyone have good idea about this TIA, Julie
SQL Recursie view or stored procedure   (131 Views)
I have a table with a recursive parent-child relation: Parent_ID refers to Line_ID example of how that may look: Line_ID . Parent_ID ------------------ 1 . . . . . null -- higest item (no parent) 2 . . . . . 1 3 . . . . . 1 4 . . . . . 2 5 . . . . . 3 6 . . . . . 3 7 . . . . . 5 8 . . . . . 5 9 . . . . . 8 10 . . . . null What i need is the following fucntion/view/..: INPUT = any Line_ID OUTPUT = table with ALL its LOWEST children (a Line_ID that has no Parent_ID refering to it) example: . . 1 . . . . . . . 10 |----| 2 . . 3 | . |---| 4 . 5 . .6 . .|-| . .7 8 . . . | . . . 9 1 would give 4, 6, 7, 9 2 would give 4 3 would give 6, 7, 9 4 would give null 5 would give 7, 9
Append stored procedure   (114 Views)
I need to make this into an APPEND stored procedure, basically one that inserts data from one table to the other once the record becomes inactive. This is what I have so far I need to the data inserted from another table called the EmployeeGamingLicense (active table). I need for data to get inserted as soon as the Status field shows the record as TERMINATED. Can anyone help pls Code: CREATE PROCEDURE [insert_TERMINATION_TestSp] (@TM_#_1 [int], @FirstName_2 [nvarchar](50), @LastName_3 [nvarchar](50), @SocialSecurityNumber_4 [int], @DateHired_5 [datetime], @Status_6 [nvarchar](50), @Title_7 [nvarchar](50), @DepartmentName_8 [nvarchar](50)) AS INSERT INTO [GamingCommissiondb].[dbo].[TERMINATION] ( [TM #], [FirstName], [LastName], [SocialSecurityNumber], [DateHired], [Status], [Title], [DepartmentName]) VALUES ( @TM_#_1, @FirstName_2, @LastName_3, @SocialSecurityNumber_4, @DateHired_5, @Status_6, @Title_7, @DepartmentName_8) GO
How to executed extended stored procedures as non-sysadmin users????   (204 Views)
Hi , I have a scenario related to SQL 2005 Security, I have created database "db1" as "sa" user. Steps followed 1.Created a "dummy" login 2.Created a "dummy" user,mapped to "dummy" login for "db1" database and made database owner. 3.logged in as "dummy" login and created a stored procedure which contains EXEC xp_cmdshell Statement and while executing the stored procedure, it is throwing below error. Msg 15153, Level 16, State 1, Procedure xp_cmdshell, Line 1 The xp_cmdshell proxy account information cannot be retrieved or is invalid. Verify that the '##xp_cmdshell_proxy_account##' credential exists and contains valid information. 4. Next, what i done is, i have turned on xp_cmdshell feature and created a proxy account using Windows Authentication Account. 5. I Again tried to connect to "dummy" login and tried to execute the stored procedure and now it worked. Now my Question is, what does the below statement do. EXEC sp_xp_cmdshell_proxy_account 'domain\username', 'domain_user_password' What affect is there if i created this new account. I know this would create a Proxy Credential but i am wondering how would the "dummy" user can able to utilize this Proxy Account internally because externally/explicitly am not giving/executing any command to make use of this newly created proxy account. Can anyone please elaborate on how internally this Proxy Account is able to be utilized by the user "dummy" when he is getting logged in. Am just curious about what is happening internally In other words, just want to know how this proxy account is made available to "dummy" user when he is getting logged in. Commands Used to replicate the scenario. Step1 : Create the database as "sa" user use master go CREATE DATABASE db1 go Step2 : Create a login -- create a login "dummy" as "sa" user USE [master] GO CREATE LOGIN [dummy] WITH PASSWORD=N'dummy', DEFAULT_DATABASE=[db1], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF GO Step3 : Create a User in "DB1" database and make him database owner. USE [db1] GO CREATE USER [dummy] FOR LOGIN [dummy] GO USE [db1] GO EXEC sp_addrolemember N'db_owner', N'dummy' GO Step4 : login as "dummy" and create a stored procedure which uses xp_cmdshell and when you try to execute the stored procedure. It will throw an error. CREATE PROC USP_TEST AS BEGIN DECLARE @STR VARCHAR(100) SET @STR = 'GUEST' PRINT @STR EXEC sys.xp_cmdshell 'dir c:\*.*' END EXEC USP_TEST /* GUEST Msg 15153, Level 16, State 1, Procedure xp_cmdshell, Line 1 The xp_cmdshell proxy account information cannot be retrieved or is invalid. Verify that the '##xp_cmdshell_proxy_account##' credential exists and contains valid information. */ Step5 : Enable the xp_cmdshell feature as "sa" user EXECUTE sp_configure 'show advanced options', 1 RECONFIGURE WITH OVERRIDE GO EXECUTE sp_configure 'xp_cmdshell', '1' RECONFIGURE WITH OVERRIDE GO EXECUTE sp_configure 'show advanced options', 0 RECONFIGURE WITH OVERRIDE GO EXEC sys.xp_cmdshell 'dir c:\*.*' Step6 : Now login as Windows Authentication user and try to create a proxy account EXEC sp_xp_cmdshell_proxy_account 'GSPSTRAIL\Administrator', 'mychlocallogin' Step7: login "dummy" user and again try to execute the stored procedure. EXEC USP_TEST Step8: To drop the proxy account, execute the below peice of code. /* -- To drop a Proxy Account -- login as Windows Authentication and execute the below command /* EXEC sp_xp_cmdshell_proxy_account NULL */
Help on stored procedure that inserts & selects   (174 Views)
hello, I'm working on a page that has a grid and a calendar. I want to have the grid display data based on what the user clicks in the grid. It could be one day or many days. I had a store proc that selected records and it worked alright, if the data was already there. The problem is when I don't have the data there. ( This is a timesheet app and I know my design might not be great - hence my problems). Anyway, I was trying to combine an insert and select into one store proc, thinking this could help me, but it seems like it only helps me when I have one row of data - unless you know how to do this. How do I make my SP insert work when there is a range of dates Maybe not possible...... Here's my code Code: ALTER PROCEDURE dbo.TESTING ( @PRESENT int, @TSKey int , @Tdate datetime, @TotHours int, @Amend datetime , @beginTime as datetime, @endTime as Datetime ) AS IF ( @PRESENT =1 ) BEGIN --TEST INSERT INTO TIMEDATA (tkey,TDate) values (@TSKey,@Tdate) where ( tdate = @beginTime) --this does not work where tdate > @begindate and tdate < @enddate END ELSE BEGIN UPDATE TimeData SET TotHours = @TotHours , AuthAMend = @AMend where tkey = @tskey and TDate=@Tdate END
passing parameter to stored procedure in Access   (142 Views)
This is my first time using sql server and there's lots I'm still figuring out. I know how to pass a parameter to a stored procedure when my stored procedure is the data source for a report. But this is different - I created a stored procedure that gives data needed for a mail merge. The caseID is variable. I have a button on my case form that, when clicked, outputs the stored procedure to an excel document so that it can be used as a mail merge source. Right now it (as expected) prompts the user to enter the Case ID. But that is a hidden field anyway. I want to take the Case ID from the current record and make it the value of @CaseID. Can anyone tell me how to do this
need help creating stored procedure   (235 Views)
hi , i nid to create a stored procedure which clears the database every 3 months for example on June 1 [sep 1,dec 1, etc] the stored procedure will delete all records dated b4 june 1. i have browse thru different tutorials on the web and have read abt cursors, but i do not know how to use it. could someone kindly give me a head start thanx