Try this one - CREATE PROCEDURE [dbo].[SP_All] @myDate DATETIME , @ServerName SYSNAME AS BEGIN EXEC dbo.sp_1 @myDate, @ServerName EXEC dbo.sp_2 @myDate, @ServerName EXEC dbo.sp_3 @myDate, @ServerName EXEC dbo.sp_4 @myDate, @ServerName END

Assuming that you are updating 9 tables in one action like(button click),maintaining transaction may be your requirement ,so i would like to suggest you to make one storedprocedures and maintain transaction in it,rather than 9 different procedures..

If you're going to use Stored Procedures then look at the SqlCommand class that will allow you to execute stored procedures on the database. The other idea would be to consider what kind of Object-Relational Mapping tool you are using that may allow for SQL commands to be executed directly since you already seem to be using some LINQ in your code. You'd pair the SqlCommand class with a SqlConnection class that have a direct connection to the database, presuming the stored procedure is MS-SQL Server or SQL Express where you want to execute the procedure in the DB. Unless you have a very different configuration than what I've seen, most calls to stored procedures are done by wrapping some using statements for the connection and command to run the procedure. Calling stored procedure fro

Though completely unaware of the technologies used here (unless some minor knowledge of SQL Server), I will attempt an answer (please forgive me if my jython syntax is not correct. I am trying to outline possibilities here not exact code) My first approach (found at this post) would be to try: cur.execute("use CSM") cur.callproc(("CSM","dbo","dbo.DUMMY"), ["carrier1"]) This must have to do with the fact that sa users always have the dbo as a default schema (described at this SO post) If the above does not work I would also try to use the CSM database name in the JDBC url (this is very common when using JDBC for other databases) and then simply call one of the two below. cur.callproc("DUMMY", ["carrier1"]) cur.callproc("dbo.DUMMY", ["carrier1"]) I hope this helps Update: I quote

How can I get list of these encrypted Stored Procedures? SELECT SCHEMA_NAME(p.schema_id) + '.' + p.name FROM sys.procedures p WHERE OBJECTPROPERTY(p.object_id, 'IsEncrypted') = 1 How can I invoke MSSQL encrypted Stored Procedures from Java? Should be no difference between encrypted and non-encrypted stored procedures when you invoke their from Java.

solution: from django.contrib import admin from polls.models import Poll from django.http import HttpResponse from django.shortcuts import redirect from django.core.urlresolvers import clear_url_caches from django.utils.importlib import import_module from django.conf import settings def index(request): admin.site.register(Poll) reload(import_module(settings.ROOT_URLCONF)) clear_url_caches() return redirect('/admin')

postgresql does not have stored procedures, but you can write a function which is essentially the same thing. I prefer to put complex queries into a function. This way you can make fixes and performance improvements on the database side instead of having to push out new PHP code each time. Here's a resource that demonstrates how to create a pg function and call it from PHP: http://pgedit.com/resource/php/pgfuncall

I ran into this too. You have to use the sqlsrv question mark convention. Here is a good link for using named parameters with sqlsrv. calling stored procedure with named parameters

It's the DECLARE keyword. That only applies for anonymous PL/SQL blocks. With stored procedures the IS keyword marks the start of the declaration section. The PL/SQL documentation provides a formal specification of the syntax and plenty of examples. Find out more. You could have solved this problem yourself by displaying the compilation error(s). Modern IDEs like PLSQL Developer, SQL Developer or TOAD will do this automatically. In SQL*Plus you can use the show errors command. If you are using some other client you can run: select * from user_errors where name = 'GETHEADOFSCHOOL'; Notice that Oracle stores its object names in CAPITALS. So CamelCase is all very well in the source code but the absence of underscores makes data dictionary lookups harder.

I've had this conundrum before and it was because I was actually trying to create the stored procedure in the master database. I believe by default, in SSMS, this database is selected by default. If you are using SSMS, ensure that the database selected from the dropdown list (usually top left on the query window toolbar) is the correct database.

First of all, you don't need a procedure for such a trivial task. update db_books set hits=hits+1 where Book_ID = :id; will do the job better than your unreliable procedure

This has turned into a bit of a ramble I am sorry but I am not sure of your level of knowledge so trying to keep this simple! When designing your system you need to think about how you want to structure it. Just like in the real world it is nice to organise things in logical places rather than just pile them up in the middle of the room! In theory you could put everything inside the MAIN method of your application but that would be hard to read and maintain. So, instead we break things out into methods so that you can see what is happening more easily. This then scales up into breaking methods out into classes. If you have lots of methods that all relate to a User then it would make sense to create a class called User and put all of the methods in that class. That way should you, or anot

You can create a custom SimpleMembershipProvider and SimpleRoleProvider that calls the stored procedures your DBA provides. You will need to rip out the initialization process out of the MVC 4 Internet template as discussed in this article, and add your own initialization if required. To plug in your custom providers into your application modify the web.config to something like this: <add name="SimpleMembershipProvider" type="MyCustomMembershipProvider.SimpleMembershipProvider, MyCustomMembershipProvider"/> Where MyCustomMembershipProvider is the name of the assembly that contains your implementation of SimpleMembershipProvider. Do the same for your role provider.

The answer you're looking for is at this SO post... http://stackoverflow.com/a/4561443/1246574 The one thing I would improve upon for the accepted answer in that post, is the example in that answer doesn't use any USING statements. It would be better to have the connection and command within USING statements so they are automatically disposed. Another approach would be to use the Microsoft Enterprise Library for interacting with your SQL Server DB. I think it's easier than using plain old SqlConnection and SqlCommand.

There is no any issue using stored procedure in MVC, you can go for it. There would be worth reading this about your performance question. If controller is not depended on the implementation of data access layer then no matter what you use whether EF or Stored procedure, you are good to go. Darin Dimitrov already explain it about here.

It's pretty simple, anything related to the database that can be run at the DB end, should be run there. There are some special cases were sometimes you need some extra logic but for the most part if its a common piece of DB logic and is executed frequently then it would be more efficient as a stored procedure.

In terms of performance, I don't have any specific numbers. Please elaborate on specific concerns you might have. In general, with JPA you have far less control when it comes to performance tuning than you would if implementing a custom solution. However, JPA provides a solid, proven infrastructure with a boat load of functionality that you don't have to write yourself! JPA will definitely help you to more quickly get your application off the ground. In terms of learning curve. If I assume you are starting fresh... there is a great deal to learn with either approach. Both require a working knowledge of SQL and entity relationship models. The JPA approach requires you learn JPA! Go figure! A MySQL approach requires knowledge of JDBC. Your question, 'do stored procedure run faster than J

ExecuteScalar is meant to return only the first column of the first row from the result.. If your SProc would return more than one value, you could use the ExecureReader() method. SqlConnection sqlConnection1 = new SqlConnection("Your Connection String"); SqlCommand cmd = new SqlCommand(); SqlDataReader reader; cmd.CommandText = "StoredProcedureName"; cmd.CommandType = CommandType.StoredProcedure; cmd.Connection = sqlConnection1; sqlConnection1.Open(); reader = cmd.ExecuteReader(); // Data is accessible through the DataReader object here. sqlConnection1.Close(); You should also read on Random class. You should use the same instance to generate numbers.

The EXECUTE privilege is required to call stored procedures. Documentation here: http://dev.mysql.com/doc/refman/5.5/en/stored-routines-privileges.html

Stored procedures aren't stored as files, they're stored as metadata and exposed to us peons (thanks Michael for the reminder about sysschobjs) in the catalog views sys.objects, sys.procedures, sys.sql_modules, etc. For an individual stored procedure, you can query the definition directly using these views (most importantly sys.sql_modules.definition) or using the OBJECT_DEFINITION() function as Nicholas pointed out (though his description of syscomments is not entirely accurate). To extract all stored procedures to a single file, one option would be to open Object Explorer, expand your server > databases > your database > programmability and highlight the stored procedures node. Then hit F7 (View > Object Explorer Details). On the right-hand side, select all of the procedures yo

This is some code that I have in one of my applications. See if it helps: //Query SQL $tsql = "Exec spinsert @tabla=?,@columnas=?,@valores=?"; $params = array($tabla,$columnas,$valores); //Execute the stored query $stmt = sqlsrv_query($conn, $tsql, $params); if ($stmt === false) { echo "<h3>Error in query preparation or execution.</h3>"; ListErrors(); die; } else { echo "Insert Successful"; } // this should help for the non-insert/update case $arr = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC); var_dump($arr);

You should look up the SQL MERGE statement. It allows you to perform UPSERT's - i.e. INSERT if a key value does not already exist, UPDATE if the key does exist. http://technet.microsoft.com/en-us/library/bb510625.aspx However, your requirement to check for a key value in 2 places before you perform an update does make it more complex. I haven't tried this but I would think a VIEW or a CTE could be used to establish if the ID exists in both your tables & then base the MERGE on the CTE/VIEW. But definitely start by looking at MERGE!

You can absolutely do this within the stored procedure without changing your application code, but bear in mind that you're introducing locking issues and the possibility of timeouts. Use GET_LOCK() and RELEASE_LOCK() to take care of the synchronization. Run GET_LOCK to perform the synchronization at the start of your stored procedure, and RELEASE_LOCK once you're done: IF (GET_LOCK('lock_name_for_this_SP', 60)) THEN .... body of SP RELEASE_LOCK('lock_name_for_this_SP'); ELSE .... lock timed out END IF You'll also need to take care that your application timeouts are longer than the lock timeout so you don't incur other problems.

"DELIMITER ;;" is not valid use: DELIMITER New_delimiter Old_Delimiter_To_Finish_Line Both delimiters must be differents In the code of the procedure use the old delimiter but finish it with the new code; Restore the old delimiter DELIMITER $; CREATE PROCEDURE `CANCEL_ORDER`(IN order_id INT, IN buyer_user_id INT) BEGIN SET autocommit=0; START TRANSACTION; SELECT customer_user_id INTO @userid FROM orders WHERE id=order_id; IF @userid=buyer_user_id THEN UPDATE orders SET status='failed',canceled_at=UNIX_TIMESTAMP(CURRENT_TIMESTAMP()) WHERE id=order_id; COMMIT; ELSE SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Error: Customer ID in orders does not match with given buyer_user_id', MYSQL_ERRNO = 1003; ROLLBACK; END IF; END;$ D

I was thinking about offsetting business logic to stored procedures in specific databases. Therefore if the logic is generic to all external databases then hold it in the application (.NET). If the logic is specific to a database then create a stored procedure. This statement causes great concern in my mind. When you say if the logic is specific to a database, to which I presume you mean one of the twelve aforementioned, this is a design flaw. Databases are just that, information stores, they should not require any "special" logic to access them outside of whatever view structure is exposed. Further, if the data needs to be manipulated in a non-set manner than you need to put it inside your application. That is don't make your perform calculations when they can be offset to

The only fix I could find was to close the EDMX, and manually edit the XML, which, even after removing the stored proc from the database, and updating the model from the database - still had mention of the proc. Removing the lines from the XML has solved the issue.

Try with spiting USe DB and create procedure. Like this DECLARE @create_store_procedure nvarchar(max) SET @create_store_procedure = N' USE [' + @DATABASE_NAME + '] ' EXEC sp_executesql @statement = @create_store_procedure SET @create_store_procedure = N' CREATE PROCEDURE [dbo].[sproc_imp_' + @TableName + '] AS BEGIN PRINT(''doing something'') END ' EXEC sp_executesql @statement = @create_store_procedure This is working perfectly for me

That's a typo. It should read "Type-safe support of scalar database functions". At the moment you have to use plain SQL to call stored procedures. Also see https://groups.google.com/forum/#!searchin/scalaquery/procedure/scalaquery/BUB2-ryR0bY/EFZGX663tRYJ

There are two approach in using of DbContext. First is database first which I think that you can use store Procedures, second is Code First, that Code First currently only supports mapping to tables. This unfortunately means that you can’t map Code First directly to stored procedures, views, or other database objects. If you are letting Code First generate a database, there is no way to create these artifacts in the database, other than manually adding them once Code First has created the database. If you are mapping to an existing database, there are some techniques you can use to get data from non-table database artifacts

IF types are OK, then that shoud work id not, change then into i.e. (name CHAR(30),path CHAR(255)) create procedure check_status(name INT(30),path INT(255)) BEGIN select Count(*) as result from `document_details` where `document_name`=name and `document_path`=path; END; In procedures there is no IN/OUT in funcions there are...

What you describe seems to be a tunnel for SQL server over web api. I wouldn't encourage it. Instead of looking at the two systems as SQL Server database v/s NoSQL database, I would look at the services to abstract them into domain entities. If the two systems need to exchange data, it would be in the form of these entities. The reasons I feel this way: Keep the systems isolated, schema changes need not affect downstream systems. Allows the flexibility to encapsulate the internals of one system so that designing the consumer can be contract driven. Edit: I discourage this because This tends to have a tangled setup, Site A now talks raw data that it does not own or control. You could easily be in a position where the SQL server needs changes demanded by B and this makes A very brit

You really don't want your business logic to live in your SPs. The most obvious reason is that your rules will not be easily testable. Your best bet, IMHO is to follow the repository pattern and interact with your database via SPs and move your rules to another set of classes. You can then mock your data and have a nice suite of unit tests to ensure everything stays running smoothly after feature-adds and refactoring. If all of your DB access is handled via SPs, using straight ADO instead of EF might also be preferable. You won't have to carry the extra weight that comes with EF and you should see some increased performance.

ok, this may be a little oversimplified (and possibly incomplete): With a stored procedure: you do not need to transmit the query to the database the DBMS does not need to validate the query every time (validate in a sense of syntax, etc) the DBMS does not need to optimize the query every time (remember, SQL is declarative, therefore, the DBMS has to generate an optimized query execution plan)

It sounds like it would make sense to create a shared DAL that both applications can share. I would add unit tests (or really integration tests) to make sure the DAL is compatible with the apps after changes. That way your tests would fail if incompatible changes have been made

It is doable. Create tests and in the setup create a new instance of db and give it some data and then execute the procs. Validate your assumptions, like I got the correct data back. Drop the test db then do it all again in the next test.

Sure, here's a basic introduction to ouptput parameters. Here's the short and sweet of it. Note the OUTPUT keyword on the output parameter: CREATE PROCEDURE dbo.uspMyProc @SomeParameter varchar(10), @TheOutputParameter varchar OUTPUT AS -- Do stuff SELECT @TheOutputParameter = somecolumn FROM something -- or SET @TheOutputParameter = someresult RETURN

If you are working with a grid, you will want to work with a slightly different return type I'm guessing as you are getting multiple records. Your SqlDataProvider is fine. But in the controller you want the return to be List<GradesInfo>. Then use CBO.FillCollection<GradesInfo>(DataProvider.Instance().GetEmri("yourvaluehere")); Then, in the ascx you can do the following, assuming that you are using a datagrid with an id of dgrData. var controller = new GradesController(); dgrData.DataSource = controller.GetEmri("yourvaluehere"); dgrData.DataBind();

Our suggestion would be to use the SQL Server Data Tools (SSDT) and create a database schema change management project in Visual Studio that you can then check-in to Team Foundation Server. It has quite a few benefits like being able to "compile" the schema and has tools that can be used in generating automatic change scripts for target servers (whether they are empty, test, or even production servers). http://msdn.microsoft.com/en-us/data/tools.aspx

Try this query: SELECT * FROM INFORMATION_SCHEMA.ROUTINES This will return all stored procedure in the current database and also the schema associated with it. If you want to get the tables and related stored procedure try something like this: SELECT t.TABLE_NAME, s.ROUTINE_NAME,s.SPECIFIC_SCHEMA FROM INFORMATION_SCHEMA.TABLES t INNER JOIN INFORMATION_SCHEMA.ROUTINES s ON s.ROUTINE_NAME IN ( SELECT referencing_entity_name FROM sys.dm_sql_referencing_entities(TABLE_SCHEMA + '.' + TABLE_NAME, 'OBJECT') ) AND s.ROUTINE_TYPE = 'PROCEDURE' WHERE t.TABLE_TYPE = 'BASE TABLE' Stay away from the sysobject etc views

At the beginning of your procedure (spData4) create three temp tables that corespond to the output columns of three stored procedures. Run all 3 sps using INSERT..EXEC and insert data into 3 temp tables. At the end write a query that JOIN the result of 3 temp tables and return it as SELECT from procedure Something like this (fix for your correct column types): CREATE PROCEDURE spData4 (@Year smallint) AS BEGIN CREATE TABLE #temp1 (RegionName NVARCHAR(50), TotalSitesVisited INT, TotalViolations INT) CREATE TABLE #temp2 (RegionName NVARCHAR(50), TotalSiteVisits INT) CREATE TABLE #temp3 (RegionName NVARCHAR(50), TotalBadSites INT) INSERT INTO #temp1 EXEC spData1 @Year INSERT INTO #temp2 EXEC spData2 @Year INSERT INTO #temp3 EXEC spData3 @Year SELECT COALESCE(t1.RegionName, t2