How to increment field after selecting it

I have FeaturedClassifiedsCount field, which I would like to update each time record is selected. How do I do it in stored procedure on SQL 2005?

This is my existing code:

alterPROCEDURE dbo.SP_FeaturedClassifieds

@.PageIndexINT,

@.NumRowsINT,

@.FeaturedClassifiedsCountINTOUTPUT

AS

BEGIN

select @.FeaturedClassifiedsCount=(SelectCount(*)From classifieds_AdsWhere AdStatus=100And Adlevel=50)

Declare @.startRowIndexINT;

Set @.startRowIndex=(@.PageIndex* @.NumRows)+ 1;

With FeaturedClassifiedsas(

SelectROW_NUMBER()OVER(OrderBy FeaturedDisplayedCount*(1-(Weight-1)/100)ASC)as

Row, Id, PreviewImageId, Title, DateCreated, FeaturedDisplayedCount

From

classifieds_Ads

Where

AdStatus=100And AdLevel=50

)

Select

Id, PreviewImageId, Title, DateCreated, FeaturedDisplayedCount

From

FeaturedClassifieds

Where

Rowbetween

@.startRowIndexAnd @.startRowIndex+@.NumRows-1

END

Hello rfurdzik,

Am I correct that you want to update the counter in the table Classified_Ads? Try to add an update statement before the last select statement :

UPDATE Classified_Ads SET FeaturedDisplayedCount = FeaturedDisplayedCount + 1

FROM FeaturedClassifieds

WHERE FeaturedClassifieds.Id = Classified_Ads.Id

AND Row between (@.startRowIndex AND @.startRowIndex + @.NumRows - 1)

How to improve performance of the report's display while updating the table?

Hello, I need help to improve the performance of select statments in my repo
rt. My report is selecting (lots of data) from a attendance table, while a t
rigger is inserting more than 4000 rows of data into the table.
Can I use nolock table hints in my report? How can I use it?
For example,
select * from table a, table b, table c, table d
Should I write like this for the nolock when I want only the table b use nol
ock table hints:-
select * from table a, table b with (nolock), table c, table dAriel
Are you sure that these tables have no JOIN's condition?
This is a mistake which most users make when using the old-style JOIN syntax
is not specifying the join condition. The result set of your query is
Cartesian Product of four tables. it would generate every possible
combination of rows between them.
"@.Ariel" <anonymous@.discussions.microsoft.com> wrote in message
news:CBE065F9-8781-4727-BE82-2ECED244C30A@.microsoft.com...
> Hello, I need help to improve the performance of select statments in my
report. My report is selecting (lots of data) from a attendance table, while
a trigger is inserting more than 4000 rows of data into the table.
> Can I use nolock table hints in my report? How can I use it?
> For example,
> select * from table a, table b, table c, table d
> Should I write like this for the nolock when I want only the table b use
nolock table hints:-
> select * from table a, table b with (nolock), table c, table d|||As Uri says, make sure you have the correct join syntax. (The example you
posted is incorrect.) But the direct answer to your question is yes
select * From table with (nolock)
You WILL see data in the process of being changed in a transaction for which
the transaction has NOT yet committed ( dirty data)... Also look up in books
on line (readpast).
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.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
"@.Ariel" <anonymous@.discussions.microsoft.com> wrote in message
news:CBE065F9-8781-4727-BE82-2ECED244C30A@.microsoft.com...
> Hello, I need help to improve the performance of select statments in my
report. My report is selecting (lots of data) from a attendance table, while
a trigger is inserting more than 4000 rows of data into the table.
> Can I use nolock table hints in my report? How can I use it?
> For example,
> select * from table a, table b, table c, table d
> Should I write like this for the nolock when I want only the table b use
nolock table hints:-
> select * from table a, table b with (nolock), table c, table d|||Thanks Uri and Wayne.
Yes, my table got join statements within.
select * from table a, table b with (nolock), table c and table d
where a.id = b.id and
b.id = c.id and
c.id = d.id;
// the b table I assume is the attendance table.
uri, did you mean that my result will be wrong after I use the nolock syntax
?
Or you mean it will be wrong in terms of some of the data it linked will be
deleted or updated?
Thanks Again|||Uri was saying that your select has an error in it, but it does not. ( He
probably was just looking quickly.) Each one of the tables is in the where
clause, but you are using an OLD style syntax which is VERY prone to the
kinds of errors that Uri mentioned... Why don't you spend some time getting
used to and using the ANSI style join syntax - you'll be better off ... IE
select * from a inner join b with (nolock) on a.id = b.id
inner join c on b.id = c.id
inner join d on c.id = d.id;
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.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
"@.Ariel" <anonymous@.discussions.microsoft.com> wrote in message
news:8647D87B-C082-485D-8BFF-1B822C33FA8D@.microsoft.com...
> Thanks Uri and Wayne.
> Yes, my table got join statements within.
> select * from table a, table b with (nolock), table c and table d
> where a.id = b.id and
> b.id = c.id and
> c.id = d.id;
> // the b table I assume is the attendance table.
> uri, did you mean that my result will be wrong after I use the nolock
syntax?
> Or you mean it will be wrong in terms of some of the data it linked will
be deleted or updated?
> Thanks Again
>

How to identify all parameter values selected

I have the SQL query. If the user is selecting all the vendor Numbers available in the vendor number parameter drop down then, I will not include the vendor Number condition in the where portion of the sql query. For that I want to know whether the user has selected all the values available in the drop down. How to identify this?

Have a Default Value in your DropDown, for instance: SELECT ALL with the value of "" VALUE=""

which is basically null. In your where cause use an ISNULL

For instance:

@.COMPANY_ID INT = NULL

Select ID, [NAME], COMPANY_ID FROM EMPLOYEES WHERE COMPANY_ID = ISNULL(@.COMPANY_ID, COMPANY_ID)

Hope this helps.