Is it possible to put several queries into one sp

I have an update query which either inserts a row or increases quantity, depending if row exists or not. It works, better than my explanation probably.

After that query could be a good time to count total of all calculated sub sums.

Something like this.

previous query

END

go
SELECT SUM(SubTotal)
FROM dbo.t_Shoppings

I have tried this on the tool which has a long name, but I think my way didn't work. (Microsoft sql server management studio express)

Is this possible or do I have make and call another stored procedure.

I can send my sp if someone wants.

No, this is for sure possible. Try taking the "END" between your first and second query and put it after your second query. Let me know what errors you are getting when you try to execute it if that doesn't work

|||

you can just put queries on eby one withiout any separator , just start every select statement in new line. Do not use GO it unless you run your query in Management studio environment (Go is not SQL comamnd)

|||

Thank you both for your advises. I'll test the sp later today. And I have to check how to return parameters and so on first.

I'll mark your answers as answers then too.

Regards

Leif

|||

Hi

I tried today my sp and got it working. Server management studio didn't show output parameter right but the query itself seems to work. I'll test it with code later.

Thanks for help.

Here is my stored procedure now. I didn't translate it, so variables may look strange. Kokonaissumma means total value btw.

create PROCEDURE [dbo].[kori2]
(
@.Tuotekoodi varchar(20),
@.kokonaissumma money output
)
AS
BEGIN
SET NOCOUNT ON;
IF NOT EXISTS(SELECT * FROM dbo.t_osto WHERE Tuotekoodi=@.Tuotekoodi)
BEGIN
INSERT dbo.t_osto (Tuotekoodi, Nimi,Malli,Toimittajanimi,Ryhma,Myyntihinta,Alv)
SELECT Tuotekoodi, Nimi,Malli,Toimittajanimi,Ryhma,Myyntihinta,Alv
FROM dbo.t_Tuote
WHERE Tuotekoodi= @.Tuotekoodi
END
ELSE
BEGIN
UPDATE dbo.t_osto
SET Maara=Maara+1
WHERE Tuotekoodi=@.Tuotekoodi
END
END

return (SELECT count(*) FROM dbo.t_osto)

(select @.kokonaissumma=sum(Yhteensa)FROM dbo.t_osto)

Is it ever possible that 2 different rows are inserted at same time into a table?

There is a stored procedure that inserts a row into 'Vendors' table. Is it possible that two different calls to this sp happen at the same time and as a result, each sp inserts into the table its row atexactly the same time?

How you can insert two records same time as your table will be locked while inserting data.

Is having a trigger that inserts a row in Table A, when a row in same table is inserted by

I want to insert a row for a Global user in Table 'A' whenever ADO.Net code inserts a Local user row into same table. I recommended using a trigger to implement this functionality, but the DBA was against it, saying that stored proecedures should be used, since triggers are unreliable and slow down the system by placing unecessary locks on the table. Is this true OR the DBA is saying something wrong? My thinking is that Microsoft will never include triggers if they are unreliable and the DBA is just wanting to offload the extra DBA task of triggers to the programmer so that a stored procedure is getting called, so he has less headache on his hands.

Thanks

Hey,

I would prefer stored procedure; the reason is I have no control over triggers, so it makes it easier from a maintenance perspective on me, which is what I think you may also be under that restraint? I don't know how much slowing down it would do; I would think a lot of that depends on volume of data as well.

If you are inserting the main data using a stored procedure, then that stored procedure can do two inserts. You can also group them in a transaction so if one fails, both statements are rolled back for safety purposes.

Microsoft includes a lot of things: they include xp_cmdshell which most DBA's disable because of the power of it. So they will include things even though they may not be the best to implement...

|||

Actually, your DBA is correct. I always recommend, if possible, opting for stored procedures as opposed to triggers. The trigger will save you time if you are inserting rows into the table from many different sources in many different ways. But, if the only point of entry is a stored procedure, there's no reason not to place it in the sp. The farther back in SQL Server version you go, the less reliable triggers seem to get - and, yes, they can place locks on tables. Microsoft included them because they have their place - but it is a mistake to use them as substitutes for logical flow.

This shouldn't be a lot of extra work on you. If you aren't using a stored procedure already, they are much faster and far more secure than "on page" SQL. If you have insert statements in various parts of your application inserting to the same table, then you should be encapsulating them into a stored procedure anyway! Your DBA's job is to protect the efficiency and cleanliness of your database. Adding triggers unnecessarily affects both.

Is complete control over subscriber-to-publisher inserts/updates possible?

Hi, we are using merge replication from our server (SQL Server 2005) to what
will soon be hundreds of subscribers. The subscribers are actually from a
Windows app that uses a SQLCE database. The client app is able to insert and
update records that are then merged up to the server. Everything I have
mentioned up to this point is already functional, but we recently realized
that more "control" over the replication process is needed. Here's what we
want to do: When a record that was inserted at the subscriber is replicated
up to the server, it must be caught and redirected to a stored procedure
which will perform the insert instead. This should be completely transparent
to the subscriber (meaning we can't return them an error code).
Unless I am mistaken the only way to get this kind of functionality is by
using a business logic handler. I have a sample which demonstrates some very
basic uses for the business logic handler but unfortunately it barely
scratches the surface of what is possible. This doc page from the BO explains
a scenario very similar to what we need (Custom Change Handling -> Apply
custom data), but I do not know how I would go about implementing it.
Can anyone who has done something like this before point me in the right
direction?
Using the BusinessLogic resolver is the correct way of doing it.
If the logic is such that it is only in one direction or only one table is
affected you can either use transactional replication from the subscribers
to the publisher (you will need to upgrade your subscribers to SQL Server
2005 standard for this), or have a trigger hanging of the publisher table so
when the insert arrives this trigger will process it and fire the stored
procedure.
You can use the session_property function to detect if the process doing the
dml is a replication process.
if convert(bit, sessionproperty('replication_agent'))=1
do work.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Chris" <no@.spam.com> wrote in message
news:%2367uVkx0HHA.4932@.TK2MSFTNGP03.phx.gbl...
> Hi, we are using merge replication from our server (SQL Server 2005) to
> what
> will soon be hundreds of subscribers. The subscribers are actually from a
> Windows app that uses a SQLCE database. The client app is able to insert
> and
> update records that are then merged up to the server. Everything I have
> mentioned up to this point is already functional, but we recently realized
> that more "control" over the replication process is needed. Here's what we
> want to do: When a record that was inserted at the subscriber is
> replicated
> up to the server, it must be caught and redirected to a stored procedure
> which will perform the insert instead. This should be completely
> transparent
> to the subscriber (meaning we can't return them an error code).
> Unless I am mistaken the only way to get this kind of functionality is by
> using a business logic handler. I have a sample which demonstrates some
> very
> basic uses for the business logic handler but unfortunately it barely
> scratches the surface of what is possible. This doc page from the BO
> explains
> a scenario very similar to what we need (Custom Change Handling -> Apply
> custom data), but I do not know how I would go about implementing it.
> Can anyone who has done something like this before point me in the right
> direction?
|||Thank you Hilary. I would prefer to use the business logic resolver though if
possible, but the more I dig into this it seems like it won't work the way I
need it to. I believe all I can do with it is modify the record sent from the
subscriber. Please correct me if I'm wrong, but it will not allow me to
*stop* the insert unless I outright reject it. Meaning I can't "redirect" the
insert to a SP and still tell the subscriber that everything went fine.
Using a trigger on the publisher table seems interresting. I will have to
look into that further. Thanks for your insight.
Chris
On 7/31/2007 6:39:27 AM, "Hilary Cotter" wrote:
> Using the BusinessLogic resolver is the correct way of doing it.
> If the logic is such that it is only in one direction or only one table is
> affected you can either use transactional replication from the subscribers
> to the publisher (you will need to upgrade your subscribers to SQL Server
> 2005 standard for this), or have a trigger hanging of the publisher table so
> when the insert arrives this trigger will process it and fire the stored
> procedure.
> You can use the session_property function to detect if the process doing the
> dml is a replication process.
> if convert(bit, sessionproperty('replication_agent'))=1
> do work.
>