Anybody knows the syntax? Thanks

declare @.lastmonth char(7)
select comment from tbldescription
where themonth = code.getlastmonth() <-- syntax wrong
getlastmonth() is a function returning last month in form like '2006.02'
Anybody knows the right syntax, please email me back. Thanks!It is not completely clear what are you trying to get.
1. If you want to use you custom code function, then
just use general sql pane (command type=text) and as you data source use:
="select comment from tbldescription where themonth ='"+code.getlastmonth()
+"'"
2. use just a plain sql query (command type=table direct)
select comment from tbldescription where YEAR(yourdatetime_field) =YEAR(GETDATE()) and MONTH(yourdatetime_field) = MONTH (GETDATE()) -1
Hope it helps
Oleg Yevteyev,
San Diego, CA
It is OK to contact me with a contracting opportunity.
"myfirstname"001atgmaildotcom.
Replace "myfirstname" with Oleg.
--
"Henry Chen" <HenryChen@.discussions.microsoft.com> wrote in message
news:5A98BD40-5FB4-4383-B307-4D38D7507690@.microsoft.com...
> declare @.lastmonth char(7)
> select comment from tbldescription
> where themonth = code.getlastmonth() <-- syntax wrong
>
> getlastmonth() is a function returning last month in form like '2006.02'
> Anybody knows the right syntax, please email me back. Thanks!
>
>|||I am creating a data connection and the three line codes are in the dataset
panel, then I click on refresh tab, it generated an error, why? that is my
question.
"Henry Chen" wrote:
> declare @.lastmonth char(7)
> select comment from tbldescription
> where themonth = code.getlastmonth() <-- syntax wrong
>
> getlastmonth() is a function returning last month in form like '2006.02'
> Anybody knows the right syntax, please email me back. Thanks!
>
>|||Hi,
"Why?" That's because SQL server doesn't understand what you are trying to
write and which is not in the SQL SERVER scope. This is used inside the
report.(code.getlastmonth()) Instead use sqlserver commands to get the last
month in your format. Can take it from the last post.
Amarnath
"Henry Chen" wrote:
> I am creating a data connection and the three line codes are in the dataset
> panel, then I click on refresh tab, it generated an error, why? that is my
> question.
> "Henry Chen" wrote:
> > declare @.lastmonth char(7)
> >
> > select comment from tbldescription
> > where themonth = code.getlastmonth() <-- syntax wrong
> >
> >
> > getlastmonth() is a function returning last month in form like '2006.02'
> >
> > Anybody knows the right syntax, please email me back. Thanks!
> >
> >
> >|||the correct answer is
="select comment from tbldescription where themonth = '" +
code.getlastmonth() + "'"
in ONE LINE.
so you are right.
Thanks!
"Henry Chen" wrote:
> declare @.lastmonth char(7)
> select comment from tbldescription
> where themonth = code.getlastmonth() <-- syntax wrong
>
> getlastmonth() is a function returning last month in form like '2006.02'
> Anybody knows the right syntax, please email me back. Thanks!
>
>sql

Anybody knows the problem of this code?

My code is this:
shared function dataFisica( byVal data as Date ) as Date
If data='01/01/1900' Then
return ' '
Else
return data
End If
end function
And the reporting services do an error in line 1.
Try using double quotes instead of single quotes

function dataFisica( byVal data as Date ) as Date
If data="01/01/1900" Then
return ""
Else
return data
End If
end function

Any way to force a recompile on a statement within a User Defined Function?

Consider the following:
TableA (c1 int, c2 int, c3 int, c4 int, c5 varchar(80, etc...)
There is a clustered index on (c1, c3), nonclustered index on (c2)
The UDF contains a SELECT statement of the form:
select c1 from TableA where c2 = @.InputParm
TableA contains about 300000 rows and there are some values for column c2
that have a cardinality of over 30000. So obviously if this value gets
passed into the UDF, a Clustered Index Scan is probably best. On the other
hand if a parm gets passed for a value that has a few corresponding rows for
c2 then it is best that the nonclustered index on c2 be used.
The problem is that when the UDF is first invoked with a parm that has a
high number of corresponding rows for c2, the execution plan uses a
clustered index SCAN and all subsuquent calls use this same execution plan.
Ideally I would like to have this UDF defined to recompile each call so that
I get the best execution plan for each call.
If this were a Stored Proc - I would either define it "WITH RECOMPILE"
(doesn't appear to be valid for functions), or I would use dynamic SQL
(since Functions cannot access temp tables I cannot use this approach
either).
Any ideas would be appreciated.
I have a UDF in which I would like to force a recompile based on the value
of one of the parameters. It seems that when the UDF is first executed, if
it is passed a value that has a it performs
Within the UDF is a select from
"TJTODD" <tjtodd@.anonymous.com> wrote in message
news:O7J6bCLeEHA.712@.TK2MSFTNGP09.phx.gbl...
> Consider the following:
> TableA (c1 int, c2 int, c3 int, c4 int, c5 varchar(80, etc...)
> There is a clustered index on (c1, c3), nonclustered index on (c2)
> The UDF contains a SELECT statement of the form:
> select c1 from TableA where c2 = @.InputParm
> TableA contains about 300000 rows and there are some values for column c2
> that have a cardinality of over 30000. So obviously if this value gets
> passed into the UDF, a Clustered Index Scan is probably best. On the
other
> hand if a parm gets passed for a value that has a few corresponding rows
for
> c2 then it is best that the nonclustered index on c2 be used.
> The problem is that when the UDF is first invoked with a parm that has a
> high number of corresponding rows for c2, the execution plan uses a
> clustered index SCAN and all subsuquent calls use this same execution
plan.
> Ideally I would like to have this UDF defined to recompile each call so
that
> I get the best execution plan for each call.
> If this were a Stored Proc - I would either define it "WITH RECOMPILE"
> (doesn't appear to be valid for functions), or I would use dynamic SQL
> (since Functions cannot access temp tables I cannot use this approach
> either).
> Any ideas would be appreciated.
>
I don't think there's any way to get the function to recompile.
Here are your options:
1. Change the indexing scheme so a single plan is appropriate.
-The obvious candidate here is to add c1 to the nonclustered index on c2,
thus covering your query.
2. Examine the cardinality inside the stored function and switch between two
hinted queries.
3. Rewrite the function as a join and use dynamic SQL.
David
David
|||Can you wrap the query in the UDF inside sp_executesql? If so, do not use
any parameters to sp_executesql, put the entire query in the query string>
that way each different value for c2 will have a different plan.
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
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:%23uah$MMeEHA.3016@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> "TJTODD" <tjtodd@.anonymous.com> wrote in message
> news:O7J6bCLeEHA.712@.TK2MSFTNGP09.phx.gbl...
c2
> other
> for
> plan.
> that
>
> I don't think there's any way to get the function to recompile.
> Here are your options:
> 1. Change the indexing scheme so a single plan is appropriate.
> -The obvious candidate here is to add c1 to the nonclustered index on
c2,
> thus covering your query.
> 2. Examine the cardinality inside the stored function and switch between
two
> hinted queries.
> 3. Rewrite the function as a join and use dynamic SQL.
> David
>
> David
>

Any way to force a recompile on a statement within a User Defined Function?

Consider the following:
TableA (c1 int, c2 int, c3 int, c4 int, c5 varchar(80, etc...)
There is a clustered index on (c1, c3), nonclustered index on (c2)
The UDF contains a SELECT statement of the form:
select c1 from TableA where c2 = @.InputParm
TableA contains about 300000 rows and there are some values for column c2
that have a cardinality of over 30000. So obviously if this value gets
passed into the UDF, a Clustered Index Scan is probably best. On the other
hand if a parm gets passed for a value that has a few corresponding rows for
c2 then it is best that the nonclustered index on c2 be used.
The problem is that when the UDF is first invoked with a parm that has a
high number of corresponding rows for c2, the execution plan uses a
clustered index SCAN and all subsuquent calls use this same execution plan.
Ideally I would like to have this UDF defined to recompile each call so that
I get the best execution plan for each call.
If this were a Stored Proc - I would either define it "WITH RECOMPILE"
(doesn't appear to be valid for functions), or I would use dynamic SQL
(since Functions cannot access temp tables I cannot use this approach
either).
Any ideas would be appreciated.
I have a UDF in which I would like to force a recompile based on the value
of one of the parameters. It seems that when the UDF is first executed, if
it is passed a value that has a it performs
Within the UDF is a select from"TJTODD" <tjtodd@.anonymous.com> wrote in message
news:O7J6bCLeEHA.712@.TK2MSFTNGP09.phx.gbl...
> Consider the following:
> TableA (c1 int, c2 int, c3 int, c4 int, c5 varchar(80, etc...)
> There is a clustered index on (c1, c3), nonclustered index on (c2)
> The UDF contains a SELECT statement of the form:
> select c1 from TableA where c2 = @.InputParm
> TableA contains about 300000 rows and there are some values for column c2
> that have a cardinality of over 30000. So obviously if this value gets
> passed into the UDF, a Clustered Index Scan is probably best. On the
other
> hand if a parm gets passed for a value that has a few corresponding rows
for
> c2 then it is best that the nonclustered index on c2 be used.
> The problem is that when the UDF is first invoked with a parm that has a
> high number of corresponding rows for c2, the execution plan uses a
> clustered index SCAN and all subsuquent calls use this same execution
plan.
> Ideally I would like to have this UDF defined to recompile each call so
that
> I get the best execution plan for each call.
> If this were a Stored Proc - I would either define it "WITH RECOMPILE"
> (doesn't appear to be valid for functions), or I would use dynamic SQL
> (since Functions cannot access temp tables I cannot use this approach
> either).
> Any ideas would be appreciated.
>
I don't think there's any way to get the function to recompile.
Here are your options:
1. Change the indexing scheme so a single plan is appropriate.
-The obvious candidate here is to add c1 to the nonclustered index on c2,
thus covering your query.
2. Examine the cardinality inside the stored function and switch between two
hinted queries.
3. Rewrite the function as a join and use dynamic SQL.
David
David|||Can you wrap the query in the UDF inside sp_executesql? If so, do not use
any parameters to sp_executesql, put the entire query in the query string>
that way each different value for c2 will have a different plan.
--
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
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:%23uah$MMeEHA.3016@.tk2msftngp13.phx.gbl...
> "TJTODD" <tjtodd@.anonymous.com> wrote in message
> news:O7J6bCLeEHA.712@.TK2MSFTNGP09.phx.gbl...
> > Consider the following:
> >
> > TableA (c1 int, c2 int, c3 int, c4 int, c5 varchar(80, etc...)
> > There is a clustered index on (c1, c3), nonclustered index on (c2)
> >
> > The UDF contains a SELECT statement of the form:
> > select c1 from TableA where c2 = @.InputParm
> >
> > TableA contains about 300000 rows and there are some values for column
c2
> > that have a cardinality of over 30000. So obviously if this value gets
> > passed into the UDF, a Clustered Index Scan is probably best. On the
> other
> > hand if a parm gets passed for a value that has a few corresponding rows
> for
> > c2 then it is best that the nonclustered index on c2 be used.
> >
> > The problem is that when the UDF is first invoked with a parm that has a
> > high number of corresponding rows for c2, the execution plan uses a
> > clustered index SCAN and all subsuquent calls use this same execution
> plan.
> > Ideally I would like to have this UDF defined to recompile each call so
> that
> > I get the best execution plan for each call.
> >
> > If this were a Stored Proc - I would either define it "WITH RECOMPILE"
> > (doesn't appear to be valid for functions), or I would use dynamic SQL
> > (since Functions cannot access temp tables I cannot use this approach
> > either).
> >
> > Any ideas would be appreciated.
> >
>
> I don't think there's any way to get the function to recompile.
> Here are your options:
> 1. Change the indexing scheme so a single plan is appropriate.
> -The obvious candidate here is to add c1 to the nonclustered index on
c2,
> thus covering your query.
> 2. Examine the cardinality inside the stored function and switch between
two
> hinted queries.
> 3. Rewrite the function as a join and use dynamic SQL.
> David
>
> David
>

Any way to force a recompile on a statement within a User Defined Function?

Consider the following:
TableA (c1 int, c2 int, c3 int, c4 int, c5 varchar(80, etc...)
There is a clustered index on (c1, c3), nonclustered index on (c2)
The UDF contains a SELECT statement of the form:
select c1 from TableA where c2 = @.InputParm
TableA contains about 300000 rows and there are some values for column c2
that have a cardinality of over 30000. So obviously if this value gets
passed into the UDF, a Clustered Index Scan is probably best. On the other
hand if a parm gets passed for a value that has a few corresponding rows for
c2 then it is best that the nonclustered index on c2 be used.
The problem is that when the UDF is first invoked with a parm that has a
high number of corresponding rows for c2, the execution plan uses a
clustered index SCAN and all subsuquent calls use this same execution plan.
Ideally I would like to have this UDF defined to recompile each call so that
I get the best execution plan for each call.
If this were a Stored Proc - I would either define it "WITH RECOMPILE"
(doesn't appear to be valid for functions), or I would use dynamic SQL
(since Functions cannot access temp tables I cannot use this approach
either).
Any ideas would be appreciated.
I have a UDF in which I would like to force a recompile based on the value
of one of the parameters. It seems that when the UDF is first executed, if
it is passed a value that has a it performs
Within the UDF is a select from"TJTODD" <tjtodd@.anonymous.com> wrote in message
news:O7J6bCLeEHA.712@.TK2MSFTNGP09.phx.gbl...
> Consider the following:
> TableA (c1 int, c2 int, c3 int, c4 int, c5 varchar(80, etc...)
> There is a clustered index on (c1, c3), nonclustered index on (c2)
> The UDF contains a SELECT statement of the form:
> select c1 from TableA where c2 = @.InputParm
> TableA contains about 300000 rows and there are some values for column c2
> that have a cardinality of over 30000. So obviously if this value gets
> passed into the UDF, a Clustered Index Scan is probably best. On the
other
> hand if a parm gets passed for a value that has a few corresponding rows
for
> c2 then it is best that the nonclustered index on c2 be used.
> The problem is that when the UDF is first invoked with a parm that has a
> high number of corresponding rows for c2, the execution plan uses a
> clustered index SCAN and all subsuquent calls use this same execution
plan.
> Ideally I would like to have this UDF defined to recompile each call so
that
> I get the best execution plan for each call.
> If this were a Stored Proc - I would either define it "WITH RECOMPILE"
> (doesn't appear to be valid for functions), or I would use dynamic SQL
> (since Functions cannot access temp tables I cannot use this approach
> either).
> Any ideas would be appreciated.
>
I don't think there's any way to get the function to recompile.
Here are your options:
1. Change the indexing scheme so a single plan is appropriate.
-The obvious candidate here is to add c1 to the nonclustered index on c2,
thus covering your query.
2. Examine the cardinality inside the stored function and switch between two
hinted queries.
3. Rewrite the function as a join and use dynamic SQL.
David
David|||Can you wrap the query in the UDF inside sp_executesql? If so, do not use
any parameters to sp_executesql, put the entire query in the query string>
that way each different value for c2 will have a different plan.
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
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:%23uah$MMeEHA.3016@.tk2msftngp13.phx.gbl...
> "TJTODD" <tjtodd@.anonymous.com> wrote in message
> news:O7J6bCLeEHA.712@.TK2MSFTNGP09.phx.gbl...
c2[vbcol=seagreen]
> other
> for
> plan.
> that
>
> I don't think there's any way to get the function to recompile.
> Here are your options:
> 1. Change the indexing scheme so a single plan is appropriate.
> -The obvious candidate here is to add c1 to the nonclustered index on
c2,
> thus covering your query.
> 2. Examine the cardinality inside the stored function and switch between
two
> hinted queries.
> 3. Rewrite the function as a join and use dynamic SQL.
> David
>
> David
>

Any SQL equivalent of InStrRev()?

In VB, there's a string function called InStrRev() which returns the
position of an occurrence of one string within another, from the end of
string.
I have a column containing data such as "/uploads/encrypted/design1.doc". I
want to be able to extract the pure filename from this column (e.g. returns
"design1.doc" from the above example).
In VB, I could use InStrRev(columnData, "/") to locate the last slash '/'
and then extract the rest of the string from that position.
Is there a similar way to do this in SQL script?
Thank you.DId you try something using CHARINDEX(REVERSE())?
SELECT CHARINDEX('x', 'foobarxgoo')
SELECT CHARINDEX('x', REVERSE('foobarxgoo'))
"Bob" <noreply@.nospam.com> wrote in message
news:Oy1$J083FHA.4076@.TK2MSFTNGP15.phx.gbl...
> In VB, there's a string function called InStrRev() which returns the
> position of an occurrence of one string within another, from the end of
> string.
> I have a column containing data such as "/uploads/encrypted/design1.doc".
> I want to be able to extract the pure filename from this column (e.g.
> returns "design1.doc" from the above example).
> In VB, I could use InStrRev(columnData, "/") to locate the last slash '/'
> and then extract the rest of the string from that position.
> Is there a similar way to do this in SQL script?
> Thank you.
>|||Hi Aaron,

> SELECT CHARINDEX('x', 'foobarxgoo')
returns 7

> SELECT CHARINDEX('x', REVERSE('foobarxgoo'))
returns 4
The only way I can find this useful is if I REVERSE the string (i.e.
"/uploads/encrypted/design1.doc" to "cod.1ngised/detpyrcne/sdaolpu/"), then
find the first instance of a slash "/". Truncate the rest of the string.
That would get me "cod.1ngised". And finally, REVERSE again to get
"design1.doc".
But that sounds like a lot of calls and could slow down performance!
What do you think?
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23CZHh883FHA.2196@.tk2msftngp13.phx.gbl...
> DId you try something using CHARINDEX(REVERSE())?
> SELECT CHARINDEX('x', 'foobarxgoo')
> SELECT CHARINDEX('x', REVERSE('foobarxgoo'))
>
> "Bob" <noreply@.nospam.com> wrote in message
> news:Oy1$J083FHA.4076@.TK2MSFTNGP15.phx.gbl...
>|||You can directly use the expression:
RIGHT( @.s, CHARINDEX( '/', REVERSE( @.s ) ) - 1 )
Obviously, any nested string manipulation functions done over a large
dataset is going to impact performance ( VB is no different either ). In
many cases it might be negligible. If it is a crucial issue, rather than
putting the entire path in a single column, consider using a separate column
for the file name.
Anith

any news about hit-highlighting?

Hi there,
Any news about hit-highlight in sql search?
Anyone has a function/sp that can help with outputing better
formatted-results to the user?
Thanks!
this functionality does not ship in SQL FTS.
What you have to do is store the document in the file system as well, and
then in a web page create a hyperlink to this document so that when the user
clicks on the hyperlink the virtual file path will be posted to webhits.dll
which is what provides the hit highlighting functionality for Index
Server/Indexing Services.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Guy Brom" <guy_brom@.yahoo.com> wrote in message
news:%23oNffyVdEHA.3212@.TK2MSFTNGP12.phx.gbl...
> Hi there,
> Any news about hit-highlight in sql search?
> Anyone has a function/sp that can help with outputing better
> formatted-results to the user?
> Thanks!
>

Any function?

Hi, any function wich returns me if contains some string inside another
string for using on SP?
InStr?
ThanksCHARINDEX ,PATINDEX'
"Paulo" <prbspfc@.uol.com.br> wrote in message
news:u2BJWIUeIHA.4844@.TK2MSFTNGP04.phx.gbl...
> Hi, any function wich returns me if contains some string inside another
> string for using on SP?
> InStr?
> Thanks
>|||if CHARINDEX('5.0', SELECT @.@.VERSION) = 0 then 'WIN 2000
if CHARINDEX('5.1', SELECT @.@.VERSION) = 0 then 'WIN XP
if CHARINDEX('5.2', SELECT @.@.VERSION) = 0 then 'WIN2K3
Should work?
Thanks man!
"Uri Dimant" <urid@.iscar.co.il> escreveu na mensagem
news:uDZFoKUeIHA.5348@.TK2MSFTNGP03.phx.gbl...
> CHARINDEX ,PATINDEX'
> "Paulo" <prbspfc@.uol.com.br> wrote in message
> news:u2BJWIUeIHA.4844@.TK2MSFTNGP04.phx.gbl...
>> Hi, any function wich returns me if contains some string inside another
>> string for using on SP?
>> InStr?
>> Thanks
>>
>|||Paulo
CREATE TABLE #tmp (c VARCHAR(1000))
GO
INSERT INTO #tmp EXEC('SELECT @.@.VERSION')
SELECT CASE WHEN CHARINDEX('5.0', c) > 0 then 'WIN 2000'
WHEN CHARINDEX('5.1', c) > 0 then 'WIN XP'
WHEN CHARINDEX('5.2', c) > 0 then 'WIN2K3' END
FROM #tmp
"Paulo" <prbspfc@.uol.com.br> wrote in message
news:ucf0TSUeIHA.288@.TK2MSFTNGP02.phx.gbl...
> if CHARINDEX('5.0', SELECT @.@.VERSION) = 0 then 'WIN 2000
> if CHARINDEX('5.1', SELECT @.@.VERSION) = 0 then 'WIN XP
> if CHARINDEX('5.2', SELECT @.@.VERSION) = 0 then 'WIN2K3
> Should work?
> Thanks man!
> "Uri Dimant" <urid@.iscar.co.il> escreveu na mensagem
> news:uDZFoKUeIHA.5348@.TK2MSFTNGP03.phx.gbl...
>> CHARINDEX ,PATINDEX'
>> "Paulo" <prbspfc@.uol.com.br> wrote in message
>> news:u2BJWIUeIHA.4844@.TK2MSFTNGP04.phx.gbl...
>> Hi, any function wich returns me if contains some string inside another
>> string for using on SP?
>> InStr?
>> Thanks
>>
>>
>|||Thanks man... very good !
"Uri Dimant" <urid@.iscar.co.il> escreveu na mensagem
news:uqwDEZUeIHA.5208@.TK2MSFTNGP04.phx.gbl...
> Paulo
> CREATE TABLE #tmp (c VARCHAR(1000))
> GO
> INSERT INTO #tmp EXEC('SELECT @.@.VERSION')
> SELECT CASE WHEN CHARINDEX('5.0', c) > 0 then 'WIN 2000'
> WHEN CHARINDEX('5.1', c) > 0 then 'WIN XP'
> WHEN CHARINDEX('5.2', c) > 0 then 'WIN2K3' END
> FROM #tmp
>
>
>
> "Paulo" <prbspfc@.uol.com.br> wrote in message
> news:ucf0TSUeIHA.288@.TK2MSFTNGP02.phx.gbl...
>> if CHARINDEX('5.0', SELECT @.@.VERSION) = 0 then 'WIN 2000
>> if CHARINDEX('5.1', SELECT @.@.VERSION) = 0 then 'WIN XP
>> if CHARINDEX('5.2', SELECT @.@.VERSION) = 0 then 'WIN2K3
>> Should work?
>> Thanks man!
>> "Uri Dimant" <urid@.iscar.co.il> escreveu na mensagem
>> news:uDZFoKUeIHA.5348@.TK2MSFTNGP03.phx.gbl...
>> CHARINDEX ,PATINDEX'
>> "Paulo" <prbspfc@.uol.com.br> wrote in message
>> news:u2BJWIUeIHA.4844@.TK2MSFTNGP04.phx.gbl...
>> Hi, any function wich returns me if contains some string inside another
>> string for using on SP?
>> InStr?
>> Thanks
>>
>>
>>
>

Any function?

Hi, any function wich returns me if contains some string inside another
string for using on SP?
InStr?
Thanks
CHARINDEX ,PATINDEX?
"Paulo" <prbspfc@.uol.com.br> wrote in message
news:u2BJWIUeIHA.4844@.TK2MSFTNGP04.phx.gbl...
> Hi, any function wich returns me if contains some string inside another
> string for using on SP?
> InStr?
> Thanks
>
|||if CHARINDEX('5.0', SELECT @.@.VERSION) = 0 then 'WIN 2000
if CHARINDEX('5.1', SELECT @.@.VERSION) = 0 then 'WIN XP
if CHARINDEX('5.2', SELECT @.@.VERSION) = 0 then 'WIN2K3
Should work?
Thanks man!
"Uri Dimant" <urid@.iscar.co.il> escreveu na mensagem
news:uDZFoKUeIHA.5348@.TK2MSFTNGP03.phx.gbl...
> CHARINDEX ,PATINDEX?
> "Paulo" <prbspfc@.uol.com.br> wrote in message
> news:u2BJWIUeIHA.4844@.TK2MSFTNGP04.phx.gbl...
>
|||Paulo
CREATE TABLE #tmp (c VARCHAR(1000))
GO
INSERT INTO #tmp EXEC('SELECT @.@.VERSION')
SELECT CASE WHEN CHARINDEX('5.0', c) > 0 then 'WIN 2000'
WHEN CHARINDEX('5.1', c) > 0 then 'WIN XP'
WHEN CHARINDEX('5.2', c) > 0 then 'WIN2K3' END
FROM #tmp
"Paulo" <prbspfc@.uol.com.br> wrote in message
news:ucf0TSUeIHA.288@.TK2MSFTNGP02.phx.gbl...
> if CHARINDEX('5.0', SELECT @.@.VERSION) = 0 then 'WIN 2000
> if CHARINDEX('5.1', SELECT @.@.VERSION) = 0 then 'WIN XP
> if CHARINDEX('5.2', SELECT @.@.VERSION) = 0 then 'WIN2K3
> Should work?
> Thanks man!
> "Uri Dimant" <urid@.iscar.co.il> escreveu na mensagem
> news:uDZFoKUeIHA.5348@.TK2MSFTNGP03.phx.gbl...
>
|||Thanks man... very good !
"Uri Dimant" <urid@.iscar.co.il> escreveu na mensagem
news:uqwDEZUeIHA.5208@.TK2MSFTNGP04.phx.gbl...
> Paulo
> CREATE TABLE #tmp (c VARCHAR(1000))
> GO
> INSERT INTO #tmp EXEC('SELECT @.@.VERSION')
> SELECT CASE WHEN CHARINDEX('5.0', c) > 0 then 'WIN 2000'
> WHEN CHARINDEX('5.1', c) > 0 then 'WIN XP'
> WHEN CHARINDEX('5.2', c) > 0 then 'WIN2K3' END
> FROM #tmp
>
>
>
> "Paulo" <prbspfc@.uol.com.br> wrote in message
> news:ucf0TSUeIHA.288@.TK2MSFTNGP02.phx.gbl...
>

Any equivalent for NEXT_DAY fn of Oracle

Haii Friends,
Is there any equivalent for Oracle's NEXT_DAY function in sqlserver.I need it for the following query...
SELECT NEXT_DAY( SYSDATE ,'THURSDAY') FROM DUAL;
ur Help in this regard is really appreciated...
Regards,
VickyI'm thinking that this query returns you the upcoming Thursday? There's not an immediate function that will easily do this for you, however, either a proc or function can achieve this easily. This is assuming you always want the upcoming Thursday

CREATE PROCEDURE dbo.getNextThursday AS

DECLARE @.CurrentDate AS DATETIME
DECLARE @.DayOfWeek AS TINYINT

SELECT @.DayOfWeek = DATEPART(WEEKDAY, GETDATE())

IF (@.DayOfWeek < 5) -- Not equal to Thursday
BEGIN
SELECT @.CurrentDate = (GETDATE() + (5-@.DayOfWeek))
END
ELSE
BEGIN
SELECT @.CurrentDate = (GETDATE() + (7-(@.DayOfWeek-5)))
END

SELECT @.CurrentDate

GO

ANSI Nulls

I've mentioned this before, but now it's got me stuck. I have a
table-valued function that was created using QA in SQL 2000. I can't
edit it using 2005 Mgt Studio, because the property AnsiNullsStatus
isn't set (apparently SQL 2000 forgot to set it when creating the
function). Now I find I really want to edit it in SQL 2005 MS without
going to SQL 2000 QA on my laptop. So, using SQL 2005 MS, I dropped
and re-created the function, having obtained the code from SQL 2000 QA.
That should fix this, right? When SQL 2005 re-creates the function, it
will specify whatever it needs. But no, I still can't edit it! The
property is still not set!

Can anyone help? Please?

Thanks,
Jim GeissmanHello, Jim

It's a known bug. See:
http://lab.msdn.microsoft.com/Produ...ackid=FDBK38845

Microsoft tried to fix it in SQL Server 2005 SP1, but did not fully
succeed. However, on SP1 you can script the function in Management
Studio, by choosing "Script function as Create" (but it still doesn't
work with "Modify" or "Script function as Alter").

Razvan|||Thank you, Razvan.