|How to call Stored Procedures (with 2 parameters) in a Stored Procedure?|
Try this one -
CREATE PROCEDURE [dbo].[SP_All]
, @ServerName SYSNAME
EXEC dbo.sp_1 @myDate, @ServerName
EXEC dbo.sp_2 @myDate, @ServerName
EXEC dbo.sp_3 @myDate, @ServerName
EXEC dbo.sp_4 @myDate, @ServerName
|Call stored procedures from another stored procedure or only one SP for better efficiency?|
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..
|WCF and MVC : Call stored procedures from web mvc application|
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
|What is the correct usage of zxjdbc to call stored procedures?|
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
My first approach (found at this post) would be to try:
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.
I hope this helps
Update: I quote
|How can I call MSSQL encrypted Stored Procedures from Java?|
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.
|django call admin.site.register from view|
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
|stored procedures vs PDO php pl/pgsql|
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
|Retrieve Stored Procedures|
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
|My Stored Procedures Does Not Work|
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
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.
|Some stored procedures are not displayed|
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
|Create Stored Procedures with PDO in PHP|
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
|DLL files & Stored Procedures in ASP.NET C#|
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
|SimpleMembership with stored procedures|
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:
Where MyCustomMembershipProvider is the name of the assembly that contains
your implementation of SimpleMembershipProvider. Do the same for your role
|How to use SQL-Server Stored Procedures?|
The answer you're looking for is at this SO post...
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.
|is it the right way to do the project with asp.net mvc 4 and stored procedures?|
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
Darin Dimitrov already explain it about here.
|Stored procedures and classes|
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.
|MySQL Stored Procedures vs JPA Queries|
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
|Getting stored procedures multiple result|
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
SqlConnection sqlConnection1 = new SqlConnection("Your Connection String");
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "StoredProcedureName";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = sqlConnection1;
reader = cmd.ExecuteReader();
// Data is accessible through the DataReader object here.
You should also read on Random class. You should use the same instance to
|How to restrict a user to stored procedures only?|
The EXECUTE privilege is required to call stored procedures.
|How to script out stored procedures to files?|
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
|PHP - MSSQL getting results from Stored Procedures|
This is some code that I have in one of my applications. See if it helps:
$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>";
echo "Insert Successful";
// this should help for the non-insert/update case
$arr = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC);
|Stored Procedures - Updating and Inserting|
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.
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!
|Synchronizing stored procedures in mysql|
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
.... lock timed out
You'll also need to take care that your application timeouts are longer
than the lock timeout so you don't incur other problems.
|syntax error in stored procedures|
"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
Restore the old delimiter
CREATE PROCEDURE `CANCEL_ORDER`(IN order_id INT, IN buyer_user_id INT)
SELECT customer_user_id INTO @userid FROM orders WHERE id=order_id;
IF @userid=buyer_user_id THEN
UPDATE orders SET
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Error: Customer ID in orders does not match
with given buyer_user_id', MYSQL_ERRNO = 1003;
|SOLID stored procedures and functions|
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
|Stored Procedures and updating EDMX|
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.
|SQL dynamically create stored procedures?|
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 + ']
EXEC sp_executesql @statement = @create_store_procedure
This is working perfectly for me
|Calling stored procedures with Slick|
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
|DBContext vs ObjectContext - using Stored Procedures|
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
|Parameters in stored procedures in mysql|
IF types are OK, then that shoud work id not, change then into i.e. (name
create procedure check_status(name INT(30),path INT(255))
select Count(*) as result from `document_details`
where `document_name`=name and `document_path`=path;
In procedures there is no IN/OUT in funcions there are...
|Expose SQL Server stored procedures on a web API|
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
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
Allows the flexibility to encapsulate the internals of one system so that
designing the consumer can be contract driven.
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
|Using MVC & Entity Framework with Stored Procedures|
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.
|What are the benefits of creating Stored Procedures in SQL and MySQL?|
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
|Sharing stored procedures across multiple apps|
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
|Unit tests for Stored Procedures in SQL Server|
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.
|Stored procedures in SQL Server Errors Unsolved|
Sure, here's a basic introduction to ouptput parameters.
Here's the short and sweet of it. Note the OUTPUT keyword on the output
CREATE PROCEDURE dbo.uspMyProc
@TheOutputParameter varchar OUTPUT
-- Do stuff
SELECT @TheOutputParameter = somecolumn FROM something
SET @TheOutputParameter = someresult
|How to populate gridviews from code behind using Stored Procedures|
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
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");
|Moving Stored Procedures from SSMS into TFS 2012|
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).
|List of tables used in all stored procedures with SP's schema name|
Try this query:
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
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
FROM sys.dm_sql_referencing_entities(TABLE_SCHEMA + '.' +
AND s.ROUTINE_TYPE = 'PROCEDURE'
WHERE t.TABLE_TYPE = 'BASE TABLE'
Stay away from the sysobject etc views
|Combine results from multiple stored procedures|
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)
CREATE TABLE #temp1 (RegionName NVARCHAR(50), TotalSitesVisited 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