how will you take only stored procedure backup in oracle 10g?
how will you take only stored procedure backup in oracle 10g? ...
stored-procedures
How to learn stored procedures in SQL Server as a Java programmer?
What is a good way to learn to develop SQL Server(2005) stored procedures? I am primarily a java developer but need to take on SQL Server stored procedure development. I have some basic SQL in my background but nothing major. Any good specific tutorials maybe using the adventure works schema? Or just good reference sites that contain alo...
What is a stored procedure with a padlock icon in SQL 2005?
I see some stored procedures in one database I'm managing that have the regular stored proc icon, but with a little padlock next to them. The differences I see is that I can't "modify" them, and if I try to script them, it says "Text is Encrypted". Is this because these are CLR stored procedures? Are they "regular" procedures, but encry...
CLR SQL Stored Procedures Testing with Unit Test Project
I'm just getting into using VS2008 to write clr stored procedures for SQL 2008. When writing c# code I am used to having a separate 'Test Project' where I would place all my unit testing code, however it appears at first blush that I can't have the same setup with a clr sql project with stored procedures. It 'feels' like this can be do...
Return value from a stored proc on error
Hi there, I have an sp in SQL Server that when errors returns -4 what does -4 mean? Is there a table somewhere explaning what the possible return values are? ...
Stored Procedure that handles 1 or 2 or 3 value ...
i am using a stored procedure to run some queries in my database. The value is taken from a query string then passed to the stored procedure. the thing is, the user may select more than 1 option that generates 3 or more query string. e.g. http://localhost.com/test.aspx?param=76&param2=79 i know how to take the values from the query...
Creating Stored Procedure Syntax, relating to use of GO
Does anyone know why. CREATE PROCEDURE My_Procedure (@Company varchar(50)) AS SELECT PRD_DATE FROM WM_PROPERTY_DATES WITH (NOLOCK) WHERE PRD_COMPANY = @Company GO Gives me an error message in SQL management studio: Msg 102, Level 15, State 1, Procedure My_Procedure, Line 1 Incorrect syntax near 'GO'. ...
How to retrieve scalar value from stored procedure (ADO.NET)
Hello everyone, If in the stored procedure, I just execute one statement, select count(*) from sometable, then from client side (I am using C# ADO.Net SqlCommand to invoke the stored procedure), how could I retrieve the count(*) value? I am using SQL Server 2008. I am confused because count(*) is not used as a return value parameter of...
Strange "Invalid column name" error when executing SP
We have an SP in which we build a query and then execute the query by using the exec(@select) call, where the variable that holds the SQL query as it is constructed is @select. I have been assigned a request to add a new column to the resultset returned by the SP. This new column should not be returned in all circumstances, but only und...
How to check stored procedure return value elegantly
Hello everyone, Here is my current implementation of a stored procedure which returns Order status for a given Order ID. There are two situations, there is matched Order ID and I will retrieve the related status, there is no matched Order ID (i.e. non-existing Order ID). My confusion is, how to implement the two functions elegantl...
Passing datetime to a stored procedure & add a 1 sec to datetime
There are two things here i want to accomplish. List I want to pass a datetime into my stored procedure in the following format ('2007-05-28 00:00:00'), but that doesn't seem to work unless i use {ts '2007-05-28 00:00:00'} The next thing is I want to increase the @SEAL_DATE by a second so it can be used to check the dates between the e...
Using a cursor with dynamic sql in a stored proc
I have dynamic sql statement I've created in a stored proc. I need to iterate over the results using a cursor. I'm having a hard time figuring out the right syntax. Here's what I'm doing SELECT @SQLStatement = 'SELECT userId FROM users' DECLARE @UserId DECLARE users_cursor CURSOR FOR EXECUTE @SQLStatment --Fails here. Doesn''t like th...
How to insert email ID field with @ from a stored procedure in mysql?
I am having a basic mysql stored procedure for inserting user data along with email id. For example CALL INSERTUSER( 'Someone', '[email protected]' ); My problem is that the stored procedure is taking @ as special character.I am new to mysql. How can I insert this? Is there any escape character?Please help. ...
SQL Server stored procedure issue calling another stored procedure
Here's a issue I have with a stored procedure (using SQL Server 2005), inside this stored procedure it calls another stored procedure putting the data into a temp table. INSERT INTO #tmpTable (Column1, Column2, Column3) EXEC psp_rptInsideStoredprocedure 2 This inside stored procedure has a mode parameter that determines which columns...
pl/sql Stored procedure... where does the execution time go?
Hi everybody, I am currently tracing a performance leak in a stored procedure. Having a timestamp put out right after the initial "begin" and one right before the final "end" (I am doing a commit before) says the procedure takes abt. 10 secs to finish. However, I have to wait 2mins+ for it to end. Can anybody tell me where the rest of ...
Stored procedures not called in Entity framework using asp.net MVC
I am trying to use the stored procedure mapping feature in Entity Framework to perform the insert update and delete functions. For whatever reason the procedures are not being called. They are correctly mapped, and supposedly all I have to do is call SaveChanges(); in my controller for them to execute. Using this tutorial as a referenc...
Continue after primary key violation error
The problem is during inserting data from staging table during import routine. The system is a legacy one I inherited, and short term while I develop something more suitable I want to patch things to avoid the data transfer failing. Unfortunately the facility exists via another application to create an entry into table, called CommRece...
The "right" way to do stored procedure parameter validation
I have a stored procedure that does some parameter validation and should fail and stop execution if the parameter is not valid. My first approach for error checking looked like this: create proc spBaz ( @fooInt int = 0, @fooString varchar(10) = null, @barInt int = 0, @barString varchar(10) = null ) as begin if (@fooInt = 0 an...
C# + Sql Server - Execute a stored procedure large number of times. Best way?
Hi all. I have one stored procedure which inserts data into 3 tables, (does UPSERTS), and has some rudamentary logic. (IF-THEN-ELSE) I need to execute this Sproc millions of times (From a C# app) using different parameters and I need it to be FAST. What is the best way to do so? Does anybody know an open-source (or not) off the shelf...
Mysql Cursor / Fetch
Hi, I expect the following Stored Routine to return a serie of rows, while it only returns 1 CREATE PROCEDURE example() BEGIN DECLARE current_id INT; DECLARE done INT DEFAULT 0; DECLARE cur_main CURSOR FOR SELECT id from tasks; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; OPEN ...