Spawning Multiple Threads In Stored Procedure Or Application
Jul 16, 2001
Hi Guys,
I am searching for some information on achieving performance improvement by spawning multiple threads in single Stored procedure or rather say within Single Database connection. We have a batch process that updates around 200 tables and each table update takes around 2 mnts. I am trying to optimize this by running these updates in parallel rather than sequential. These all tables are mutually exclusive. I have written a stored procedure which updates these tables in loop. Concern is that every update statement waits for other to get over. I am calling this Sp from Java application. One crude way will be opening multiple connections to database each running separate T-SQL statement. It comes with lot of overhead in opening connections .Is there any way I can force explicitly in T-SQL stored procedure to spawn a new thread for every Update statement. In case I try to do same process from a Java connection.. is there a way I can open multiple threads for each statement under same database connection.
Thanks,
Vikalp
View 1 Replies
ADVERTISEMENT
Aug 22, 2007
hi,
do i need to use specially synchronized code if i have multiple threads inserting, updating and reading rows to and from the same database?? in this case, i know that no 2 threads will try to insert or update the exact same row into the DB, however, multiple threads might try to read the same row from the database.
thanks!
View 3 Replies
View Related
Aug 9, 2006
Hi, I'm trying to stress test my web application, but when I get high load, the queries that used to take 10-20 ms starts taking 500 - 2000+ ms. Or to put it another way, when i run them single threaded i can do about 43000 a minute, when they are run in paralell it drops to about 2500 a minute.
What can i do about this ?
There are severeal queries thats affected, but here is one example:
update [user] with (ROWLOCK XLOCK) set timestamp = getdate() where userid = 1''
btw: im running sql server 2005 sp 1. The stress test is run on 3 machines total (web, sql and client) the client is simulation 400 users, cliking a page as soon as the last one is loaded, ie there will always be 400 page requests.
View 9 Replies
View Related
Dec 21, 2004
Our company is in the retail business, thus, the window for processing cubes is very small during Christmas season (only 4 hours each day).
To speed things up, we have partitioned our cube at monthly level so, potentially, 12 threads can be run simultantsly. However, when I looked at DTS, I am not so sure whether or how it can accomplish that task. Has anyone tried this before or is aware of another third party tool can do the trick?
thx in adcance,
Carl.
View 2 Replies
View Related
Mar 3, 2008
Hi All
I have been asked by developers if there is any advantage in processing multiple clustering models simultaneously by using AMO and multiple threads as against processing one after another.
I have limited experience with Analysis Services but based on my reading I don't see this method providing any advantage.
Does anyone have any recommendations or advice? The system Enterprise Edition running on an x86 Server with 2 dual core processors and 4GB of RAM. Would the answer alter if the server running x64 version of SQL Server and Windows.
Thanks
Nadreck
View 3 Replies
View Related
Jan 24, 2006
I found a peculiar thing today while working with SQL Mobile in a multithreaded application (VS2005, application for Pocket PC 2003).
I created a class which has one SqlCeConnection object. Every time I call a function to insert/select/delete something from the local db, I open the connection, execute the query an then close the connection again.
But when I'm calling a function from the db class in thread 1 and in the meantime call a different function (from the same db class of course) in thread 2, things go wrong. Because when function 1 wants to close the connection, function 2 is still using the connection and it will crash my application with a native exception (0xC0000005: access violation).
I can see why the error is happening, but shouldn't there be a nice .NET handled exception instead of a native exception which grinds my app to a hold?
(A workaround I use now is to use multiple connection objects instead of one, but I thought I'd give this feedback anyway)
View 5 Replies
View Related
Jul 24, 2012
we are queirying an stored procedure multiple times same time,from our application. In this case, few processes executing successfully and few getting failed with error "50000 error executing the stored procedure" and if we run thesame process again its getting executed sucessfully.Does the MySQL cannot handle multiple threads same time?
View 9 Replies
View Related
Aug 14, 2007
Hi,
I am using distributed transactions where in I start a TransactionScope in BLL and receive data from service broker queue in DAL, perform various actions in BLL and DAL and if everything is ok call TransactionScope.Commit().
I have a problem where in if i run multiple instances of the same app ( each app creates one thread ), the threads pop out the same message and I get a deadlock upon commit.
My dequeue SP is as follows:
CREATE PROC [dbo].[queue_dequeue]
@entryId int OUTPUT
AS
BEGIN
DECLARE @conversationHandle UNIQUEIDENTIFIER;
DECLARE @messageTypeName SYSNAME;
DECLARE @conversationGroupId UNIQUEIDENTIFIER;
GET CONVERSATION GROUP @conversationGroupId FROM ProcessingQueue;
if (@conversationGroupId is not null)
BEGIN
RECEIVE TOP(1) @entryId = CONVERT(INT, [message_body]), @conversationHandle = [conversation_handle], @messageTypeName = [message_type_name] FROM ProcessingQueue WHERE conversation_group_id=@conversationGroupId
END
if @messageTypeName in
(
'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog',
'http://schemas.microsoft.com/SQL/ServiceBroker/Error'
)
begin
end conversation @conversationHandle;
end
END
Can anyone explain to me why the threads are able to pop the same message ? I thought service broker made sure this cannot happen?
View 11 Replies
View Related
Jul 20, 2005
I have an ODBC/C program and I want to call the stored proceduressp_addlogin and sp_adduser in it. Can someone please provide me withsome sample code showing the best way to do this.
View 2 Replies
View Related
Apr 26, 2008
Hi,I am developing application in asp.net 2.0 using C#. My back end is sql server 2005. I also use microsoft enterprise library 2006.My problem is:First i pass three parameters to stored procedure from the application. After i increase one more parameter in both application as well as stored procedure, the application gives an error that parameter does not match with the stored procedures parameters.after couple of hours when i restart the machine and again run the application it works fine with four parameters.does sql server 2005 stores the parameters in cache??
View 3 Replies
View Related
Mar 30, 2004
Hey all,
I've got a question and after doing some research I've found only a vague reference but no clear answer.
I have a java app that will be passing parameters to my stored procedure. I'll grab the requested info from the tables but instead of sending it back to the java app that sent the request, I need to send it to a "different" java app (the second java app will not be running at the time).
Can someone point me to a good source for executing java applications from a stored procedure?
Thanks in advance ...
tam
View 1 Replies
View Related
Jul 20, 2005
Hi,In SQL Books Online in the section on @@Error it gives the followingexample:-- Execute the INSERT statement.INSERT INTO authors(au_id, au_lname, au_fname, phone, address,city, state, zip, contract) values(@au_id,@au_lname,@au_fname,@phone,@address,@city,@state,@zip,@contract)-- Test the error value.IF @@ERROR <> 0BEGIN-- Return 99 to the calling program to indicate failure.PRINT "An error occurred loading the new author information"RETURN(99)ENDELSEBEGIN-- Return 0 to the calling program to indicate success.PRINT "The new author information has been loaded"RETURN(0)ENDGOHow do I access the value returned by the RETURN statement (i.e. 99 or0) in my asp application that called the stored proc.Sometimes rather than just return an integer signifying success orfailure I've seen examples where the id of the newly added item isreturned on success and perhaps -1 if the operation fails. Theseexamples make use of ouput parameters to achieve this. If theoperation succeeds then then the output parameters value is set to thenew id and this is accessed from the calling application.E.g.IF @@ERROR <> 0BEGIN-- Return -1 to the calling program to indicate failure.PRINT "An error occurred loading the new author information"SELECT @MyOuptputParameter = -1ENDELSEBEGIN-- Return id to the calling program to indicate success.PRINT "The new author information has been loaded"SELECT @MyOuptputParameter = @@IDENTITYENDWhy go to this trouble if you can use the RETURN statement?
View 4 Replies
View Related
Nov 11, 2006
Hi,
I Have created a stored procedure to use full text search and return the results back,
When i run this procedure in the query analyzer it is working fine, but when i run this procedure from asp.net application, it is returning zero rows.
I have checked all the parameters and everything in my web application, there is nothing wrong in there.
Another stored procedure which almost do the samething with some different parameters is working fine on both ends.
I am using Sql server 2005 (express) + VS.NET 2005. and using ASPNET to connect to database.
Thanks in advance for any suggestions.
View 7 Replies
View Related
Mar 1, 2007
Hello
I am building a survey application.
I have 8 questions.
Textbox - Call reference
Dropdownmenu - choose Support method
Radio button lists - Customer satisfaction questions 1-5
Multiline textbox - other comments.
I want to insert textbox, dropdown menu into a db table, then insert each question score into a score column with each question having an ID.
I envisage to do this I will need an insert query for the textbox and dropdownlist and then an insert for each question based on ID and score.
Please help me!
Thanks
Andrew
View 9 Replies
View Related
Feb 17, 2008
Hi,
I have a simple web application which calls a stored procedure. The stored procedure operates as a transaction and runs for several minutes. I've created a partial class to set the SQLcommand timeout property to avoid any timeouts, which works fine. Unfortunately though, when the application is run in the production environment, it ends in an error after a certain amount of time (maybe a couple of minutes - not exactly sure), which seems to be the same each run. It doesn't appear to end the stored procedure though, which results in locking the tables. It runs fine in the development environment, and it doesn't appear as though any error information is provided when the application crashes.
I'm assuming that the ASP.NET application is timing out for some reason, but the stored procedure itself is fine. I can run it directly from SQL server without any dramas. In the Virtual Directory configuration within IIS, I have the script timeout period set to 1200 seconds. The Default Web-Site timeout property is set to 120 seconds, but I'm assuming that this is only for internet connection timeout, not database transaction timeouts.
Any information as to what may be causing this is appreciated.
Thanks
View 3 Replies
View Related
Jan 17, 2005
I have a stored procedure that works when executed in query analyzer. (It is also way too long to post here) When called from my application ado.net returns the error:
Invalid object name #idTable
If I run the proc in query analyzer using the same parameters (copied from quickwatch while debugging) there is no error.
While very complicated, this procedure runs quickly so timing out is not an issue.
Does anyone know why a proc would run in query analyzer and not in an asp.net/c# application?
Thank you.
View 4 Replies
View Related
Jun 18, 2014
I have two tables called ECASE and PROJECT
In the ECASE table there is trigger to get the max value of case_id column in ecase based on project and increment one to that case_id value and insert into ecase table .
When we insert a new record to the ECASE table this trigger calls and insert the case_id column value.
When i run with multiple threads , the transaction is rolled back because of trigger . The reason is , on the project table the lock is happening while getting the max value of case_id column based on project.
I need to prevent the deadlock .
View 3 Replies
View Related
Jul 20, 2005
in my java application I've made a call to this stored procedureCREATE procedure pruebaICM@pANI varchar(20),@pTABLA varchar(20),@pInsert varchar(500),@pUpdate varchar(1000),@pFLAG varchar(1),@pResultado int OUTPUTasbeginDECLARE @ani varchar(20)declare @cliente intDECLARE @sentencia nvarchar(1000)DECLARE @tabla nvarchar(20)DECLARE @sentencia_where nvarchar(50)DECLARE @sql nvarchar(1050)SET NOCOUNT ONset @tabla = @pTABLAset @ani = @pANISELECT @sql= N'select @cliente=count(ani) from '+ @tabla + N' whereani = ' + @aniexec sp_executesql @sql, N'@Cliente INT OUTPUT', @Cliente OUTPUTSELECT @Clienteif (@pFLAG = 'A') or (@pFLAG = 'Actualizar') or (@pFLAG = 'I')beginif (@cliente = 0)beginset @sentencia = N'insert into ' +@pTABLA + N' values (' + @pInsert + N')'EXEC sp_executesql @sentenciaset @pResultado = 1SELECT @pResultadoreturn @pResultadoendif (@cliente = 1)beginset @sentencia = N'update ' + @pTABLA +N' set ' + @pUpdateset @sentencia_where = N' where ANI =' + @pANIset @sql = @sentencia +@sentencia_whereEXEC sp_executesql @sqlset @pResultado = 2SELECT @pResultadoreturn @pResultadoendendelse if (@pFLAG = 'B') or (@pFLAG = 'Borrar')beginif (@cliente = 0)beginset @pResultado = 0SELECT @pResultadoreturn @pResultadoendelse if (@cliente = 1)beginset @sentencia = N'delete from '+@pTABLA + N' where ANI = ' + @pANIEXEC sp_executesql @sentenciaset @pResultado = 3SELECT @pResultadoreturn @pResultadoendendEXEC sp_cursorcloseendMy problem is that the ouutput param @pResultado haven't got any valueand don't return anything to the java application. How can I fix thisproblem?Thanka very much for helping me!!!!
View 2 Replies
View Related
Mar 17, 2008
Hello, i have a problem regarding stored procedures and view server state.
I have an application with a lot of stored procedures, one of them checks data of the connected users.
In SQL 2000 i had no problem getting this information, but in SQL server 2005 i do.
my stored procedure looks like this:
ALTER PROCEDURE [dba].[applsp_GetConnectionInfo]
(
@DBName varchar(100)
)
WITH EXECUTE AS OWNER AS
BEGIN
SET NOCOUNT ON
DECLARE @sCollationMaster VARCHAR(128);
DECLARE @sSqlString VARCHAR(900);
-- Determine collation from master database because collation from master and ultimo database may differ
SELECT @sCollationMaster = CAST(databasepropertyex('master', 'Collation') AS VARCHAR);
SET @sSqlString =
'SELECT max(status) AS Status, max(isnull(SCISUSENAME, ''ULTIMOLOGIN'')) AS Login
, MAX(Rtrim(Rtrim(convert(varchar(255), nt_domain)) + nt_username)) AS NTUser
, max(Rtrim(hostname)) AS Host, MAX(Rtrim(program_name)) AS Program
FROM master.dbo.sysprocesses JOIN dba.SCONNECTIONINFO on SCISPID = CAST(spid AS VARCHAR)
AND ( SCISUSENAME = ISNULL(loginame, '''') COLLATE ' + @sCollationMaster + ' OR ISNULL(loginame, '''') = ''ULTIMOLOGIN'')
WHERE ...... AND DB_NAME(dbid) = ''' + @DBName + '''
GROUP BY hostprocess
ORDER BY Login
';
EXEC(@sSqlString);
END
I've granted view server state permissions to my user 'dba' which is the db_owner.
When i execute the query in the stored procedure seperatly as dba i get all the info i need, but when i execute the stored procedure i don't see anything.
I seem to have the same problem with sp_who2
Executing it gives me information about everyone but when i put in a stored procedure like this:
alter procedure test
with execute as owner as
begin
EXEC sp_who2
end
I just see information about myself
View 5 Replies
View Related
Mar 7, 2008
Hi,
I am getting deadlock on activated procedure which I am using to receive message from the Service Broker Queue.
Deadlock details:
Two threads are tring to do delete on internal table queue_messages_122847900 ends up in a dead lock.
Activated procedure code
RECEIVE TOP(1) @xmlMessage = message_body,
@handle = conversation_handle,
@message_type = message_type_name
FROM TransactionQueue;
IF (@message_type = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog')
BEGIN
END CONVERSATION @handle;
RETURN 0
END
.........................
.........................
After this I do process the message and some other processing
And then
END CONVERSATION @handle;
Note I do have single conversation group
Is their a problem in the way I am receiving and processing messages. Is it possible because of the delay between RECEIVE and END CONVERSATION same message is read by two different threads.
Thanks
View 1 Replies
View Related
Jul 3, 2007
Ok, so i have this program, and at the moment, it generates an sql statement based on an array of db fields, and an array of values...
my question is this, is there any way to create a stored procedure that has multiple dynamic colums, where the amount of colums could change based on how many are in the array, and therefore passed by parameters...
if this is possible, is it then better the pass both columns and values as parameters, (some have over 50 columns)...or just create a seperate stored procedure for each scenario?? i have no worked out how many this could be, but there is 6 different arrays of colums, 3 possible methods (update, insert and select), and 2 options for each of those 24...so possibly upto 48 stored procs...
this post has just realised how deep in im getting. i might just leave it as it is, and have it done in my application...
but my original question stands, is there any way to add a dynamic colums to a stored proc, but there could be a different number of colums to update or insert into, depending on an array??
Cheers,
Justin
View 2 Replies
View Related
Sep 14, 2006
Ok, this is kind of an odd problem. Back in June we were having problems with our call manager software, and they decided to have it just start usinga new database. Now I'm trying to generate some reports which need to cover both the old call stats and the new, so that means the stored procedure builds a temp table and populates it from both databases.This works perfectly fine in Management Studio, and when being called from Excel.However when I try to call it from an ASP.NET web app using SqlCommand.ExecuteReader(), I only get results from the new database!What on earth could cause that?
View 2 Replies
View Related
Feb 5, 2008
I have put together the stored-procedure below to carry out update, delete and insert queries in one visit. The code has been pieced together from the pages listed at the bottom. I pass the procedure three XML strings and after testing it a few times it seems to work fine. I’m fairly new to stored procedures though, so I was hoping someone would answer these questions:
1. Is this an acceptable way to do this? Can you foresee any problems?2. I want to make this an ‘all-or-nothing’ event, i.e. if any part of the procedure fails, it must all fail. How would I achieve that?3. I want to know in my calling code what the result is. I’ve used output parameters before, but I’m unsure how to combine one with 2 above.
Sorry this is a long script, but I’ve removed most of the column names and values to shorten it. Thanks in advance.CREATE PROCEDURE amendPageRecords
@PagesToUpdate xml,
@PagesToDelete xml,
@PagesToInsert xml
AS
BEGIN
-- UPDATING RECORDS ------------------------------------------------------------
DECLARE @UpdateTable TABLE (PageID int, PageType varchar(10))
INSERT INTO @UpdateTable (PageID, PageType)
SELECT PageID = UpdatePages.Item.value('@PageID', 'int'),
PageType = UpdatePages.Item.value('@PageType', 'varchar(10)')
FROM @PagesToUpdate.nodes('Pages/Page') AS UpdatePages(Item)
UPDATE page
SET page.page_type = UP.PageType
FROM page INNER JOIN @UpdateTable UP ON page.page_id = UP.PageID
-- DELETING RECORDS ------------------------------------------------------------
DECLARE @DeleteTable TABLE (PageID int)
INSERT INTO @DeleteTable (PageID)
SELECT PageID = DeletePages.Item.value('@PageID', 'int')
FROM @PagesToDelete.nodes('Pages/Page') AS DeletePages(Item)
DELETE FROM page
FROM page INNER JOIN @DeleteTable DP ON page.page_id = DP.PageID
-- INSERTING RECORDS -----------------------------------------------------------
DECLARE @InsertTable TABLE (SiteID int, PageType varchar(10))
INSERT INTO @InsertTable (SiteID, PageType)
SELECT SiteID = InsertPages.Item.value('@SiteID', 'int'),
PageType = InsertPages.Item.value('@PageType', 'varchar(10)')
FROM @PagesToInsert.nodes('Pages/Page') AS InsertPages(Item)
INSERT INTO page
SELECT SiteID, PageType
FROM @InsertTable
END
GOCode taken from:http://weblogs.asp.net/jgalloway/archive/2007/02/16/passing-lists-to-sql-server-2005-with-xml-parameters.aspxhttp://www.eggheadcafe.com/articles/20030627c.asphttp://www.sommarskog.se/arrays-in-sql-2005.html
View 10 Replies
View Related
Feb 5, 2008
I need help in figuring out the proper way of writing a stored procedure out correctly to get my desired datasource. In my ocnIdToRatePlanOptions table, I will recieve a parameter via request.querystring @ocnId to filter out my result set for ocnIdToRatePlan table. Based on the ocnId filtered I want it to select the corresponding tables too.So, if a querystring is passed that is 3955 in my ocnIdToRatePlanOptions table, I want it to use it to create a select for RatePlan1. If a querystring is passed that is 1854 in my ocnIdToRatePlanOptions table, I want it to use to create a select for RatePlan2. Is this possible? ocnIdToRatePlanOptions Table [otrpoRefId] [int] IDENTITY(1,1) NOT NULL,[FKocnId] [nvarchar](4) NOT NULL,[FKrpoRefId] [int] NOT NULL,1, 3955, 12, 1854, 2RatePlan1 Table[rp1RefId] [int] IDENTITY(1,1) NOT NULL,[FKocnId] [nvarchar](4) NOT NULL[fee] [decimal](18, 2) NOT NULL1, 3955, 1.002, 2350, 2.00RatePla2 Table[rp2RefId] [int] IDENTITY(1,1) NOT NULL,[FKocnId] [nvarchar](4) NOT NULL,[q_0_50] [numeric](18, 2) NOT NULL CONSTRAINT [DF_ratePlan2_q_0_50] DEFAULT ((225)),[q_51_100] [numeric](18, 2) NOT NULL CONSTRAINT [DF_ratePlan2_q_51_100] DEFAULT ((325)),[q_101_150] [numeric](18, 2) NOT NULL CONSTRAINT [DF_ratePlan2_q_101_150] DEFAULT ((345)),[q_151_200] [numeric](18, 2) NOT NULL CONSTRAINT [DF_ratePlan2_q_151_200] DEFAULT ((400)),[q_201_250] [numeric](18, 2) NOT NULL CONSTRAINT [DF_ratePlan2_q_201_250] DEFAULT ((450)),[q_251_300] [numeric](18, 2) NOT NULL CONSTRAINT [DF_ratePlan2_q_251_300] DEFAULT ((500)),[q_301_400] [numeric](18, 2) NOT NULL CONSTRAINT [DF_ratePlan2_q_300_400] DEFAULT ((650)),[q_401_600] [numeric](18, 2) NOT NULL CONSTRAINT [DF_ratePlan2_q_401_600] DEFAULT ((950)),[q_601] [numeric](18, 2) NOT NULL CONSTRAINT [DF_ratePlan2_q_601] DEFAULT ((1.50)) 1,1854, 225.00, 325.00, 345.00, 400.00, 450.00, 500.00, 650.00, 950.00, 1.502,8140, 225.00, 325.00, 345.00, 400.00, 450.00, 500.00, 650.00, 950.00, 1.50
View 12 Replies
View Related
Apr 18, 2001
I am trying to write a stored procedure that has an inner join between two tables from two different databases on the same sql server.
Something like...
Select * FROM
DB1-table1 INNER JOIN DB2-table1
ON DB1-table1.ID = DB2-table1.ID
yada yada yada.......
Does anyone know how to do this or is it possible? If so, what database should I put the stored procedure in or does it matter?
View 2 Replies
View Related
Aug 3, 1999
Hello.
I'm trying to write a generic Stored Procedure will select records with the following options.
1. Select XXX from theTable
Where ID = 1
2. Select XXX from theTable
Where ID = 1 or ID = 3 or ID = 4
The issues is that at run time the WHERE CLAUSE COULD CHANGE
(it could have multiple OR-d options)
Is there a way to pass a varChar argument like "Where ID=1 or ID =2"?
Thanks in advance!
-andy
View 2 Replies
View Related
Sep 6, 2006
Hi All,
I have a database with very heavy volume of data.
I need to write a stored procedure with 20 parameters as input and it searches in a table . Most of the parameters or NULL , how do I write this procedure without using any dynamic queries.
Ex : To find a customer I have a proc which can accept 20 parameters like CustName, City, State , Phone , Street etc.
Im passing only Custname as parameters and other 19 parameters are NULL.How do I write the WHERE clause ?
Thanks in advance,
HHA
View 4 Replies
View Related
Jan 10, 2004
Hi,
I'm trying to move an asp site from an Access DB to SQL Server.
The site features a search system which allows multiple keywords to be used in up to three fields.
The way I used to manage this was very similar to how it is described below (taken from http://www.planet-source-code.com/vb/scripts/ShowCode.asp?lngWId=4&txtCodeId=6701):
"So you're replacing all of the middle spaces with a SQL 'and' statement. In plain English, if your search phrase is "print bug", this now becomes "'%print%' AND field LIKE '%bug%'" when you concatenate the leading and trailing %'s and quotes (this is for Microsoft Access drivers, other drivers may use different wildcards)--so just append this phrase to the "WHERE field LIKE " phrase, and you're in business."
The trouble I'm having is that the search string is sitting in a Stored Procedure. How can I dynamically append to the search string in this case?
If it isn't possible, how can I go about this?
Cheers all.
__________________________________________________ ___
Code:
ASP:
item=request(item)
'****item = Replace(item, " ","%' AND item like '%")**** - needs attention
rs.Open "Exec getlist "& item &"" objConn, 3
SP:
CREATE PROCEDURE getlist
@item nvarchar(255)
SELECT item FROM publications WHERE item LIKE '%' + @item + '%'
GO
View 14 Replies
View Related
Apr 29, 2008
I believe we can you multiple statements in stored procedures?
Suppose I have a stored procedure and I pass parameters to this SP.
What I am aiming for is to pass some values to the stored procedure, use a select statement to retrieve some values, then have two update statements as below. Updating the same table but with opposite values, both passed as a parameter and retrived, as given below:
CREATE sp_temp_proc
@order_id int,
@order_position int,
@temp_order_id OUTPUT
@temp_order_position OUTPUT,
SELECT order_id AS temp_order_id
FROM <TABLE> WHERE order_position < @order_position
@temp_order_id = temp_order_id
UPDATE <TABLE> SET order_position = @order_position WHERE order_id =
@temp_order_id
UPDATE <TABLE> SET order_position = @temp_order_position WHERE order_id = @order_id
View 2 Replies
View Related
Oct 15, 2014
I want to use 3 transactions in a single stored procedure.First it should update the value and then if the 1st transaction executed successfully then 2nd transaction should start,if 2nd trans executed successfully then 3rd trans should execute.After that only i want to commit all the transactions that is 1,2,3.If any of the trans fails the other shouldn't execute.How do i do this?
USE [recruit]
GO
/****** Object: StoredProcedure [dbo].[Import] Script Date: 10/15/2014 17:13:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Proc [dbo].[Import]
@CustId int
[code].....
View 2 Replies
View Related
Oct 22, 2007
how can we use 2 databases in 1 stored procedure.
like for example i want to get records from 1 table and insert it to other tables in another database.
thnx for the help.
View 1 Replies
View Related
Feb 24, 2008
Hello all,
I'm working on a project for fun with some friends and have run into an issue with stored procedures. I've dealt with SQL quite a bit at my current job, but always from the perspective of somebody querying the database. The database was always managed by someone else and I never had to worry about the underlying code. Now, with my own project at home, I'm trying to deal with a situation and would like to use one, but I'm not sure if it is the best option and if so, exactly how to go about it.
Imagine a site that tracks movies. I have 3 tables:
Movies ( MovieID, Title, DirectorID, ActorID )
Actors ( ActorID, Name )
Director (DirectorID, Name)
This is an overly simple example, but it gets to the heart of my problem.
Okay, now what I'm wanting to do is to be able to write a procedure that would let me create my entries from just one call -- for instance
create_movie( 'Super Movie', 'directorJoe', 'actorJohn' )
that would do the following things:
-Look and see if the given director and actor already exist (from previous films)
-If they do, grab their ID values and use those in the new movie entry
-If they do not, create new entries and get THOSE ID values to use in the new movie entry
Can this be done in a stored procedure (I'm pretty sure it can be) and what sort of commands should I look into -- I'm not looking for a complete solution, cause I want to learn, but I am having trouble finding examples that fit my scenario.
Thanks.
View 3 Replies
View Related
