|
Ok. I'm beginning to wonder if I shouldn't just do the entire set of procedures in a single stored procedure because I can't take the chance that during the UPDATE or INSERT operations that the record count will change. So maybe I should do something similar to the following.
<br />
create procedure dpo.StoredProcedure1<br />
@cnt int = 0<br />
@AccountID int<br />
@NewAccountID int<br />
AS<br />
SELECT @cnt = Count(*) FROM PlaceInReferralLine<br />
SELECT ReferredBy FROM UserAccounts WHERE AccountID = @NewAccountID<br />
<br />
SELECT AccountID FROM PlaceInReferralLine WHERE PlaceInReferralLine = 1<br />
<br />
UPDATE UserAccounts SET ReferredBy = @AccountID WHERE AccountID = @NewAccountID<br />
<br />
INSERT INTO PlaceInReferralLine (AccountID, PlaceInReferralLine) VALUES (@NewAccountId , @cnt)<br />
UPDATE PlaceInReferralLine SET PlaceInReferralLine = PlaceInReferralLine - 1<br />
<br />
UPDATE PlaceInReferralLine SET PlaceInReferralLine = @cnt WHERE PlaceInReferralLine = @AccountID<br />
RETURN 1<br />
RETURN 1 <- Intended for the entire procedure being successful. Which I guess for .NET to do this I would need to execute as a SqlCommand.ExecuteScalar() operation.
Sorry if there is any confusion with both a col and table name PlaceInReferralLine
|
|
|
|
|
Yes that would be a good idea. Since you are going to just have one stored procedure you should remove the @cnt input parameter and add
Declare @cnt int
after the AS
and before the Select @cnt
You do not have to do an executeScaler to get a returncode. You always get a return code when executing a stored procedure. You can still use executenonquery.
Ben
|
|
|
|
|
Ok now to just go and learn how to use the result sets so I can do my if statements.
|
|
|
|
|
If you use executenonquery the returncode is the result of that call. So you don't need to worry about result sets.
Ben
|
|
|
|
|
I made a second error with the parameters. The only one I will want to pass is the @NewAccountID. The @AccountID must be set by
SELECT AccountID FROM PlaceInReferralLine WHERE PlaceInReferralLine = 1
since there is no posibility of there everybeing more than one of the PlaceInReferralLine being equal to 1.
I also need to make sure
SELECT ReferredBy FROM UserAccounts WHERE AccountID = @NewAccountID
is empty or null before I continue to process. Otherwise the ReferredBy column could be changed.
I supposed I could to this at the program level rather than at the SQL Server level.
|
|
|
|
|
I think I would do this as well in the sql stored procedure. It would be the most efficent there.
Ben
|
|
|
|
|
Ok I've looked, I probably don't know exactly what I need so can you point me in teh right direction for information on how to use the results from the select statements directly within the stored procedure.
|
|
|
|
|
There are several things you can do. I think you said you didn't want the record to exist. I think the best way to do that would be:
if (Select count(*) from table where column = @param) = 0
begin
end
If the record might exist, but the value for the field is null you can do:
if (select isnull(column2,'') from table where column = @param) = ''
Begin
End
Ben
|
|
|
|
|
Ok so how would I do something like
@AccountID = Select AccountID FROM Table where Col2 = 1;
|
|
|
|
|
Declare @accountID int
select @accountid = accountid form table where col2 = 1
There are no ; in sql.
Ben
|
|
|
|
|
Hi there .
During transaction is began , I inserted new data in table , until I don't call commit() or rollback() functions , another processes can not access to this table.
I mean, if i try to add a new data in a table with transaction and before closing transaction(commit or rollback) another process can't read that table; then a exception will occurred.
what is the problem?
DMASTER
|
|
|
|
|
Developer611 wrote: what is the problem?
There is no problem. A transaction shouldn't last long enough for there to be a problem. I'm guessing the other process times out waiting for the table to become available. And while it is in use in a transaction you have made it unavailable.
The solution is to make your transactions shorter.
Upcoming events:
* Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ...
"I wouldn't say boo to a goose. I'm not a coward, I just realise that it would be largely pointless."
My website
|
|
|
|
|
Thanks dear Colin . But how can I do this in short time . Did you have any solutions ?
DMASTER
|
|
|
|
|
Developer611 wrote: But how can I do this in short time . Did you have any solutions ?
What the heck are you actually doing? A transaction should start, the query(ies) run and the transaction committed or rolledback. What else are you doing in there that make it take so long?!
If you start a transaction perform some queries, wait for user input, do some more queries then finally end the transaction then you have some serious redesigning to do.
Upcoming events:
* Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ...
"I wouldn't say boo to a goose. I'm not a coward, I just realise that it would be largely pointless."
My website
|
|
|
|
|
I have 5 or more queries that run in same time and one of queries take along time to done.
I have to do all of this queries in a transaction.
I have no way to do this in separate transactions.
I think transactions work with temporary tables that mean changes will apply to original tables after commit. This means that we can use original tables while a transaction is running!?!
|
|
|
|
|
SalarSoft wrote: I think transactions work with temporary tables that mean changes will apply to original tables after commit. This means that we can use original tables while a transaction is running!?!
No. If you in a transaction and you alter a table you are altering that actual table. No temporary tables are involved unless you explicitly involve them. Changes are made as you make them. If you roll back the changes are reversed.
SalarSoft wrote: I have 5 or more queries that run in same time and one of queries take along time to done.
If you are using SQL Server 2005 you may like to look into Snapshot transactions. That might help you.
Upcoming events:
* Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ...
"I wouldn't say boo to a goose. I'm not a coward, I just realise that it would be largely pointless."
My website
|
|
|
|
|
hi to all brod and sis,
i would like to ask some help for this problem coz i was confuse in computing year and month in one column only like for example i have column date_hired then i want to get the year and then the month versus Now just to get the exact year and month, this is should be the output in sqlsever analyzer..
date_hired Year Month
2005-09-11 2 22 ---- i know that there is no 22 months
how i am going to compute that months coz in our calendar we only have 12 and more that can give some computation for that problem which will be my pattern in making my stored procedure..
thanks in advance
|
|
|
|
|
your query is not clear.
however the result of year and mth from date_hired can be obtained (as per your example) ....
select date_hired, datediff(yy, date_hired, getdate()) AS 'Year', datediff(m, date_hired, getdate()) AS 'Month'
now it will return months as 23
Regards
KP
|
|
|
|
|
Use the DatePart function.
SG
|
|
|
|
|
Try to understand your question... hope this is what you want:
SELECT [date_hired], [Year] = DATEDIFF(m, [date_hired], GETDATE())/ 12, [Month] = DATEDIFF(m, [date_hired], GETDATE()) % 12
reference --> http://www.oin1.com/Technical/SQL/DateVsCurrent.htm[^]
Eliz.K
|
|
|
|
|
Hello everybody
I have a table "Employee" which contains 20 records. i want to select records from 10 to 15. how can I select?
Regards, Qaiser Nadeem
|
|
|
|
|
You need to make a ID field in Employee table from 1 to 20
Then do this
Select * from tblEmployee where ID in [10,15]
It seem to be a solution or an answer.
|
|
|
|
|
select * from Employee<br />
where [id] >= 10 and id <=15
I Love SQL
|
|
|
|
|
If you only want 10 of the 20 records you can do
select top 10 * from yourtable
Ben
|
|
|
|
|
in sql server there is a new feature which will help you to send e-mails via the database .do you know exactly how to do this .
eg:
if(time period expires)
send an e-mail;
|
|
|
|