Check if exist

Hi guys help please..is there a function in MS SQL that check if a particular value exist in a row and would return a boolean value base from what found, Return True if it found something and False if it does not found one. I've try the EXISTS function but I cant get the rigth syntax..Any help will be greatly appreciated!

OR Maybe you can help me directly with my problem. I want to check first in my Table 1 with 3 columns if value X exists in column 1 and if X exists UPDATE that column with value Y and if value X does not exists INSERT something in the Table 1. Any suggestion or Comments will be greatly appreciated!Hi

Post what you've got for your exists syntax. It should merely require some tweaking.|||Here it is.
EXISTS(select Sales_Date from CFREE_Sales where Sales_Date = '8/31/2007 12:00:00 AM')|||Try:
IF EXISTS(select Sales_Date from CFREE_Sales where Sales_Date = '20070831') BEGIN
PRINT 'It exists'
END
ELSE BEGIN
PRINT 'It does not exist'
END What is the result|||Yah...Thats what I need..Thanks a lot!|||An alternative to if exists (select * from #t1 where c1='x') begin
update #t1 set c2=c2+100 where c1='x'
end
else begin
insert into #t1 values ('x',100)
endisupdate #t1 set c2=c2+100 where c1='x'
if @.@.rowcount=0 begin
insert into #t1 values ('x',100)
end|||update #t1 set c2=c2+100 where c1='x'
if @.@.rowcount=0 begin
insert into #t1 values ('x',100)
endbingo! :beer:

Check for table and return true or false

How can I write a stored procedure to return if a table exist or not?

I put:

CREATE procedure sp_BA_ReportExist

(
@.ISYES VARCHAR (10),
@.ISNO VARCHAR (10)
)

AS

DECLARE @.SQL varchar(8000)
SET @.SQL = " if object_id('BA_REPORT_MASTER') is not null RETURN "+@.ISYES+" ELSE RETURN "+@.ISNO+" "

EXEC(@.SQL)
GO

I ran it with: sp_BA_ReportExist '1','0'

but I get:

Server: Msg 178, Level 15, State 1, Line 1
A RETURN statement with a return value cannot be used in this context.
Server: Msg 178, Level 15, State 1, Line 1
A RETURN statement with a return value cannot be used in this context.

How can I make this work?

Thanks!

KenFigured it out... this works:

CREATE procedure sp_BA_ReportExist

(
@.ISYES VARCHAR (10),
@.ISNO VARCHAR (10)
)

AS

DECLARE @.SQL varchar(8000)
SET @.SQL = " if object_id('BA_REPORT_MASTER') is not null PRINT "+@.ISYES+" ELSE PRINT "+@.ISNO+" "

EXEC(@.SQL)
GO

As always I find the answer right after I post!|||you could also use:

declare @.TableName sysname
set @.TableNAme = 'sysobjects'
if OBJECTPROPERTY(OBJECT_ID(@.TableName),'IsTable') = 1
print "+@.ISYES+"
else
print "+@.ISNO+"|||I have another problem now...

How do I get the return value?|||do you want it as a result set, output parameter or as a numeric valued returned by the "RETURN" statement?|||A resultset will work.

Basically I just need to know if the table exists so my application can set some values. Ic na't figure out how to get the value back into the application.

Thanks so much for any light you can shed on this!

Ken|||try:

create procedure sp_BA_ReportExist(
@.ISYES VARCHAR (10)
, @.ISNO VARCHAR (10))
AS
if (object_id('BA_REPORT_MASTER') is not null)
select @.ISYES as Answer
else
select @.ISNO as Answer

return 0
GO

exec sp_BA_ReportExist 'Yes', 'No'|||Too Cool! Thank you so much!

I was kinda close, but didn't have it quite right!

Thanks for your help!

Ken|||or:
create procedure sp_TableExists(
@.TableName sysname
, @.ISYES VARCHAR (10) = 'Yes'
, @.ISNO VARCHAR (10) = 'No')
AS
select case OBJECTPROPERTY(OBJECT_ID(@.TableName),'IsTable') when 1 then @.ISYES else @.ISNO end as Answer
return 0
GO

exec sp_TableExists 'sysobjects','Yes', 'No'

or just

exec sp_TableExists 'sysobjects'

Charts

I have a Custom Dundas Charting library that I utilize normally. I'm having
troubles getting my chart object (and tried to return and image) to render
within my reports. I'm confident I have the custom assemblie referenced. I
don't want to use the chart field from the toolbox, I want to set it to what
my assemblie returns. Anyway to get around this? I'm aware of the custom
data extensions, although, I wanted to just have my existing assemblie which
returns charts do it instead. so all my reports that need graphs can just
reference the assemblie and graph that is needed. Anyway to accomplish this?
I'm getting data type reference errors etc.
ClintPlease try this:
* Add an image to your report
* Set the image type to Database
* Set the image mimetype to e.g. image/png
* For the image value use an expression like
=MyCustomAssembly.GenerateChart()
Note: your custom assembly call has to return the image as byte[].
Here is a code snippet which should convert the Dundas chart output into a
byte array:
public static byte[] GenerateChart()
{
// chart rendering code
...
// save chart image to byte[]
System.IO.MemoryStream renderedImage = new MemoryStream();
dundasChart.ImageType = ChartImageType.Png;
dundasChart.Save(renderedImage);
renderedImage.Position = 0;
return renderedImage.ToArray();
}
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Clint Jennings" <cjennings@.fusionAlliance.com> wrote in message
news:%237PwafckEHA.896@.TK2MSFTNGP12.phx.gbl...
> I have a Custom Dundas Charting library that I utilize normally. I'm
having
> troubles getting my chart object (and tried to return and image) to render
> within my reports. I'm confident I have the custom assemblie referenced.
I
> don't want to use the chart field from the toolbox, I want to set it to
what
> my assemblie returns. Anyway to get around this? I'm aware of the custom
> data extensions, although, I wanted to just have my existing assemblie
which
> returns charts do it instead. so all my reports that need graphs can just
> reference the assemblie and graph that is needed. Anyway to accomplish
this?
> I'm getting data type reference errors etc.
> Clint
>|||Thanks Robert. Created a public function in the library to return a byte
stream.
"Robert Bruckner [MSFT]" wrote:
> Please try this:
> * Add an image to your report
> * Set the image type to Database
> * Set the image mimetype to e.g. image/png
> * For the image value use an expression like
> =MyCustomAssembly.GenerateChart()
> Note: your custom assembly call has to return the image as byte[].
> Here is a code snippet which should convert the Dundas chart output into a
> byte array:
> public static byte[] GenerateChart()
> {
> // chart rendering code
> ...
> // save chart image to byte[]
> System.IO.MemoryStream renderedImage = new MemoryStream();
> dundasChart.ImageType = ChartImageType.Png;
> dundasChart.Save(renderedImage);
> renderedImage.Position = 0;
> return renderedImage.ToArray();
> }
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Clint Jennings" <cjennings@.fusionAlliance.com> wrote in message
> news:%237PwafckEHA.896@.TK2MSFTNGP12.phx.gbl...
> > I have a Custom Dundas Charting library that I utilize normally. I'm
> having
> > troubles getting my chart object (and tried to return and image) to render
> > within my reports. I'm confident I have the custom assemblie referenced.
> I
> > don't want to use the chart field from the toolbox, I want to set it to
> what
> > my assemblie returns. Anyway to get around this? I'm aware of the custom
> > data extensions, although, I wanted to just have my existing assemblie
> which
> > returns charts do it instead. so all my reports that need graphs can just
> > reference the assemblie and graph that is needed. Anyway to accomplish
> this?
> > I'm getting data type reference errors etc.
> >
> > Clint
> >
> >
>
>

Chart Filters

HI,
Does anybody know how to write a chart filter that will return only the last row in the record set and conversely return all rows except for the last in the data set (for a separate chart)
My Report consists of a dataset that has the total in the last row. I want to create a chart with only the total row and another chart on the same page using the same data set but excluding the total row.
Cheers
KevinIf there is anything special about the row it would be straight forward as a
chart filter and a table filter to separate the rows. If the only
distinguishing feature of the row is that it is the last row then I can not
think of a way in RS 2000 to do this. We are considering allowing
aggregates of aggregates, which would allow you to filter on the max
rownumber.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Kevin Wilson" <KevinWilson@.discussions.microsoft.com> wrote in message
news:A23DB1DA-C4EC-4789-A46A-C236B070F5A4@.microsoft.com...
> HI,
> Does anybody know how to write a chart filter that will return only the
last row in the record set and conversely return all rows except for the
last in the data set (for a separate chart)
> My Report consists of a dataset that has the total in the last row. I want
to create a chart with only the total row and another chart on the same
page using the same data set but excluding the total row.
> Cheers
> Kevin|||Jason,
There is nothing special about the row except that it's the last. However I've managed to created a chart filter that returns the last row by using the Bottom N operator.
expression operator value
=Fields!RH0_Product.Value BottomN =1
However do you know how to express a filter that defines where the rows is NOT = to the BottomN 1
If I can get this then I've solved my problem...
Much appreciated.
Kevin
"Jason Carlson [MSFT]" wrote:
> If there is anything special about the row it would be straight forward as a
> chart filter and a table filter to separate the rows. If the only
> distinguishing feature of the row is that it is the last row then I can not
> think of a way in RS 2000 to do this. We are considering allowing
> aggregates of aggregates, which would allow you to filter on the max
> rownumber.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Kevin Wilson" <KevinWilson@.discussions.microsoft.com> wrote in message
> news:A23DB1DA-C4EC-4789-A46A-C236B070F5A4@.microsoft.com...
> > HI,
> > Does anybody know how to write a chart filter that will return only the
> last row in the record set and conversely return all rows except for the
> last in the data set (for a separate chart)
> >
> > My Report consists of a dataset that has the total in the last row. I want
> to create a chart with only the total row and another chart on the same
> page using the same data set but excluding the total row.
> >
> > Cheers
> > Kevin
>
>|||Please see my response to your "NOT BottomN" thread started on 06/29.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Kevin Wilson" <KevinWilson@.discussions.microsoft.com> wrote in message
news:A6A1CBD8-F987-472C-B507-E0AA21FD3A4C@.microsoft.com...
> Jason,
> There is nothing special about the row except that it's the last. However
I've managed to created a chart filter that returns the last row by using
the Bottom N operator.
> expression operator value
> =Fields!RH0_Product.Value BottomN =1
> However do you know how to express a filter that defines where the rows
is NOT = to the BottomN 1
> If I can get this then I've solved my problem...
> Much appreciated.
> Kevin
>
> "Jason Carlson [MSFT]" wrote:
> > If there is anything special about the row it would be straight forward
as a
> > chart filter and a table filter to separate the rows. If the only
> > distinguishing feature of the row is that it is the last row then I can
not
> > think of a way in RS 2000 to do this. We are considering allowing
> > aggregates of aggregates, which would allow you to filter on the max
> > rownumber.
> >
> > --
> >
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> >
> >
> > "Kevin Wilson" <KevinWilson@.discussions.microsoft.com> wrote in message
> > news:A23DB1DA-C4EC-4789-A46A-C236B070F5A4@.microsoft.com...
> > > HI,
> > > Does anybody know how to write a chart filter that will return only
the
> > last row in the record set and conversely return all rows except for the
> > last in the data set (for a separate chart)
> > >
> > > My Report consists of a dataset that has the total in the last row. I
want
> > to create a chart with only the total row and another chart on the same
> > page using the same data set but excluding the total row.
> > >
> > > Cheers
> > > Kevin
> >
> >
> >

Chart "Top N"

With 2k5 I am trying to make the "Top N" operator, used in a chart "category," return only N categories. But it almost always returns more than N categories. Can someone explain the nuances of the Top N operator, so I can get past this?

Is N fixed number, If yes then you can try filtering on rownumber.

One more option is to create a different dataset and then use "select top 10" syntax in query itself.

HTH1

Priyank

|||

Whether N is fixed or not, "rownumber" does not correlate to chart "category" grouping. I want the top 10 categories, not the top ten rows.

I don't want to use "select top N" in my data set, because it would require refactoring my query to use a "group by" expression. What I want is for the grouping to be handled by the chart.

So this brings me back to the beginning. Can someone explain the highly erratic behavior of the "top N" operator? It sometimes returns more than N, sometimes less, and it often screws up the sort order. Is this something fixed in a SSRS patch?

|||

Ok, I figured it out. First of all, assuming the perspective of "order ascending," "Top N" actually means "Bottom N," and "Bottom N" actually means "Top N." Go figure. Also, the "Top N" operator, at least when used with a chart "category" filter, does the following:

1) Uses the category field expression to select the first row of each category

2) Sorts the selection of "first rows" that came from the category, based on the field expression that was given to the "Top N" operator itself

3) Performs a dense_rank() function on that output

4) Selects the "Top N" of the dense rank

5) Afterward, the "Sorting" spec of the category is applied

This is why I sometimes get more than "N" categories returned.

charindex question

I have a field containing a string with '/' in it multiple times.

How can I return the charindex of the last occurance of '/' in the
string?

Regards,
Ciarndeclare @.s varchar(10)
set @.s='as/gf/af/h'
select len(@.s)-charindex('/',reverse(@.s))+1

Madhivanan

CHARINDEX doesn't work

My charindex seems to always return 0 no matter what. I tried it on
different SQL servers and I always get 0 no matter what. Here was a
test script I tried and still got zero.
DECLARE @.myvar as varchar(25)
DECLARE @.myvar2 as varchar(25)
SET @.myvar = 'hello'
SET @.myvar2 = 'll'
PRINT CHARINDEX( @.myvar, @.myvar2 )
PRINT CHARINDEX( 'test', 's' )
Both print zero... can anyone tell me what i'm doing wrong?Never mind.. I had the parameters mixed up. What a dumb mistake.|||try this, you reversed them!
DECLARE @.myvar as varchar(25)
DECLARE @.myvar2 as varchar(25)
SET @.myvar = 'hello'
SET @.myvar2 = 'll'
PRINT CHARINDEX( @.myvar2, @.myvar )
PRINT CHARINDEX( 's','test' )
http://sqlservercode.blogspot.com/

CHARINDEX

I find that CHARINDEX does not seem to return the position of a matching
phrase when the token being searched contains punctuation after the phrase.
In other words CHARINDEX('Birthday', 'Hope you have a happy Birthday!', 0)
does NOT return the position of Birthday in the phrase!!
Please can anyone advise me on this.
Thanks
Bryan
I get 23 from both SQL 2000 SP4, and SQL 2005 SP2.
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005