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 possible to have more than one table footer rows ?

Well, we can add another table footer row by right-clicking on the footer row of the table and select, Inser Row Above, or, Insert Row Below.

But, I want to set different values to same properties of rows. Such as;

Row3.RepeatOnNewPage = False

Row4.RepeatOnNewPage = True

What do you say ?

-SMang the Frustrated Developer

SMang,

You can't hide your table rows independently with the RepeatOnNewPage, but you can set the rows "hidden" property to true or false. The catch here is that you will need to tie it to a toggle item.

Ham

sql

Is it possible to have a matrix row footer?

I have a fairly standard matrix on my report, that looks like this when it
renders (I'm simplifying somewhat to get the idea across):
Account Jan Feb Mar Apr May ..... Dec
Total
Office Supplies 100 150 200 50 100 100
1900
...
Obviously "Account" the the row group, and "Month" is the column group.
This works just fine.
What I'd like to have is have a column AFTER the Total column, that lists
some comments about the account (tied to the Account row group). For
example:
Account Jan Feb Mar Apr May ..... Dec
Total Account comments
Office Supplies 100 150 200 50 100 100
1900 Increased by 5% due to switch to new supplier
I can't figure out how to do this...can someone help?
Thanks,
C17Hi C17,
I understand that you would like to know if you can have a row footer in
the matrix of your report.
If I have misunderstood, please let me know.
I do not think that you can do this in a matrix report. You can only add
row group or column group in the existing matrix. Instead I recommend that
you modify your DataSet query text so that you can use a table for
implementing your requirements. You may refer to PIVOT statement so that
you can rotate the month values from one column to multiple columns.
You may refer to:
Using PIVOT and UNPIVOT
http://msdn2.microsoft.com/en-us/library/ms177410.aspx
Hope this helps. If you have any other questions or concerns, please feel
free to let me know.
Best regards,
Charles Wang
Microsoft Online Community Support
===========================================================Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg@.microsoft.com.
===========================================================Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for
non-urgent issues where an initial response from the community
or a Microsoft Support Engineer within 1 business day is acceptable.
Please note that each follow up response may take approximately
2 business days as the support professional working with you may
need further investigation to reach the most efficient resolution.
The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by
contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
============================================================This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================|||On Mar 21, 3:31=A0pm, "C17" <c...@.community.nospam> wrote:
> I have a fairly standard matrix on my report, that looks like this when it=
> renders (I'm simplifying somewhat to get the idea across):
> =A0 =A0 Account =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Jan =A0 =A0 =A0Feb =A0 =A0 ==A0Mar =A0 =A0 Apr =A0 =A0 May ..... Dec
> Total
> =A0 =A0 Office Supplies =A0 =A0 =A0100 =A0 =A0 150 =A0 =A0 =A0200 =A0 =A0 ==A0 50 =A0 =A0 =A0100 =A0 =A0 =A0 =A0100
> 1900
> =A0 =A0 ...
> Obviously "Account" the the row group, and "Month" is the column group.
> This works just fine.
> What I'd like to have is have a column AFTER the Total column, that lists
> some comments about the account (tied to the Account row group). =A0For
> example:
> =A0 =A0 Account =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Jan =A0 =A0 =A0Feb =A0 =A0 ==A0Mar =A0 =A0 Apr =A0 =A0 May ..... Dec
> Total =A0 =A0 =A0Account comments
> =A0 =A0 Office Supplies =A0 =A0 =A0100 =A0 =A0 150 =A0 =A0 =A0200 =A0 =A0 ==A0 50 =A0 =A0 =A0100 =A0 =A0 =A0 =A0100
> 1900 =A0 =A0 Increased by 5% due to switch to new supplier
> I can't figure out how to do this...can someone help?
> Thanks,
> C17
What I would do is take create a dataset that is a Union of your Data
+ Comments, differentiated by a Group field, then create a Matrix with
the Group field as a Column group, then conditionally hide the
SubTotal on the Comment group.
In english, make a data set that is:
DataType, Account, Month, Amount, Comment
'Amount', 'Office Supplies', #1/1/2007#, 100, Nothing
'Amount', 'Office Supplies', #2/1/2007#, 150, Nothing
'Comment', 'Office Supplies', #1/1/2007#, Nothing, 'Increased by 5%
due to switch to new supplier'
=2E..
The Matrix would be
Column Group1: =3DFields!DataType.Value, Sort Ascending
Column Group2: =3DFields!Month.Value, Sort Ascending
Turn on Column Subtotal2
Maybe add a conditional Hide if InScope(Group2) And Fields!
Group1.Value =3D 'Comment'
Row Group: Account
Details Expression: =3DIIF( First(Fields!DataType.Value) =3D 'Amount',
Sum( Fields!Amount.Value ), First( Fields!Comment.Value ) )
Since I'm not in a Dev environment at the moment, I can't test this,
but you may have to play with the data types to get things to work.
-- Scott

is it possible to freeze column or row in a report?

I am using sql 2000 reporting service. One of the matrix report which has so
many columns and rows and it stretches beyond the width and height of the
paper size specified and the first row and column of the matrix report
showing the description of the data like the column/row header and I wonder
if it is possible to freeze this row and column in the report so that it is
visible when scrolling down or to the right of the report. Thanks.That is possible in RS 2005, but not in RS 2000
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Paul" <paul_mak@.shaw.ca> wrote in message
news:%23O6$fs7EGHA.3700@.TK2MSFTNGP15.phx.gbl...
>I am using sql 2000 reporting service. One of the matrix report which has
>so many columns and rows and it stretches beyond the width and height of
>the paper size specified and the first row and column of the matrix report
>showing the description of the data like the column/row header and I wonder
>if it is possible to freeze this row and column in the report so that it is
>visible when scrolling down or to the right of the report. Thanks.
>

Is it possible to format (Text) in this way

hi,

i want to display something is this fashion

Roth 401(k) Contribution

jfkajfkdjfdjfkdj ldkfdlkfl;dkfldkfld;fkdl

in a table row, but i am not sure how to do it. can some one pls give me some ideas.

Regards,

Karen

If I understand correctly you want the ability to display bold and non-bold text in the same text box?
km
|||yeah thats true|||

I don't think different font properties are possible in a single textbox. Wat you can try doing is put two textboxes (one with Bold font and the other without, as you require) one below the other inside a rectangle and place the rectangle in your Table Row.

-Aayush

|||thanks a lot it works now.

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 it a bug in SQL CE?

Hi!

I use SQL CE with VS.NET. I find the following bug 2th.

The table has an "ID int IDENTITY(0,1) PRIMARY KEY,". That is my row identity.

I add rows to the table, then I realized that the ID order not in the general order (from 0 to ........)

For example: 6,7,8,0,1,2,3,4,5.

Of course row 6,7 and 8 was added the very last.

The content of each row is not mixed, only the ID order.

Is it a very confused, because we develop mobile invoice programs for PDAs.

What I did wrong?

Thank you!

Does that happen in the Query Analyzer on the PDA?

Maybe it does not order the rows by the primary key column by default (?). I don't know if that would be a bug, although it sounds more convenient if it did order on any key columns.

|||I moved this thread to the SQL Mobile's team forusm|||

Neither SQL Server nor SQL Mobile guarrenty you about the physical order of the rows and you are not expected to concluded something from running multiple queries. It can always change. If you want the rows to be ordered on a column, you should ideally use ORDER BY. Your query result (with out ordering) always depends on the cursor position.

Thanks,

Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Mobile, Microsoft Corporation

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.