|
Ok what programming language. If it's .NET then use the System.Net.Mail.SmtpClient to send the mail out.
|
|
|
|
|
How can I make this below statement work for MSSQL 2005
UPDATE TABLE
SET FIELD2 = (SELECT COUNT(*) FROM TABLE)
WHERE FIELD1 = @Parameter
I'm not sure if it will work as is. I was sort of hoping there was an eval fuction, but I haven't found one.
|
|
|
|
|
This should work:
Declare @cnt int
Select @cnt = count(*) from table
UPDATE TABLE
SET FIELD2 = @cnt
WHERE FIELD1 = @Parameter
Ben
|
|
|
|
|
Thanks that looks exactly like what I need to do.
|
|
|
|
|
Ok Is there something I need to do to make it do these statements in a .NET application using a SqlCommand. Possible following each command set with the word GO
Declare @cnt int
GO
SELECT @cnt = COUNT(*) from TABLE
GO
or a \n
|
|
|
|
|
It would be better to put the code into a stored procedure and call the procedure from the sqlCommand object.
Ben
|
|
|
|
|
In general SQLcommands in .net don't like the hard core sql stuff. They just want a select, update, insert, delete or call of a stored procedure.
Ben
|
|
|
|
|
Ok I maybe calling the
DECLARE @cnt int
Select @cnt = count(*) from PlaceInReferralLine
from more than one place so I'm assuming the best thing to do would be put just this code in
the stored procedure.
I'll give that a try, because the orginal code and the stuff I modified kept coming up with a SYNTAX ERROR near @cnt. Of course they couldn't atleast tell me which one was causing the problem from the debugger. Hehe.
Ok. While I am used to getting and retrieving data from different databases I'm still what I consider to be an extreme novice as I only know how to do the basic select, insert, etc.. stuff
So now for a few more questions
1. How do I get it to return the count from the store procedure.
CREATE PROCEDURE dbo.StoredProcedure1
DECLARE @cnt int
Select @cnt = count(*) from TABLE
RETURN @cnt <-I'm assuming
or maybe more simply
CREATE PROCEDURE dbo.StoredProcedure1
RETURN Select count(*) from TABLE
All this is being done in Visual Web Developer from MS.
2. Then I assume to get the data from the stored procedure
INSERT INTO TABLE
(col1, col2)
VALUES ('value1', dpo.StoredProcedure1)
|
|
|
|
|
Ok, there are several ways of doing it. I would guess the easiest is to just return an output parameters. so your SP looks like:
CREATE PROCEDURE dbo.StoredProcedure1
@param1 int,
@cnt int output
as
Select @cnt = count(*) from TABLE where column = @param1
RETURN 0
<pre>
So then when you call the stored procedure you will have to add two sqlparameters to your sqlcommand. The @cnt needs to be marked as direction = output
After you call to executenonquery on the stored procedure you can then check the parameters and get the output value. Then use this value when doing the insert or update.
You can't actually call a stored procedure in an insert statement.
You can call a function, but there are other limitation with functions.
Hope that helps.
Ben
|
|
|
|
|
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
|
|
|
|