create a view that return data and its count of description

I have a view that has a link to 3 tables.
I need to dispaly only the data in one table and list how
many that data is defined in one table.
here is the code for the first view.
USE Aromatherapy
GO
if exists (select name from sysobjects
where name = 'Oils_Cautions_View' and type
= 'V')
DROP VIEW
Oils_Cautions_View
go
CREATE VIEW Oils_Cautions_View AS
SELECT o.oilID, oilName,
Description FROM Oils AS o, Cautions as c, OilCautions as
oc
Where o.OILID = oc.OilID AND c.CautionID = oc.cautionID
All I need is the oilName and its COUNT of cautions.
let say the oil name is "Basil" and it has three counts.
All I need is Basil in one coumn and in the other coulmn
I need "3"
Thank youWithout DDL, I'm taking some guesses, but try:
SELECT o.oilName, count(*) AS CautionCount
FROM Oils AS o
join OilCautions AS oc
on o.OILID = oc.OILID
group by o.oilName
HTH
Vern
>--Original Message--
>I have a view that has a link to 3 tables.
>I need to dispaly only the data in one table and list how
>many that data is defined in one table.
>here is the code for the first view.
>USE Aromatherapy
>GO
>if exists (select name from sysobjects
> where name = 'Oils_Cautions_View' and type
>= 'V')
> DROP VIEW
> Oils_Cautions_View
>go
>CREATE VIEW Oils_Cautions_View AS
>SELECT o.oilID, oilName,
>Description FROM Oils AS o, Cautions as c, OilCautions as
>oc
>Where o.OILID = oc.OilID AND c.CautionID = oc.cautionID
>All I need is the oilName and its COUNT of cautions.
>let say the oil name is "Basil" and it has three counts.
>All I need is Basil in one coumn and in the other coulmn
>I need "3"
>Thank you
>.
>|||Hi Vern,
Thank you very much for your time
Cristian
>--Original Message--
>Without DDL, I'm taking some guesses, but try:
>SELECT o.oilName, count(*) AS CautionCount
>FROM Oils AS o
>join OilCautions AS oc
> on o.OILID = oc.OILID
>group by o.oilName
>HTH
>Vern
>>--Original Message--
>>I have a view that has a link to 3 tables.
>>I need to dispaly only the data in one table and list
how
>>many that data is defined in one table.
>>here is the code for the first view.
>>USE Aromatherapy
>>GO
>>if exists (select name from sysobjects
>> where name = 'Oils_Cautions_View' and
type
>>= 'V')
>> DROP VIEW
>> Oils_Cautions_View
>>go
>>CREATE VIEW Oils_Cautions_View AS
>>SELECT o.oilID, oilName,
>>Description FROM Oils AS o, Cautions as c, OilCautions
as
>>oc
>>Where o.OILID = oc.OilID AND c.CautionID = oc.cautionID
>>All I need is the oilName and its COUNT of cautions.
>>let say the oil name is "Basil" and it has three counts.
>>All I need is Basil in one coumn and in the other
coulmn
>>I need "3"
>>Thank you
>>.
>.
>

create a view

How can I create a view which based on a store procedure and then sp will
return a select statement only? Thanks.You can't (*). A view is one SELECT statement (which can be complex in itself), and a SELECT
statement read data from tables or views, it doesn't execute stored procedures. Consider re-writing
the procedure to a table-valued function.
(*) Well, you can by using OPENROWSET or OPENQUERY, but that is a hack and I wouldn't use it in
production.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"00KobeBrian" <a@.b.com> wrote in message news:OWbx40TXGHA.196@.TK2MSFTNGP04.phx.gbl...
> How can I create a view which based on a store procedure and then sp will return a select
> statement only? Thanks.
>|||Hi,
I'd like to know your concern clearly:
You want to create a store procedure which return a data set and you want
to create a view based on the data set.
I think you should use User Defined Function(UDF), it Returns a table Data
Type which can be used after As in Create View statement.
You can refer to following link for sample of UDF.
<http://msdn.microsoft.com/library/default.asp?url=/library/en-us/samples/sa
mples_1far.asp>
Hope it helps.
Best regards,
Vincent Xu
Microsoft Online Partner Support
======================================================Get Secure! - www.microsoft.com/security
======================================================When responding to posts, please "Reply to Group" via your newsreader so
that others
may learn and benefit from this issue.
======================================================This posting is provided "AS IS" with no warranties,and confers no rights.
======================================================>>From: "00KobeBrian" <a@.b.com>
>>Subject: create a view
>>Date: Tue, 11 Apr 2006 15:54:51 +0800
>>Lines: 4
>>X-Priority: 3
>>X-MSMail-Priority: Normal
>>X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
>>X-RFC2646: Format=Flowed; Original
>>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
>>Message-ID: <OWbx40TXGHA.196@.TK2MSFTNGP04.phx.gbl>
>>Newsgroups: microsoft.public.sqlserver.server
>>NNTP-Posting-Host: 210.177.248.66
>>Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP01.phx.gbl!TK2MSFTNGP04.phx.gbl
>>Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.server:427533
>>X-Tomcat-NG: microsoft.public.sqlserver.server
>>How can I create a view which based on a store procedure and then sp will
>>return a select statement only? Thanks.
>>|||or, maybe you'd be happier skipping the whole view thing entirely, and
using the stored procedures to return the table and or data you are
after.

Create a string with carriage return and line feed

Hello,
Is it possible to create a variable that has carriage return and line feed
embed inside. Say I have 3 fields: field1, field2, field3. I want to return
a
string with carriage returns and line feeds between field1, 2 and field2, 3.
Thanks in advanceLOOK in BOL for CHAR,
TAB Char(9)
LineFeed Char(10)
CarriageReturn Char(13)
HTH, Jens Smeyer.
http://www.sqlserver2005.de
--
"Ed Chiu" <EdChiu@.discussions.microsoft.com> schrieb im Newsbeitrag
news:462D3FCC-28E9-4660-A5F7-559D5CB3CA33@.microsoft.com...
> Hello,
> Is it possible to create a variable that has carriage return and line feed
> embed inside. Say I have 3 fields: field1, field2, field3. I want to
> return a
> string with carriage returns and line feeds between field1, 2 and field2,
> 3.
> Thanks in advance|||> Is it possible to create a variable that has carriage return and line feed
> embed inside. Say I have 3 fields: field1, field2, field3. I want to
return a
> string with carriage returns and line feeds between field1, 2 and field2,
3.
Include CHAR(13)+CHAR(10) when you are concatenating the strings.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com|||Try,
select 'Microsoft' + char(13) + char(10) + 'SQL' + char(13) + char(10) +
'Server' + char(13) + char(10) + '2000'
AMB
"Ed Chiu" wrote:

> Hello,
> Is it possible to create a variable that has carriage return and line feed
> embed inside. Say I have 3 fields: field1, field2, field3. I want to retur
n a
> string with carriage returns and line feeds between field1, 2 and field2,
3.
> Thanks in advance|||DECLARE @.var VARCHAR(50)
SET @.var = 'joe' + CHAR(13) + CHAR(10) + 'blow'
SELECT @.var
It's up to the consumer of the data to display it properly. For example
in Query Analyzer, if you choose to display results in a grid, it
replaces the carriage return, line feed with spaces. If you choose to
display in text mode, it'll put the carriage return, line feed in there
for you.
If you're returning this to a .net dataset, you can display it properly.
HTH...
Joe Webb
SQL Server MVP
~~~
Get up to speed quickly with SQLNS
http://www.amazon.com/exec/obidos/t...il/-/0972688811
Ed Chiu wrote:
> Hello,
> Is it possible to create a variable that has carriage return and line feed
> embed inside. Say I have 3 fields: field1, field2, field3. I want to retur
n a
> string with carriage returns and line feeds between field1, 2 and field2,
3.
> Thanks in advancesql

Create a string from report parameters, return checksum

Hi,
We want to add an extra checksum parameter to our RS report, and make the
report work only if the correct checksum is entered/passed based on all the
other parameter fields, as a simple security check when rendering reports
from a system with its own security system (users not in AD/domains).
1. How do I construct a stored procedure that creates a string consisting of
all but the last parameter and returns a checksum ? (checksums we know how to
create from strings)
2. How do I filter my report based on that ?
"where ... and @.checksum = checksum_proc.checksum" ?
Report and parameters ex.
report1: product_string, checksum
report2: customer_string, department_string, date_string, checksum
Or is there a better way ?
/JeromeOn Mar 5, 6:04 am, /jerome k <jero...@.discussions.microsoft.com>
wrote:
> Hi,
> We want to add an extra checksum parameter to our RS report, and make the
> report work only if the correct checksum is entered/passed based on all the
> other parameter fields, as a simple security check when rendering reports
> from a system with its own security system (users not in AD/domains).
> 1. How do I construct a stored procedure that creates a string consisting of
> all but the last parameter and returns a checksum ? (checksums we know how to
> create from strings)
> 2. How do I filter my report based on that ?
> "where ... and @.checksum = checksum_proc.checksum" ?
> Report and parameters ex.
> report1: product_string, checksum
> report2: customer_string, department_string, date_string, checksum
> Or is there a better way ?
> /Jerome
Here is another option you might want to consider. You might want to
have a report parameter that has a string datatype and is used as a
password. You might pass the password entered by the user back to the
stored procedure and if the password matches one in a list somewhere
(table, etc) you send the complete dataset back to the report;
otherwise, you send back no data or a single line of all nulls or a
text message of "you do not have correct permissions to access this
report" to the report. Also, to enforce security, you might create the
stored procedure using "with encryption" that way noone can look at
the logic -or- you could add a certain number of characters to the
true password in a table and just remove them when you do the table
lookup for the passwords. Hope this is helpful.
Regards,
Enrique Martinez
Sr. SQL Server Developer|||Thanks,
The report must be started with a URL (cant render reports from our system)
and should only be allowed for a certain combinations of parameters set by
our program, ex product 100 with department A. The user must not seconds
later go to the reportserver and manually enter product 100 with department B
with the same password.
If using a "one-time" password parameter, will this mean we should store all
parameters in a table as well ? If we delete the stored password in the
procedure, is it possible for the user to re-render the report to Excel etc
?
A last checksum question: Is there a function to be used in a stored
procedure that gets current report's parameter 1, 2 ... ?
/Jerome k
"EMartinez" wrote:
> On Mar 5, 6:04 am, /jerome k <jero...@.discussions.microsoft.com>
> wrote:
> > Hi,
> >
> > We want to add an extra checksum parameter to our RS report, and make the
> > report work only if the correct checksum is entered/passed based on all the
> > other parameter fields, as a simple security check when rendering reports
> > from a system with its own security system (users not in AD/domains).
> >
> > 1. How do I construct a stored procedure that creates a string consisting of
> > all but the last parameter and returns a checksum ? (checksums we know how to
> > create from strings)
> >
> > 2. How do I filter my report based on that ?
> > "where ... and @.checksum = checksum_proc.checksum" ?
> >
> > Report and parameters ex.
> > report1: product_string, checksum
> > report2: customer_string, department_string, date_string, checksum
> >
> > Or is there a better way ?
> >
> > /Jerome
>
> Here is another option you might want to consider. You might want to
> have a report parameter that has a string datatype and is used as a
> password. You might pass the password entered by the user back to the
> stored procedure and if the password matches one in a list somewhere
> (table, etc) you send the complete dataset back to the report;
> otherwise, you send back no data or a single line of all nulls or a
> text message of "you do not have correct permissions to access this
> report" to the report. Also, to enforce security, you might create the
> stored procedure using "with encryption" that way noone can look at
> the logic -or- you could add a certain number of characters to the
> true password in a table and just remove them when you do the table
> lookup for the passwords. Hope this is helpful.
> Regards,
> Enrique Martinez
> Sr. SQL Server Developer
>|||What do you propose to do to reject the user's request if it is invalid by
the rules (see thread: "Am I crazy or is there no form validation" in this
forum). I'm not arguing with you, just wondering what you think is the best
strategy here.
IAC, if I were faced with your requirement, I would probably have the users
submit their reporting URL to a small proxy web application that did the
validation you require against the user's credentials, etc. If the tests
passed (whatever they are) then the proxy would submit the request to the
report server and return the server's response.
By "small" I mean that you could probably get away with a simple APX page
for this, and in addition you would have the ability to return whatever type
of user feedback you wanted if the tests failed.
>L<
"/jerome k" <jeromek@.discussions.microsoft.com> wrote in message
news:8A257C82-B978-446C-A2B7-0FFAC3773B32@.microsoft.com...
> Thanks,
> The report must be started with a URL (cant render reports from our
> system)
> and should only be allowed for a certain combinations of parameters set by
> our program, ex product 100 with department A. The user must not seconds
> later go to the reportserver and manually enter product 100 with
> department B
> with the same password.
> If using a "one-time" password parameter, will this mean we should store
> all
> parameters in a table as well ? If we delete the stored password in the
> procedure, is it possible for the user to re-render the report to Excel
> etc
> ?
> A last checksum question: Is there a function to be used in a stored
> procedure that gets current report's parameter 1, 2 ... ?
> /Jerome k
> "EMartinez" wrote:
>> On Mar 5, 6:04 am, /jerome k <jero...@.discussions.microsoft.com>
>> wrote:
>> > Hi,
>> >
>> > We want to add an extra checksum parameter to our RS report, and make
>> > the
>> > report work only if the correct checksum is entered/passed based on all
>> > the
>> > other parameter fields, as a simple security check when rendering
>> > reports
>> > from a system with its own security system (users not in AD/domains).
>> >
>> > 1. How do I construct a stored procedure that creates a string
>> > consisting of
>> > all but the last parameter and returns a checksum ? (checksums we know
>> > how to
>> > create from strings)
>> >
>> > 2. How do I filter my report based on that ?
>> > "where ... and @.checksum = checksum_proc.checksum" ?
>> >
>> > Report and parameters ex.
>> > report1: product_string, checksum
>> > report2: customer_string, department_string, date_string, checksum
>> >
>> > Or is there a better way ?
>> >
>> > /Jerome
>>
>> Here is another option you might want to consider. You might want to
>> have a report parameter that has a string datatype and is used as a
>> password. You might pass the password entered by the user back to the
>> stored procedure and if the password matches one in a list somewhere
>> (table, etc) you send the complete dataset back to the report;
>> otherwise, you send back no data or a single line of all nulls or a
>> text message of "you do not have correct permissions to access this
>> report" to the report. Also, to enforce security, you might create the
>> stored procedure using "with encryption" that way noone can look at
>> the logic -or- you could add a certain number of characters to the
>> true password in a table and just remove them when you do the table
>> lookup for the passwords. Hope this is helpful.
>> Regards,
>> Enrique Martinez
>> Sr. SQL Server Developer
>>

Create a sales report

I am using SQL 2000 reporting service. I need to create a sales report
described below.
1. Return the top 10 clients in terms of the sales in Month/Year such as
Sep. 2006.
2. Return the sales to these 10 clients in the past 11 months of the above
entered date such as from Aug. 2005 to Aug. 2006.
3. Line them up to compare the sales in 12 months.
It's very easy to return the top 10 clients. However, I really don't know
how to complete the second retrun. Since the result should be based on the
return of the first query and the result of the first query is dynamic. Is it
possible to have this done in SQL reporting service? I can't find any related
document. Please advice.
Thanks.the Employee Sales Report sample report for Adventureworks does something
very similar to this...The drill down, I think, is either a link or subreport.
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"Jun" wrote:
> I am using SQL 2000 reporting service. I need to create a sales report
> described below.
> 1. Return the top 10 clients in terms of the sales in Month/Year such as
> Sep. 2006.
> 2. Return the sales to these 10 clients in the past 11 months of the above
> entered date such as from Aug. 2005 to Aug. 2006.
> 3. Line them up to compare the sales in 12 months.
> It's very easy to return the top 10 clients. However, I really don't know
> how to complete the second retrun. Since the result should be based on the
> return of the first query and the result of the first query is dynamic. Is it
> possible to have this done in SQL reporting service? I can't find any related
> document. Please advice.
> Thanks.

CREATE / EXECUTE Stored Procedure (SQL Server 7.0)

Hi,
I create one stored procedure to return some objects, this procedure accepts
two parameters and this parameters have a default value.
When i execute the procedure with no values the default parameters are used
and every thing goes fine, when i need to input these parameters the
following error appears.
--exec procedure_name db_name, user_name
Error:
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'user_name'.
Can you help me.
Best regardsI guess it should be
exec procedure_name 'db_name', 'user_name'
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:4A3D9388-DB65-4CAA-9F29-13C2D5B735AE@.microsoft.com...
> Hi,
> I create one stored procedure to return some objects, this procedure
> accepts
> two parameters and this parameters have a default value.
> When i execute the procedure with no values the default parameters are
> used
> and every thing goes fine, when i need to input these parameters the
> following error appears.
> --exec procedure_name db_name, user_name
> Error:
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'user_name'.
> Can you help me.
> Best regards
>|||If quoting the parameters doesn't fix it, then post the SP source so we can
see.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:4A3D9388-DB65-4CAA-9F29-13C2D5B735AE@.microsoft.com...
> Hi,
> I create one stored procedure to return some objects, this procedure
accepts
> two parameters and this parameters have a default value.
> When i execute the procedure with no values the default parameters are
used
> and every thing goes fine, when i need to input these parameters the
> following error appears.
> --exec procedure_name db_name, user_name
> Error:
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'user_name'.
> Can you help me.
> Best regards
>

CREATE / EXECUTE Stored Procedure (SQL Server 7.0)

Hi,
I create one stored procedure to return some objects, this procedure accepts
two parameters and this parameters have a default value.
When i execute the procedure with no values the default parameters are used
and every thing goes fine, when i need to input these parameters the
following error appears.
--exec procedure_name db_name, user_name
Error:
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'user_name'.
Can you help me.
Best regardsI guess it should be
exec procedure_name 'db_name', 'user_name'
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:4A3D9388-DB65-4CAA-9F29-13C2D5B735AE@.microsoft.com...
> Hi,
> I create one stored procedure to return some objects, this procedure
> accepts
> two parameters and this parameters have a default value.
> When i execute the procedure with no values the default parameters are
> used
> and every thing goes fine, when i need to input these parameters the
> following error appears.
> --exec procedure_name db_name, user_name
> Error:
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'user_name'.
> Can you help me.
> Best regards
>|||If quoting the parameters doesn't fix it, then post the SP source so we can
see.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:4A3D9388-DB65-4CAA-9F29-13C2D5B735AE@.microsoft.com...
> Hi,
> I create one stored procedure to return some objects, this procedure
accepts
> two parameters and this parameters have a default value.
> When i execute the procedure with no values the default parameters are
used
> and every thing goes fine, when i need to input these parameters the
> following error appears.
> --exec procedure_name db_name, user_name
> Error:
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'user_name'.
> Can you help me.
> Best regards
>sql

CREATE / EXECUTE Stored Procedure (SQL Server 7.0)

Hi,
I create one stored procedure to return some objects, this procedure accepts
two parameters and this parameters have a default value.
When i execute the procedure with no values the default parameters are used
and every thing goes fine, when i need to input these parameters the
following error appears.
--exec procedure_name db_name, user_name
Error:
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'user_name'.
Can you help me.
Best regards
I guess it should be
exec procedure_name 'db_name', 'user_name'
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:4A3D9388-DB65-4CAA-9F29-13C2D5B735AE@.microsoft.com...
> Hi,
> I create one stored procedure to return some objects, this procedure
> accepts
> two parameters and this parameters have a default value.
> When i execute the procedure with no values the default parameters are
> used
> and every thing goes fine, when i need to input these parameters the
> following error appears.
> --exec procedure_name db_name, user_name
> Error:
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'user_name'.
> Can you help me.
> Best regards
>
|||If quoting the parameters doesn't fix it, then post the SP source so we can
see.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:4A3D9388-DB65-4CAA-9F29-13C2D5B735AE@.microsoft.com...
> Hi,
> I create one stored procedure to return some objects, this procedure
accepts
> two parameters and this parameters have a default value.
> When i execute the procedure with no values the default parameters are
used
> and every thing goes fine, when i need to input these parameters the
> following error appears.
> --exec procedure_name db_name, user_name
> Error:
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'user_name'.
> Can you help me.
> Best regards
>

Couple of Simple questions

I know that it is good practice to use NO COUNT and RETURN in a stored
procedure but what does that do for the stored proc? I cannot seem to find a
solid answer on this except its good practice.
thanks in advance
rob
Rob,
Read this:
http://www.sql-server-performance.co...procedures.asp
HTH
Jerry
"Rob" <temp@.dstek.com> wrote in message
news:ukfTz%23FyFHA.720@.TK2MSFTNGP15.phx.gbl...
>I know that it is good practice to use NO COUNT and RETURN in a stored
> procedure but what does that do for the stored proc? I cannot seem to find
> a
> solid answer on this except its good practice.
>
> thanks in advance
> rob
>

Couple of Simple questions

I know that it is good practice to use NO COUNT and RETURN in a stored
procedure but what does that do for the stored proc? I cannot seem to find a
solid answer on this except its good practice.
thanks in advance
robRob,
Read this:
http://www.sql-server-performance.c..._procedures.asp
HTH
Jerry
"Rob" <temp@.dstek.com> wrote in message
news:ukfTz%23FyFHA.720@.TK2MSFTNGP15.phx.gbl...
>I know that it is good practice to use NO COUNT and RETURN in a stored
> procedure but what does that do for the stored proc? I cannot seem to find
> a
> solid answer on this except its good practice.
>
> thanks in advance
> rob
>

Couple of Simple questions

I know that it is good practice to use NO COUNT and RETURN in a stored
procedure but what does that do for the stored proc? I cannot seem to find a
solid answer on this except its good practice.
thanks in advance
robRob,
Read this:
http://www.sql-server-performance.com/stored_procedures.asp
HTH
Jerry
"Rob" <temp@.dstek.com> wrote in message
news:ukfTz%23FyFHA.720@.TK2MSFTNGP15.phx.gbl...
>I know that it is good practice to use NO COUNT and RETURN in a stored
> procedure but what does that do for the stored proc? I cannot seem to find
> a
> solid answer on this except its good practice.
>
> thanks in advance
> rob
>

CountRows

I have a query structured to return products sold by date. I'm then grouping them by the year they were sold, and totalling up sales..etc. The problem is when I run my sql query that retrieves the data in the SQL designer, the query retrieves 97 rows. When I run the report, and check the number of rows returned (with CountRows()) I get 67. However the remaining 30 rows still show up and are grouped on the next page! Why is this happening? Thanks!It would appear that it's splitting the data into two queries... one with 67 rows and one with 30 rows. I do not want this behavior to occur. What may I do?|||

Try moving the counter out of the group row and put CountRows() in the table row.

Tableheader CountRows()

Group1header "Not here"

data

group1footer

Tablefooter "or here" CountRows()

|||

Thanks for the speedy reply! With that in the table header it's still returning 67/30. I think I've narrowed the problem down to the List grouping expression, but I don't understand why it considers the same value to belong to a different group?

|||It had to do with the list... Removing it and throwing in just a table fixed the problem. When would you ever need to use a list? Thanks

Counting rows by a dynamic SQL statements

I need to count number of record return by a SQL statement. Any idea to do
this?
This is the sample SQL. You can try in Northwind database.
The subquery inside COUNT() expression actually is a dynamic sql which I may
change it anytime. My primary purpose is to get number of record after I
executed a dynamic SQL statement. The query below defintely cannot work
because COUNT() doesn;t take subquery.
At first, I try to use @.@.rowcount which return the number of record. Besides
using @.@.rowcount, any other T-SQL can be used?
DECLARE @.cnt int
SELECT @.cnt=COUNT(
SELECT Customers.ContactName
FROM Customers
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID
WHERE Customers.Country <> 'USA' AND Customers.Country <> 'Mexico'
GROUP BY Customers.CustomerID, Customers.ContactName, Customers.Address,
Customers.City, Customers.Country
HAVING (SUM([Order Details].UnitPrice*[Order Details].Quantity))>1000
)You can specify your SQL query as a derived table and use sp_executesql to
return the count variable as an output parameter. This will return the
count without the accompanying data:
USE Northwind
DECLARE @.cnt int
EXEC sp_executesql
N'
SELECT @.cnt = COUNT(*)
FROM (
SELECT Customers.ContactName
FROM Customers
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID
WHERE Customers.Country <> ''USA'' AND Customers.Country <> ''Mexico''
GROUP BY Customers.CustomerID, Customers.ContactName, Customers.Address,
Customers.City, Customers.Country
HAVING (SUM([Order Details].UnitPrice*[Order Details].Quantity))>1000
) AS t
',
N'@.cnt int OUT',
@.cnt OUT
SELECT @.cnt
Use a similar technique to get both the resultset and count:
USE Northwind
DECLARE @.cnt int
EXEC sp_executesql
N'
SELECT Customers.ContactName
FROM Customers
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID
WHERE Customers.Country <> ''USA'' AND Customers.Country <> ''Mexico''
GROUP BY Customers.CustomerID, Customers.ContactName, Customers.Address,
Customers.City, Customers.Country
HAVING (SUM([Order Details].UnitPrice*[Order Details].Quantity))>1000
SET @.cnt = @.@.ROWCOUNT
',
N'@.cnt int OUT',
@.cnt OUT
SELECT @.cnt
Hope this helps.
Dan Guzman
SQL Server MVP
"Joel Leong" <ch_leong@.hotmail.com> wrote in message
news:ehYf%23NOHFHA.3944@.TK2MSFTNGP10.phx.gbl...
>I need to count number of record return by a SQL statement. Any idea to do
>this?
> This is the sample SQL. You can try in Northwind database.
> The subquery inside COUNT() expression actually is a dynamic sql which I
> may change it anytime. My primary purpose is to get number of record after
> I executed a dynamic SQL statement. The query below defintely cannot work
> because COUNT() doesn;t take subquery.
> At first, I try to use @.@.rowcount which return the number of record.
> Besides using @.@.rowcount, any other T-SQL can be used?
>
> DECLARE @.cnt int
> SELECT @.cnt=COUNT(
> SELECT Customers.ContactName
> FROM Customers
> INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
> INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID
> WHERE Customers.Country <> 'USA' AND Customers.Country <> 'Mexico'
> GROUP BY Customers.CustomerID, Customers.ContactName, Customers.Address,
> Customers.City, Customers.Country
> HAVING (SUM([Order Details].UnitPrice*[Order Details].Quantity))>1000
> )
>

counting number of deleted rows

Hi

I have a stored procedure which deletes a number of rows from a number of different tables. How to i count/return the number of deleted rows in each table?

Here is my stored procedure if it helps:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[usp_delete_entry]
@.new_venue_id int
AS
BEGIN


DECLARE @.new_customer_id int
SET @.new_customer_id = (SELECT customer_id FROM VENUE WHERE venue_id = @.new_venue_id)

DELETE FROM FEATURED WHERE venue_id = @.new_venue_id
DELETE FROM FACILITIES WHERE venue_id = @.new_venue_id
DELETE FROM SIC WHERE venue_id = @.new_venue_id
DELETE FROM SUBSCRIPTION WHERE venue_id = @.new_venue_id
DELETE FROM ADMIN WHERE venue_id = @.new_venue_id
DELETE FROM VENUE WHERE venue_id = @.new_venue_id
DELETE FROM CUSTOMER WHERE customer_id = @.new_customer_id

END

thanks@.@.rowcount global variable holds the number of rows affected for the latest query.
something like this.

ALTER PROCEDURE [dbo].[usp_delete_entry]
@.new_venue_id int
AS
BEGIN
DECLARE @.count1 int, @.count2 int ,@.count3 int, @.count4 int, @.count5 int,
@.count6 int, @.count7 int

DECLARE @.new_customer_id int
SET @.new_customer_id = (SELECT customer_id FROM VENUE WHERE venue_id = @.new_venue_id)

DELETE FROM FEATURED WHERE venue_id = @.new_venue_id
SET @.count1=@.@.rowcount
DELETE FROM FACILITIES WHERE venue_id = @.new_venue_id
SET @.count2=@.@.rowcount
DELETE FROM SIC WHERE venue_id = @.new_venue_id
SET @.count3=@.@.rowcount
DELETE FROM SUBSCRIPTION WHERE venue_id = @.new_venue_id
SET @.count4=@.@.rowcount
DELETE FROM ADMIN WHERE venue_id = @.new_venue_id
SET @.count5=@.@.rowcount
DELETE FROM VENUE WHERE venue_id = @.new_venue_id
SET @.count6=@.@.rowcount
DELETE FROM CUSTOMER WHERE customer_id = @.new_customer_id
SET @.count7=@.@.rowcount

select @.count1, @.count2,@.count3,@.count4,@.count5,@.count6,@.count7

END|||thats spot on, thanks|||Keep in mind (voice of experience speaking) that ANY SELECT operation will change the value of the @.@.ROWCOUNT system variable.
DECLARE @.recCount int
declare @.RowCount int
Select @.recCount = count(*) from osid (nolock)
select @.recCount
Select @.RowCount = @.@.ROWCOUNT
select @.RowCount

Not necessarily an issue - but something to be aware of, since I have had my buttocks spanked (Ohhhhhh...) on a few occasions by test stub code inserted or other such foolishness.|||Hi,

You can use @.@.ROWCOUNT global variable after each delete and assiging to other local variables to return etc.

I think it is helpful.

Cheers
Riaz

Hi

I have a stored procedure which deletes a number of rows from a number of different tables. How to i count/return the number of deleted rows in each table?

Here is my stored procedure if it helps:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[usp_delete_entry]
@.new_venue_id int
AS
BEGIN


DECLARE @.new_customer_id int
SET @.new_customer_id = (SELECT customer_id FROM VENUE WHERE venue_id = @.new_venue_id)

DELETE FROM FEATURED WHERE venue_id = @.new_venue_id
DELETE FROM FACILITIES WHERE venue_id = @.new_venue_id
DELETE FROM SIC WHERE venue_id = @.new_venue_id
DELETE FROM SUBSCRIPTION WHERE venue_id = @.new_venue_id
DELETE FROM ADMIN WHERE venue_id = @.new_venue_id
DELETE FROM VENUE WHERE venue_id = @.new_venue_id
DELETE FROM CUSTOMER WHERE customer_id = @.new_customer_id

END

thanks

Counting items and returning values

I have a table of product orders. It contains a row for "platform" and I
need to return how many times each platform is listed in the DB

Example data for platform could be:
XBOX
XBOX
XBOX
PLAYSTATION
PLAYSTATION
GAMECUBE
PLAYSTATION

I'd like the data to be returned as

XBOX - 3
PLAYSTATION - 3
GAMECUBE - 1

How would I go about doing this please?if the name of the column is "name":
SELECT name, COUNT(name)
GROUP BY name

HTH,
-Cliff

"Andrew Banks" <banksy@.nojunkblueyonder.co.uk> wrote in message
news:ks%ac.351$lN4.6788392@.news-text.cableinet.net...
> I have a table of product orders. It contains a row for "platform" and I
> need to return how many times each platform is listed in the DB
> Example data for platform could be:
> XBOX
> XBOX
> XBOX
> PLAYSTATION
> PLAYSTATION
> GAMECUBE
> PLAYSTATION
> I'd like the data to be returned as
> XBOX - 3
> PLAYSTATION - 3
> GAMECUBE - 1
> How would I go about doing this please?