f



Error in store procedure

Hi

I've the following SP:

-----------
CREATE PROCEDURE spServicios
@numero int,
@maxdif int,
@resultado int OUTPUT
AS
BEGIN
      SET ROWCOUNT 1
      UPDATE control SET registro = getdate()
             WHERE  (fecha >= getdate()
                    AND fecha < dateadd(mi, @maxdif, getdate())
                    AND clientes_codigo IN (SELECT 
clientes_codigo FROM 					telefonos WHERE numero = @numero)
      SELECT @resultado = @@rowcount
      SET ROWCOUNT 0
END

SELECT @resultado
------------

But when I try to compile I get the message: Incorrect syntax near the 
keyword 'SELECT'


Any idea ?

Thanks in advance

J




0
jleyba (23)
2/9/2005 1:34:08 PM
comp.databases.ms-sqlserver 14567 articles. 1 followers. janinepelletier (108) is leader. Post Follow

2 Replies
301 Views

Similar Articles

[PageSpeed] 45

WHERE numero = @numero) 
should be
WHERE numero = @numero))

0
strider5 (17)
2/9/2005 1:44:17 PM
Javier (jleyba@manresa.net) writes:
> CREATE PROCEDURE spServicios
> @numero int,
> @maxdif int,
> @resultado int OUTPUT
> AS
> BEGIN
>       SET ROWCOUNT 1
>       UPDATE control SET registro = getdate()
>              WHERE  (fecha >= getdate()
>                     AND fecha < dateadd(mi, @maxdif, getdate())
>                     AND clientes_codigo IN (SELECT 
> clientes_codigo FROM    telefonos WHERE numero = @numero)
>       SELECT @resultado = @@rowcount
>       SET ROWCOUNT 0
> END
> 
> SELECT @resultado
> ------------
> 
> But when I try to compile I get the message: Incorrect syntax near the 
> keyword 'SELECT'

Count your parentheses.


-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
0
esquel (7068)
2/9/2005 11:33:20 PM
Reply:

Similar Artilces:

How to FAST and EASY ENCRYPTION ALL Stored Procedure in my MS-SQL Database ?
My MS-SQL 2000 Database have 50 more Stored Procedure . How to FAST and EASY ENCRYPTION ALL Stored Procedure in my MS-SQL Database? (mickyang@gmail.com) writes: > My MS-SQL 2000 Database have 50 more Stored Procedure . > > How to FAST and EASY ENCRYPTION ALL Stored Procedure in my MS-SQL > Database? I assume that you have the source code in files. Use a text editor that supports Find/Replace with regular expressions. Open all files in the editor. The change " AS *$" to " WITH RECOMPILE AS" in all files. Good text editor to use is TextPad, http://www.textpad.com. I would also recommend that you keep a backup directory, and use a compare merge/tool that permits you easily undo false positives. An excellent tool for this is Beyond Compare, http://www.scootersoftware.com. If you want something more robust, you would need to write a program that parses the file. Hardly worth it, if you only have 50+ procedures. -- Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx ...

Stored procedure from stored procedure
Is it possible to create a stored procedure from a stored procedure? When I attempt this inanity, it doesn't blow up until syntax error at the first "end procedure" statement of the SP I'm trying to create within the outer SP. Can this be done? Using IDS 7.31-UC4 on Solaris 2.7/Intel (don't ask). On 24 Sep 2003 11:47:09 -0700, red_valsen@yahoo.com (Red Valsen) wrote: >Is it possible to create a stored procedure from a stored procedure? >When I attempt this inanity, it doesn't blow up until syntax error at >the first "end procedure" statement of the SP I'm trying to create >within the outer SP. Can this be done? > >Using IDS 7.31-UC4 on Solaris 2.7/Intel (don't ask). Write it out to flatfile and execute the create from command line using dbaccess??? John Carlson wrote: > On 24 Sep 2003 11:47:09 -0700, red_valsen@yahoo.com (Red Valsen) > wrote: > > >>Is it possible to create a stored procedure from a stored procedure? >>When I attempt this inanity, it doesn't blow up until syntax error at >>the first "end procedure" statement of the SP I'm trying to create >>within the outer SP. Can this be done? >> >>Using IDS 7.31-UC4 on Solaris 2.7/Intel (don't ask). > > > Write it out to flatfile and execute the create from command line > using dbaccess??? As John said, somewhat obliquely, there is...

calling a stored procedure from a stored procedure
Hi Folks, I'm trying to define a (I thought) trivial stored procedure (SP) as a front-end to the standard SP "dbms_system.set_ev", which I want to use to generate a 10046 tracing event. I'm trying to execute the following PL/SQL: create or replace procedure rob_enable_tracing ( sid in integer, serial in integer ) is begin dbms_system.set_ev(sid, serial, 10046, 4, ''); end; and getting the error: PLS-00201: identifier 'DBMS_SYSTEM.SET_EV' must be declared If I provide the schema in which "set_ev" is defined, in this case "SYS", as in: create or replace procedure rob_enable_tracing ( sid in integer, serial in integer ) is begin sys.dbms_system.set_ev(sid, serial, 10046, 4, ''); end; I get: PLS-00201: identifier 'SYS.DBMS_SYSTEM' must be declared Any suggestions? thanks, RU On May 10, 1:25 pm, RU <r...@vakuum.de> wrote: > Hi Folks, > > I'm trying to define a (I thought) trivial stored procedure (SP) as > a front-end to the standard SP "dbms_system.set_ev", > which I want to use to generate a 10046 tracing event. I'm trying > to execute the following PL/SQL: > > create or replace procedure rob_enable_tracing ( > sid in integer, > serial in integer > ) > is > begin > dbms_system.set_ev(sid, serial, 10046,...

Trigger: To fill another Database with using Stored Procedures of the other Database
Hello everyone, I face currently a problem where I could need some input for searching the source of the Problem System: SQL Server 9.0 I fill from Database A with triggers Database B, everything works fine. On Database B there is a Stored Procedures that checks the records and add additional information accordingly, this Stored Procedures is normally called by the application on "update and insert" in the according table. When I try to call this Stored Procedures from the Database A, the trigger does not work anymore, even if I do a try catch over the whole trigger, he never reach the Catch and the insert I try to do there to get the error message. On both Databases the user, that is taken to execute the trigger is existent and DB-Owner of both Databases. If I go and execute the Stored Procedures manually after an insert or update to Database B everything works fine. I also already tried to check on Database B if there is an insert or update from Database A and if, to execute the Stored Procedures, with the same result, nothing and all happens anymore, neither update on Database A and also not on Database B. And also I cant catch the error as the Try/Catch is not working. Hope I could explain it understandable and maybe someone remembers already having the same problem. Thanks & Best regards Pascal (pascal.baetscher@gmail.com) writes: > I face currently a problem where I could need some input for searching > the source of the Problem > >...

Storing all stored procedures in one database to minimize different connection strings
In order to minimize the number of connection strings I have to use to access different databases on the same Sql Server, I was considering storing all stored procedures in just one database. I want to do this because connection pooling in my application - ASP.NET is based on this connection string. So if I need to access 6 different databases on one sql server & set 6 different connection strings, I end up creating 6 different connection pools. Other than it might create more management work for the DBA, are there any performance implications with implementing this scheme? Do stored p...

Getting Data from a storeed procedure in a stored procedure
What I am looking to do is use a complicated stored procedure to get data for me while in another stored procedure. Its like a view, but a view you can't pass parameters to. In essence I would like a sproc that would be like this Create Procedure NewSproc AS Select * from MAIN_SPROC 'a','b',..... WHERE ......... Or Delcare Table @TEMP @Temp = MAIN_SPROC 'a','b',..... Any ideas how I could return rows of data from a sproc into another sproc and then run a WHERE clause on that data? Thanks Chris Auer Instead of using the complicated stored procedure, why don't you use a user defined function? Hi You may be better with a table function, but for a stored procedure you can create a (temporary) table and use the INSERT...EXEC statement (see Books online) e.g. CREATE TABLE #temporary_table (col1 char(1), col2 char(1) ) INSERT INTO #temporary_table (col1, col2 ) EXEC my_proc SELECT col1, col2 FROM #temporary_table John "Chris Auer" <chris.auer@gmail.com> wrote in message news:1117740278.709876.4550@o13g2000cwo.googlegroups.com... > What I am looking to do is use a complicated stored procedure to get > data for me while in another stored procedure. > > Its like a view, but a view you can't pass parameters to. > > In essence I would like a sproc that would be like this > > Create Procedure NewSproc > AS > > Select * from MAIN_SPROC 'a','b',..... > ...

Stored procedure error ....
Trying to use the "DB2 Development Center" on UDB V8 on my Windows XP. I am new to java stored procedures. The stored procedure code I am using is at: https://www6.software.ibm.com/dw/education/dm/db2spdc/sp.java I was able to successfully 'build' the stored procedure that I created using Development Center, however, at run time it is showing the following error: A database manager error occurred.[IBM][CLI Driver][DB2/NT] SQL4302N Java stored procedure or user-defined function "DB2ADMIN.CHECKRETAILERCREDIT", specific name "SQL040608143836354" aborted with an exception "". SQLSTATE=38501 Following is the output from db2diag.log: /Output-start/ 2004-06-09-09.48.39.030000 Instance:DB2 Node:000 PID:2512(db2fmp.exe) TID:1884 Appid:none BSU Java support sqlejCallJavaRoutine_dll Probe:315 Exception thrown during routine invocation: 0x0012F2EC : 0x00E9D060 `... 2004-06-09-09.48.39.186000 Instance:DB2 Node:000 PID:2512(db2fmp.exe) TID:1884 Appid:none BSU Java support sqlejLogException Probe:10 ADM10000W A Java exception has been caught. The Java stack traceback has been written to the db2diag.log. 2004-06-09-09.48.39.186001 Instance:DB2 Node:000 PID:2512(db2fmp.exe) TID:1884 Appid:none BSU Java support sqlejLogException Probe:10 java.lang.NullPointerException at CheckRetailerCredit.checkRetailerCredit(CheckRetailerCredit...

Error on opening a database: MS JEt database says someone tries to modify the file
Hello, I've received a databasefile from a friend, which seems to be corrupted. When i try to open the file it gives the error: The Microsoft Jet Database Engine has stopped the proces, since you and another user are trying to modify the same data (translated from dutch). I can't do anythinh with this file. Even importing from another database or Compact and Repairing gives me the same message. Has anyone got a clue how to save the data in this .mdb file. Greetings, Remco Remco Groot Beumer wrote: > Hello, > > I've received a databasefile from a friend, which seems to be corrupted. > When i try to open the file it gives the error: > The Microsoft Jet Database Engine has stopped the proces, since you and > another user are trying to modify the same data (translated from dutch). > > I can't do anythinh with this file. Even importing from another database or > Compact and Repairing gives me the same message. Has anyone got a clue how > to save the data in this .mdb file. > > Greetings, > > Remco > > if your friend doesn't have a backup, it's shot. There are companies that you can sent it to which specialize in getting data out of corrupt Access databases....but it'll cost you a pretty penny! ...

Error on opening a database: MS JEt database says someone tries to modify the file #2
Hello, I've received a databasefile from a friend, which seems to be corrupted. When i try to open the file it gives the error: The Microsoft Jet Database Engine has stopped the proces, since you and another user are trying to modify the same data (translated from dutch). I can't do anythinh with this file. Even importing from another database or Compact and Repairing gives me the same message. Has anyone got a clue how to save the data in this .mdb file. Greetings, Remco ...

stored procedured call error
Hello, I have a problem with calling stored procedure from PHP using Oracle 8 functions. The line which produces the error is $s = OCIParse($db_link, " CODE OF STORED PROCEDURE "); OCIExecute($s); <-- ? Connection to the DB is ok, before these lines couple of queries are executed well. The code of SP is ok too, since compilation from the client produces no errors. The error code is produced by mentioned line is: "ORA-00000: normal, successful completion" TIA for any clue K. Kleist wrote: > Hello, > > I have a problem with calling stored procedure from PHP using Oracle 8 > functions. > > The line which produces the error is > > $s = OCIParse($db_link, " > > CODE OF STORED PROCEDURE > > "); > > OCIExecute($s); <-- ? > > Connection to the DB is ok, before these lines couple of queries are > executed well. > > The code of SP is ok too, since compilation from the client produces no > errors. > > The error code is produced by mentioned line is: > "ORA-00000: normal, successful completion" > > TIA for any clue > > K. ORA-00000 is not an error as you acknowledge and as it clearly states. Write code to handle it. -- Daniel A. Morgan http://www.psoug.org damorgan@x.washington.edu (replace x with u to respond) On 2005-08-31, Kleist <kleist@tlen.pl> wrote: > H...

Stored Procedure, error when executing (
Hi everyone, first time here, so I'm sorry if this has been covered already ages ago. :( I am trying to learn T-SQL and Stored Procedures and bought the book on these topics by Djan Sunderic, Publisher McGraw Hill/Osborne. I'm already stuck on my first Stored Procedure and getting error messages that I cannot understand. I've already tried Google and Microsoft online to no avail. I do have the .NET Framework on my system and use it for programming in C# sometimes and by the looks of the error, it's something to do with that? Please note I am only using SQL Server Express. Here is the first Stored Procedure: SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE ap_Hello AS BEGIN SET NOCOUNT ON; SELECT 'Hello world' SELECT * from dbo.EqType END GO The error in its entirety is this: Msg 6522, Level 16, State 1, Procedure ctrd_DDL_PROCEDURE_EVENTS_vb, Line 1 A .NET Framework error occurred during execution of user defined routine or aggregate 'ctrd_DDL_PROCEDURE_EVENTS_vb': System.UnauthorizedAccessException: Access to the path 'c: \server_login.log' is denied. System.UnauthorizedAccessException: at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath) at System.IO.FileStream.Init(String path, FileMode mode, FileAccess access, Int32 rights, Boolean useRights, FileShare share, Int32 bufferSize, FileOptions options, SECURITY_ATTRIBUTES secAttrs, String msgPath, Boolean bFromProxy) at System.I...

Error is oracle stored procedure
I want to check recnum before updating salary as in teh query. I will be doing it thru oracle stored procedure and by declaring variable vDupTxt. It gives me error ORA-01722 invalid number ORA-06512. How to handle this? vDupTxt := ''' 1, 5 '''; update employee set salary = 100 where recnum in ( vDupTxt ) Thanks, KK wrote: > I want to check recnum before updating salary as in teh query. > > I will be doing it thru oracle stored procedure and by declaring > variable vDupTxt. > > It gives me error ORA-01722 invalid number ORA-06512. How to handle > this? > > vDupTxt := ''' 1, 5 '''; > > update employee set salary = 100 where recnum in ( vDupTxt ) > > Thanks, You need to read the newsgroup much better than you do. That will not work and it's already been explained in the thread listed below: http://groups.google.com/group/comp.databases.oracle.server/browse_thread/thread/e5c8ca75705daf52 Please read this. David Fitzjarrell ...

Using stored procedure result set in another stored procedure
I've been developing a stored procedure that uses a user defined function in the query portion of the procedure. However, since the end product needs to allow for dynamic table names, the UDF will not work. I've been trying to get this to work with converting the UDF to a procedure, but I'm having no luck. Here is the background on what I'm trying to accomplish. I need to perform a sub-identity on a table, I have the normal identity set, but there are multiple duplicates in the table and I need each set of duplicates numbered also (1,2,3,4 for duplicate set 1, 1,2,3 for dup 2). Here is what I have using the UDF (the UDF returns a variable table with indetity and ID for each record) ********** UDF ************* CREATE FUNCTION dbo.setDuplicateTransactions(@accountNumber as varchar(50)) RETURNS @dupTransactions TABLE ( ID int IDENTITY, transactionID int ) AS BEGIN INSERT @dupTransactions SELECT t1.transactionID FROM providerTransactions t1 WHERE t1.accountNumber = @accountNumber ORDER BY t1.transactionID RETURN END ******** Stored Procedure ************ CREATE PROCEDURE dbo.sp_parseTransactions AS DECLARE @accountNumber varchar(50) DECLARE temp_cursor CURSOR FORWARD_ONLY FOR SELECT t1.accountNumber FROM providerTransactions t1 GROUP BY t1.accountNumber HAVING MAX(isNull(t1.duplicateCount,0)) != COUNT(t1.transactionID) ORDER BY t1.accountNumber OPEN temp_cursor FETCH NEXT FROM temp_cursor INTO @acco...

Syntax error in stored procedure
Hi, I have problems with one stored procedure that I am trying to develop in my informix database. The procedure is at folloews create procedure updateFlags(emp LIKE employee.idEmployee,check like dailycheck.checkdate) DEFINE selected integer; FOREACH cursor FOR select first 1 iddailycheck into selected from dailycheck DC where DC.idEmployee=emp and DC.checkdate=check and DC.modificationtype='PLANNED' order by DC.modificationdate desc; update dailycheck set lastPlanned=1 where iddailycheck=selected; end foreach; end procedure; I am using dbaccess, what the result is always the same.... Can you help me???? loose the ; at > FOREACH cursor FOR select first 1 iddailycheck > into selected from dailycheck DC > where DC.idEmployee=emp and DC.checkdate=check > and DC.modificationtype='PLANNED' order by > DC.modificationdate desc; -------------------------^^---HERE------- see you Superboer. Unholy schreef: > Hi, I have problems with one stored procedure that I am trying to > develop in my informix database. > > The procedure is at folloews > > create procedure updateFlags(emp LIKE employee.idEmployee,check like > dailycheck.checkdate) > DEFINE selected integer; > FOREACH cursor FOR select first 1 iddailycheck > ...

DB2 COBOL stored procedure to UDB SQL stored procedure
I have been given the task of taking a 3,200 line COBOL stored procedure and duplicating the same functionality in UDB 7.2 on the Windows platform with a procedural SQL stored procedure. I have fiddled with procedural SQL stored procs on UDB, but mostly short,trivial ones. Looking for good advice, links, etc. on the best approach to this. There doesn't seem to be the concept of subroutines within a stored proc, so I am guessing one main stored proc that may be calling multiple smaller stored procs? Just found out about this need late this afternoon and wondering what the best approach is to doing such a task. Any and all ideas most welcome. thanks, fv <PJackson@txfb-ins.com> wrote in message news:1175743257.812351.234300@y80g2000hsf.googlegroups.com... >I have been given the task of taking a 3,200 line COBOL stored > procedure and duplicating the same functionality in UDB 7.2 on the > Windows platform with a procedural SQL stored procedure. I have > fiddled with procedural SQL stored procs on UDB, but mostly > short,trivial ones. Looking for good advice, links, etc. on the best > approach to this. There doesn't seem to be the concept of subroutines > within a stored proc, so I am guessing one main stored proc that may > be calling multiple smaller stored procs? Just found out about this > need late this afternoon and wondering what the best approach is to > doing such a task. > > Any...

Assessment of Database Stored Procedures
I am attempting to compile a list of questions that will enable me assess 'at risk' stored procedures that need to be remediated in order to minimize unplanned downtime and enhance database performance. Is there a subset of criteria (parameters) from the MS SQL Server Best Practices Analyzer that can be used for this purpose? Any pointers to the appropriate documentation is most welcome. Thanks in advance. It depends what you class as acceptable criteria. It depends on the nature of the stored procedures. For example, if it's a simple SELECT statement , returning it in 6 seconds may be to slow for the end user., and could point to something else being the problem. Check http://www.sql-server-performance.com/articles_audit.asp for a systematic approach -- Jack Vamvas ___________________________________ Need an IT job? <a href="http://www.itjobfeed.com">uk it jobs</a> "Umar Reyi" <iyerra@indiatimes.com> wrote in message news:1177366044.733092.100680@y80g2000hsf.googlegroups.com... >I am attempting to compile a list of questions that will enable me > assess 'at risk' stored procedures that need to be remediated in order > to minimize unplanned downtime and enhance database performance. Is > there a subset of criteria (parameters) from the MS SQL Server Best > Practices Analyzer that can be used for this purpose? Any pointers to > the appropriate documentation is most welcome. > > T...

Creating Stored Procedure error
Trying to create a new stored procedure from code, running the code below I just cut and pasted directly from the MS Knowledge Base, I get the following error message: Line 1: Incorrect syntax near 'prmCity' Any ideas? lq Code: Public Function CreateProc() Dim strProc As String strProc = "Create Procedure qryCustByCity " & _ "(prmCity varchar) as " & _ "select * from Customers where City = prmCity" CurrentProject.Connection.Execute strProc End Function On 30 Oct 2003 14:18:53 -0800, laurenquantrell@hotmail.com (Lauren Quantrell) wrote: You forgot the @ sign. Try this: strProc = "Create Procedure qryCustByCity " & _ "(@prmCity varchar) as " & _ "select * from Customers where City = @prmCity" Note that an alternative to creating procedures on the fly is to send the statement to a sproc that uses EXECUTE to run the passed in statement. -Tom. >Trying to create a new stored procedure from code, running the code >below I just cut and pasted directly from the MS Knowledge Base, I get >the following error message: > > Line 1: Incorrect syntax near 'prmCity' > >Any ideas? >lq > > >Code: > >Public Function CreateProc() > > Dim strProc As String > > strProc = "Create Procedure qryCustByCity " & _ > "(prmCity varchar) as &quo...

Stored procedure error handling
OK, i'm trying to do some error checking on stored procedures and am following the advise in Erland Sommarskog's 'Implementing Error Handling with Stored Procedures' document. Can anybody help with my stored procedures and why it keeps erroring at the '-- Create new Address Detail stage'? The errorCode value that is being return in my web app is 0, so i'm not even sure why it's even raising the error!! Rather than executing the INSERT INTO AddressDetail in my CreateSupplier procedure and checking for errors, i'd like to be able execute a CreateAddressDetail SP, so that i can reuse it throughout my web app. New suppliers must have a contact address associated with it, so if there's an error creating the suppliers address, i need my CreateSupplier stored procedure to ROLLBACK and not create the new supplier. That's why i'm not doing two separate calls to the procedures from my app code. Any suggestions are most appreciated. Many thanks Dan Williams. CREATE PROCEDURE CreateSupplier @supplierName varchar(50), @userId bigint, @address varchar(50), @town varchar(50), @county varchar(50), @postCode varchar(15), @contactName varchar(50) AS BEGIN DECLARE @newSupplierId as bigint DECLARE @newAddressDetailId as bigint DECLARE @errorCode as bigint SET NOCOUNT ON BEGIN TRAN INSERT INTO Supplier (supplierName, accOpenedBy, accOpenedDate) VALUES (@supplierName, @userId, getDate()) SET @newSupplierId = SCOPE_IDENTITY() ...

Oracle Stored Procedures VERSUS SQL Server Stored Procedures
I want to know the differences between SQL Server 2000 stored procedures and oracle stored procedures? Do they have different syntax? The concept should be the same that the stored procedures execute in the database server with better performance? Please advise good references for Oracle stored procedures also. thanks!! <jrefactors@hotmail.com> wrote in message news:1120793932.351921.5760@g43g2000cwa.googlegroups.com... > I want to know the differences between SQL Server 2000 stored > procedures and oracle stored procedures? Do they have different > syntax? The concept should be the same that the stored procedures > execute in the database server with better performance? > > Please advise good references for Oracle stored procedures also. > > thanks!! > SQL Server Stored procedures use transact sql and run on SQL Server Oracle Stored Procedures use pl/sql (modeled on ADA) and run on Oracle. Oracle!=SQL Server They use two very different concurrent models and the syntax is totally different. (eg you don't need to create temp tables in Oracle like in SQL Server) otn.oracle.com and look under documentation. Jim On Thu, 07 Jul 2005 20:38:52 -0700, jrefactors wrote: > I want to know the differences between SQL Server 2000 stored > procedures and oracle stored procedures? Do they have different > syntax? No, you just have to obtain Microsoft version of PL/SQL. -- http://www.mgogala.com On Thu, 07 Jul 2005 20:38:52 -0700...

Trapping Errors in Stored Procedure
Hi there, I am converting a large PL/SQL project into Transact-SQL and have hit an issue as follows: I have a PL/SQL procedure that converts a string to a date. The procedure does not know the format of the date in the string so it tries loads of formats in converting the string to a date until it succeeds. After trying each potential format it uses the Oracle 'EXCEPTION WHEN OTHERS' construct to trap the failure so it can try another format. Is it possible to do this with SQLServer ? If I do a CONVERT and it is not one of the standard formats it fails. This is part of a backgroun...

Error message with stored procedure
Could someone help me, I'm getting this error message: Procedure or function UpdateResources has too many arguments specified. Here's my stored procedure, I am trying to update four columns, two are in the same table and two are in different tables. Any suggestions? CREATE PROCEDURE [UpdateResources] (@titleID int, @title varchar (255), @description varchar (2047), @quantityowned int) AS Update Titles SET [title]=@title, [description]=@description where titleID=@titleid Update classifications set [description]=@description Update resources set [quantityowned]=@quantityowned GO I have two columns called description, on in the titles table and one in the classification table. Thanks! This error message appears when you try to call a stored procedure and supply more parameters than those declared for that procedure. Look at the code where you call the UpdateResources procedure and check that you pass the correct parameter list. HTH, Plamen Ratchev http://www.SQLStudio.com On Oct 29, 2:52 pm, "Plamen Ratchev" <Pla...@SQLStudio.com> wrote: > This error message appears when you try to call a stored procedure and > supply more parameters than those declared for that procedure. Look at the > code where you call the UpdateResources procedure and check that you pass > the correct parameter list. > > HTH, > > Plamen Ratchevhttp://www.SQLStudio.com I've checked everything and can't figure it out. H...

How to return a Pk value from one stored procedure to another stored procedure
Dear All, I have one stored procedure like sp_insertEmployee Employee Table Fileds Eno int pk, ename varchar(100), designation varchar In stored Procedure After inserting the ename and designation it has to return the Eno pk Id automatically I have another Department deptno int pk, Eno int fk, Depname varchar In this stored procedure I need to execute the sp_insertEmployee Stored procedure and we need that Pk return value after executing that By using that Id in this Department table we will insert the eno can u help me out on this issue Thanks CREATE PROCEDURE sp_insertEmployee ( @Ename VARCHAR(100), @Designation VARCHAR(100), --Length? @Eno INT OUTPUT ) AS BEGIN --Procedure INSERT Employee ( Ename VARCHAR(100), Designation VARCHAR(100) --?? ) VALUES ( @Ename, @Designation ) SET @Eno = SCOPE_IDENTITY() END --Procedure ------------------------------ When you execute this procedure from the procedure that inserts the record in the cross-reference table between department and employee, call it like so. EXEC sp_insertEmployee @Ename, @Designation, @Eno OUTPUT Alternately, you can also have the sproc RETURN the @Eno, instead of having it as an OUTPUT parameter. CREATE PROCEDURE sp_insertEmployee ( @Ename VARCHAR(100), @Designation VARCHAR(100) --Length? ) AS BEGIN --Procedure INSERT Employee ( Ename VARCHAR(100), Designation VARCHAR(100) --?...

Re: Error -710 from stored procedure
Hi Usually this problem can be fixed by running an "update statistics for procedure my_procedure". Anyway, It's the same to run on weekly or monthly basis "update statistics" for the database Later Omar Muqoz ----- Original Message ----- From: "Heinz Weitkamp" <heinz.weitkamp@westfleisch.de> To: "user-group Informix (E-Mail)" <informix-list@iiug.org> Sent: Wednesday, September 21, 2005 9:12 AM Subject: Error -710 from stored procedure > Hi all, > > IDS 7.31 UD4 > SCO Unix 3.2 5.0.6 > > Sometimes when we execute a stored procedure it fails with the error: > > -710, Table (informix.systables) has been dropped,altered or renamed. > The stored procedure makes a > SELECT ur_benkrz_to_bennr(imandant,iben) INTO ben > FROM systables WHERE tabid=1; > > The stored procedure ur_benkrz_to_bennr only makes an SELECT INTO. No > modifications on any table. > > At execution time of the stored procedure Systables was not dropped,altered > or renamed. > I altered an other table (drop (primary) key, create (primary) key) in the > database, but such an action don't alter the > systables (i am right?). > > What can be the issue of such an error? > > Any workaround? > > Thanks in advance for any answer. > (excuse my poor school-english) > > Heinz > ...

RE: Error handling in Database Procedures
Paul, This information is documented in the SQL Reference Guide, [page 7-4 in the 2.6 version (ftp://ftp.ca.com/CAproducts/ingres/docs/Ingres_26/sqlref.pdf) page may vary in prior versions.] "Default Behavior When an error occurs in a database procedure, the behavior of the DBMS depends on whether the procedure was invoked by a rule or executed directly (using execute procedure). If the procedure was invoked by a rule, an error has the following effects: The procedure is terminated. Those statements in the procedure which have been executed are rolled back. The statement that fired the rule is rolled back. If the procedure was executed directly, an error has the following effects: All statements in the procedure up to the point of the error are rolled back. The procedure continues execution with the statement following the statement that caused the error. Parameters passed by reference are not updated." Also on the same page "Because both iierrornumber and iirowcount reflect the results of the preceding query, beware of inadvertently resetting the value of one when checking the other." It then gives the code example of the wrong way and the right way to check - essentially the same as your examples. There's then a table of statements and their effect on iierrornumber and iirowcount. HTH Paul .... Right enough, jolly good. I had looked up "Database Procedures"...

Web resources about - Error in store procedure - comp.databases.ms-sqlserver

Invasiveness of surgical procedures - Wikipedia, the free encyclopedia
There are three main categories which describe the invasiveness of surgical procedures . These are: non-invasive procedures , minimally invasive ...

Eye tattoos: Tattooist Luna Cobra defends practice following calls to ban procedure
IT SOUNDS like the really gross scene in a horror movie.

Survivors of female genital mutilation outraged by 'compromise' which could legalise procedure
Survivors of female genital mutilation are fighting back against a so-called compromise, which could let a modified form of the procedure continue. ...

Doctors Perform Medical Procedure On Wrong Newborn
The procedure was meant for another child.

Butt procedures, male breast reduction growing slices of plastic surgery
Every 30 min of 2015, a rump was remodeled. Men had 40% of all breast reductions.

"Gynecologists Kavita Shah Arora and Allan Jacobs said procedures that slightly changed the look of a ...
"... were comparable to male circumcision or cosmetic procedures in Western countries like labiaplasty. Laws against mild modifications were ...

Mix-up leads to surgical procedure on wrong baby
Tennessee mom says her newborn was mistakenly given treatment he didn't need

New Chipotle Food Safety Procedures Include Shutting Down Restaurant If Anyone Barfs
... what happened inside, but it’s pretty much exactly what you would expect: repetitive instructional videos about food safety and cleaning procedures, ...

New Video Series Shows The Reality Of Abortion Procedures
New Video Series Shows The Reality Of Abortion Procedures

Yay going in for kidney stone procedure soon!
There is a G-d i have been in the hospital with stone pain and bowel pain the past week. the bowel issue resolved by itself without surgery ...

Resources last updated: 3/22/2016 7:06:12 AM