Relatively new to SQL ...

How would I do something like this in a stored procedure?
Basically I want to declare ProductPrice as a sub routine type of thing..
Select * from products where {ProductPrice}
{ProductPrice}
(ProductID like "%Car%" or ProductID != NULL)
Help...
This is a simple example, the others I have require this kind of
functionality.
Thanks.
If I understand what you're trying to do correctly, you do this:
CREATE PROC ProductPrice
AS
SELECT * from products WHERE ProductID LIKE "%Car%" OR ProductID IS NOT
NULL)
GO
You'd normally want to pass a parameter though instead of hard-coding a
search term like "%car%". Also, the where clause you gave isn't correct...I
think you might have meant "= null" instead of not equals.
"AshVsAOD" wrote:

> How would I do something like this in a stored procedure?
> Basically I want to declare ProductPrice as a sub routine type of thing..
> Select * from products where {ProductPrice}
> {ProductPrice}
> (ProductID like "%Car%" or ProductID != NULL)
>
> Help...
> This is a simple example, the others I have require this kind of
> functionality.
> Thanks.
>
>
|||Yeah, I understand that. What I am after is. Can SQL use sub-procedures
like in my original piece of code?
[vbcol=seagreen]
Thanks for the help anyway.
"Mike Asher" <MikeAsher@.discussions.microsoft.com> wrote in message
news:FD3207AA-F1D0-460A-8979-E37E611A08B3@.microsoft.com...
> If I understand what you're trying to do correctly, you do this:
> CREATE PROC ProductPrice
> AS
> SELECT * from products WHERE ProductID LIKE "%Car%" OR ProductID IS NOT
> NULL)
> GO
> You'd normally want to pass a parameter though instead of hard-coding a
> search term like "%car%". Also, the where clause you gave isn't
correct...I[vbcol=seagreen]
> think you might have meant "= null" instead of not equals.
>
> "AshVsAOD" wrote:
thing..[vbcol=seagreen]
|||AshVsAOD wrote:
> How would I do something like this in a stored procedure?
> Basically I want to declare ProductPrice as a sub routine type of
> thing..
> Select * from products where {ProductPrice}
> {ProductPrice}
> (ProductID like "%Car%" or ProductID != NULL)
>
> Help...
> This is a simple example, the others I have require this kind of
> functionality.
> Thanks.
Firstly, you cannot compare to NULL in that method. Nothing is equal to
NULL, not even NULL. If you want to compare the value (or lack thereof)
of a column to NULL you have to use "Where ColName IS NULL" or "Where
ColName IS NOT NULL". As an exercise, run the following code and examine
the results:
create table #NullTest (col1 nvarchar(10))
go
Insert Into #NullTest Values (NULL)
Insert Into #NullTest Values (N'')
Insert Into #NullTest Values (N'ABC')
go
Select col1 from #NullTest Where col1 != NULL
Select col1 from #NullTest Where col1 = NULL
Select col1 from #NullTest Where col1 IS NOT NULL
Select col1 from #NullTest Where col1 IS NULL
Select col1 from #NullTest Where col1 = N'ABC'
Drop Table #NullTest
Secondly, your query if changed to IS NOT NULL, will return all rows
that do not have a NULL ProductID.
"ProductID like "%Car%" or ProductID IS NOT NULL"
This means give me all products that have "Car" somewhere in the name
_and_ all products where the product id is not null.
You should also avoid using SELECT * syntax for result sets.
I'm not sure exactly what your criteria for the procedure is. Do you
want to pass a ProductID to the procedure and have the procedure return
the rows that match? If so...
Create Proc dbo.GetProducts
@.ProductID nvarchar(100)
as
Begin
Select
Col1,
Col2,
Col3
From dbo.Products
Where ProductID LIKE @.ProductID
End
Go
If you only need to do equality comparisons, then you can change the
"LIKE" to an "=".
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||Ok,
So my example was poorly constructed.
I know not to use select *, I also know my query was rubbish.
What I don't know is, can I use sub routine type code in SQL.
For example Select blah from products where {productprice}
{productprice}
whatever statement here...
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:eQheovtdFHA.3880@.tk2msftngp13.phx.gbl...
> AshVsAOD wrote:
> Firstly, you cannot compare to NULL in that method. Nothing is equal to
> NULL, not even NULL. If you want to compare the value (or lack thereof)
> of a column to NULL you have to use "Where ColName IS NULL" or "Where
> ColName IS NOT NULL". As an exercise, run the following code and examine
> the results:
> create table #NullTest (col1 nvarchar(10))
> go
> Insert Into #NullTest Values (NULL)
> Insert Into #NullTest Values (N'')
> Insert Into #NullTest Values (N'ABC')
> go
> Select col1 from #NullTest Where col1 != NULL
> Select col1 from #NullTest Where col1 = NULL
> Select col1 from #NullTest Where col1 IS NOT NULL
> Select col1 from #NullTest Where col1 IS NULL
> Select col1 from #NullTest Where col1 = N'ABC'
> Drop Table #NullTest
>
> Secondly, your query if changed to IS NOT NULL, will return all rows
> that do not have a NULL ProductID.
> "ProductID like "%Car%" or ProductID IS NOT NULL"
> This means give me all products that have "Car" somewhere in the name
> _and_ all products where the product id is not null.
> You should also avoid using SELECT * syntax for result sets.
> I'm not sure exactly what your criteria for the procedure is. Do you
> want to pass a ProductID to the procedure and have the procedure return
> the rows that match? If so...
>
> Create Proc dbo.GetProducts
> @.ProductID nvarchar(100)
> as
> Begin
> Select
> Col1,
> Col2,
> Col3
> From dbo.Products
> Where ProductID LIKE @.ProductID
> End
> Go
> If you only need to do equality comparisons, then you can change the
> "LIKE" to an "=".
>
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>
|||> Yeah, I understand that. What I am after is. Can SQL use sub-procedures
> like in my original piece of code?
Yes you can; one stored proc can call another, or itself recursively.
Return values other than result sets can be passed back to the caller via
output parameters. If you want to use in-line "function-type" syntax,
though, you'll need to code the procedure as a user-defined function.
|||As Mike Asher posted, you can call stored procs from other stored procs
but that's not what you're talking about here in your example (from what
I can tell). You're talking about nested sub-queries, which basically
are categorized into scalar subqueries (that return a single column,
single row) and correlated subqueries (which are dependent on one or
more values from the outer query). I'm guessing you want to do a
correlated subquery like:
select blah from products p
where exists (select * from orders o where o.productID = p.productID)
or
select blah from products p
where productID in (select productID from orders o where customer =
'ACME Corp')
Something like that. Of course this won't allow code reuse and there
are better ways to write these queries (for example quite often
correlated subqueries can be rewritten with joins instead of the
subquery and in many cases perform better with the joins) but in my
understanding that's basically what you were asking. You cannot call a
stored proc in the WHERE clause of a SELECT, INSERT, UPDATE or DELETE
statement but you can include subqueries against views and/or tables.
Additionally, the "tables" in the FROM clause can alternately be tables,
views or derived tables (basically a SELECT statement wrapped in
parentheses and given a table alias).
HTH
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
AshVsAOD wrote:

>Ok,
>So my example was poorly constructed.
>I know not to use select *, I also know my query was rubbish.
>What I don't know is, can I use sub routine type code in SQL.
>For example Select blah from products where {productprice}
>{productprice}
>whatever statement here...
>
>"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
>news:eQheovtdFHA.3880@.tk2msftngp13.phx.gbl...
>
>
>
|||Thanks,
and sorry.
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message
news:OB6ktMvdFHA.1136@.TK2MSFTNGP12.phx.gbl...
> As Mike Asher posted, you can call stored procs from other stored procs
> but that's not what you're talking about here in your example (from what
> I can tell). You're talking about nested sub-queries, which basically
> are categorized into scalar subqueries (that return a single column,
> single row) and correlated subqueries (which are dependent on one or
> more values from the outer query). I'm guessing you want to do a
> correlated subquery like:
> select blah from products p
> where exists (select * from orders o where o.productID = p.productID)
> or
> select blah from products p
> where productID in (select productID from orders o where customer =
> 'ACME Corp')
>
> Something like that. Of course this won't allow code reuse and there
> are better ways to write these queries (for example quite often
> correlated subqueries can be rewritten with joins instead of the
> subquery and in many cases perform better with the joins) but in my
> understanding that's basically what you were asking. You cannot call a
> stored proc in the WHERE clause of a SELECT, INSERT, UPDATE or DELETE
> statement but you can include subqueries against views and/or tables.
> Additionally, the "tables" in the FROM clause can alternately be tables,
> views or derived tables (basically a SELECT statement wrapped in
> parentheses and given a table alias).
> HTH
> --
> *mike hodgson* |/ database administrator/ | mallesons stephen jaques
> *T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
> *E* mailto:mike.hodgson@.mallesons.nospam.com |* W*
http://www.mallesons.com
>
> AshVsAOD wrote:
>
|||Cheers!
"Mike Asher" <MikeAsher@.discussions.microsoft.com> wrote in message
news:316F437C-80F2-4127-846D-2A87C73960A2@.microsoft.com...[vbcol=seagreen]
sub-procedures
> Yes you can; one stored proc can call another, or itself recursively.
> Return values other than result sets can be passed back to the caller via
> output parameters. If you want to use in-line "function-type" syntax,
> though, you'll need to code the procedure as a user-defined function.
sql

Relatively new to SQL ...

How would I do something like this in a stored procedure'
Basically I want to declare ProductPrice as a sub routine type of thing..
Select * from products where {ProductPrice}
{ProductPrice}
(ProductID like "%Car%" or ProductID != NULL)
Help...
This is a simple example, the others I have require this kind of
functionality.
Thanks.If I understand what you're trying to do correctly, you do this:
CREATE PROC ProductPrice
AS
SELECT * from products WHERE ProductID LIKE "%Car%" OR ProductID IS NOT
NULL)
GO
You'd normally want to pass a parameter though instead of hard-coding a
search term like "%car%". Also, the where clause you gave isn't correct...I
think you might have meant "= null" instead of not equals.
"AshVsAOD" wrote:
> How would I do something like this in a stored procedure'
> Basically I want to declare ProductPrice as a sub routine type of thing..
> Select * from products where {ProductPrice}
> {ProductPrice}
> (ProductID like "%Car%" or ProductID != NULL)
>
> Help...
> This is a simple example, the others I have require this kind of
> functionality.
> Thanks.
>
>|||Yeah, I understand that. What I am after is. Can SQL use sub-procedures
like in my original piece of code?
> > Select * from products where {ProductPrice}
> >
> > {ProductPrice}
> > (ProductID like "%Car%" or ProductID != NULL)
Thanks for the help anyway.
"Mike Asher" <MikeAsher@.discussions.microsoft.com> wrote in message
news:FD3207AA-F1D0-460A-8979-E37E611A08B3@.microsoft.com...
> If I understand what you're trying to do correctly, you do this:
> CREATE PROC ProductPrice
> AS
> SELECT * from products WHERE ProductID LIKE "%Car%" OR ProductID IS NOT
> NULL)
> GO
> You'd normally want to pass a parameter though instead of hard-coding a
> search term like "%car%". Also, the where clause you gave isn't
correct...I
> think you might have meant "= null" instead of not equals.
>
> "AshVsAOD" wrote:
> > How would I do something like this in a stored procedure'
> >
> > Basically I want to declare ProductPrice as a sub routine type of
thing..
> >
> > Select * from products where {ProductPrice}
> >
> > {ProductPrice}
> > (ProductID like "%Car%" or ProductID != NULL)
> >
> >
> > Help...
> >
> > This is a simple example, the others I have require this kind of
> > functionality.
> >
> > Thanks.
> >
> >
> >|||AshVsAOD wrote:
> How would I do something like this in a stored procedure'
> Basically I want to declare ProductPrice as a sub routine type of
> thing..
> Select * from products where {ProductPrice}
> {ProductPrice}
> (ProductID like "%Car%" or ProductID != NULL)
>
> Help...
> This is a simple example, the others I have require this kind of
> functionality.
> Thanks.
Firstly, you cannot compare to NULL in that method. Nothing is equal to
NULL, not even NULL. If you want to compare the value (or lack thereof)
of a column to NULL you have to use "Where ColName IS NULL" or "Where
ColName IS NOT NULL". As an exercise, run the following code and examine
the results:
create table #NullTest (col1 nvarchar(10))
go
Insert Into #NullTest Values (NULL)
Insert Into #NullTest Values (N'')
Insert Into #NullTest Values (N'ABC')
go
Select col1 from #NullTest Where col1 != NULL
Select col1 from #NullTest Where col1 = NULL
Select col1 from #NullTest Where col1 IS NOT NULL
Select col1 from #NullTest Where col1 IS NULL
Select col1 from #NullTest Where col1 = N'ABC'
Drop Table #NullTest
Secondly, your query if changed to IS NOT NULL, will return all rows
that do not have a NULL ProductID.
"ProductID like "%Car%" or ProductID IS NOT NULL"
This means give me all products that have "Car" somewhere in the name
_and_ all products where the product id is not null.
You should also avoid using SELECT * syntax for result sets.
I'm not sure exactly what your criteria for the procedure is. Do you
want to pass a ProductID to the procedure and have the procedure return
the rows that match? If so...
Create Proc dbo.GetProducts
@.ProductID nvarchar(100)
as
Begin
Select
Col1,
Col2,
Col3
From dbo.Products
Where ProductID LIKE @.ProductID
End
Go
If you only need to do equality comparisons, then you can change the
"LIKE" to an "=".
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Ok,
So my example was poorly constructed.
I know not to use select *, I also know my query was rubbish.
What I don't know is, can I use sub routine type code in SQL.
For example Select blah from products where {productprice}
{productprice}
whatever statement here...
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:eQheovtdFHA.3880@.tk2msftngp13.phx.gbl...
> AshVsAOD wrote:
> > How would I do something like this in a stored procedure'
> >
> > Basically I want to declare ProductPrice as a sub routine type of
> > thing..
> >
> > Select * from products where {ProductPrice}
> >
> > {ProductPrice}
> > (ProductID like "%Car%" or ProductID != NULL)
> >
> >
> > Help...
> >
> > This is a simple example, the others I have require this kind of
> > functionality.
> >
> > Thanks.
> Firstly, you cannot compare to NULL in that method. Nothing is equal to
> NULL, not even NULL. If you want to compare the value (or lack thereof)
> of a column to NULL you have to use "Where ColName IS NULL" or "Where
> ColName IS NOT NULL". As an exercise, run the following code and examine
> the results:
> create table #NullTest (col1 nvarchar(10))
> go
> Insert Into #NullTest Values (NULL)
> Insert Into #NullTest Values (N'')
> Insert Into #NullTest Values (N'ABC')
> go
> Select col1 from #NullTest Where col1 != NULL
> Select col1 from #NullTest Where col1 = NULL
> Select col1 from #NullTest Where col1 IS NOT NULL
> Select col1 from #NullTest Where col1 IS NULL
> Select col1 from #NullTest Where col1 = N'ABC'
> Drop Table #NullTest
>
> Secondly, your query if changed to IS NOT NULL, will return all rows
> that do not have a NULL ProductID.
> "ProductID like "%Car%" or ProductID IS NOT NULL"
> This means give me all products that have "Car" somewhere in the name
> _and_ all products where the product id is not null.
> You should also avoid using SELECT * syntax for result sets.
> I'm not sure exactly what your criteria for the procedure is. Do you
> want to pass a ProductID to the procedure and have the procedure return
> the rows that match? If so...
>
> Create Proc dbo.GetProducts
> @.ProductID nvarchar(100)
> as
> Begin
> Select
> Col1,
> Col2,
> Col3
> From dbo.Products
> Where ProductID LIKE @.ProductID
> End
> Go
> If you only need to do equality comparisons, then you can change the
> "LIKE" to an "=".
>
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>|||> Yeah, I understand that. What I am after is. Can SQL use sub-procedures
> like in my original piece of code?
Yes you can; one stored proc can call another, or itself recursively.
Return values other than result sets can be passed back to the caller via
output parameters. If you want to use in-line "function-type" syntax,
though, you'll need to code the procedure as a user-defined function.|||This is a multi-part message in MIME format.
--050701000406080309060203
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
As Mike Asher posted, you can call stored procs from other stored procs
but that's not what you're talking about here in your example (from what
I can tell). You're talking about nested sub-queries, which basically
are categorized into scalar subqueries (that return a single column,
single row) and correlated subqueries (which are dependent on one or
more values from the outer query). I'm guessing you want to do a
correlated subquery like:
select blah from products p
where exists (select * from orders o where o.productID = p.productID)
or
select blah from products p
where productID in (select productID from orders o where customer = 'ACME Corp')
Something like that. Of course this won't allow code reuse and there
are better ways to write these queries (for example quite often
correlated subqueries can be rewritten with joins instead of the
subquery and in many cases perform better with the joins) but in my
understanding that's basically what you were asking. You cannot call a
stored proc in the WHERE clause of a SELECT, INSERT, UPDATE or DELETE
statement but you can include subqueries against views and/or tables.
Additionally, the "tables" in the FROM clause can alternately be tables,
views or derived tables (basically a SELECT statement wrapped in
parentheses and given a table alias).
HTH
--
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
AshVsAOD wrote:
>Ok,
>So my example was poorly constructed.
>I know not to use select *, I also know my query was rubbish.
>What I don't know is, can I use sub routine type code in SQL.
>For example Select blah from products where {productprice}
>{productprice}
>whatever statement here...
>
>"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
>news:eQheovtdFHA.3880@.tk2msftngp13.phx.gbl...
>
>>AshVsAOD wrote:
>>
>>How would I do something like this in a stored procedure'
>>Basically I want to declare ProductPrice as a sub routine type of
>>thing..
>>Select * from products where {ProductPrice}
>>{ProductPrice}
>>(ProductID like "%Car%" or ProductID != NULL)
>>
>>Help...
>>This is a simple example, the others I have require this kind of
>>functionality.
>>Thanks.
>>
>>Firstly, you cannot compare to NULL in that method. Nothing is equal to
>>NULL, not even NULL. If you want to compare the value (or lack thereof)
>>of a column to NULL you have to use "Where ColName IS NULL" or "Where
>>ColName IS NOT NULL". As an exercise, run the following code and examine
>>the results:
>>create table #NullTest (col1 nvarchar(10))
>>go
>>Insert Into #NullTest Values (NULL)
>>Insert Into #NullTest Values (N'')
>>Insert Into #NullTest Values (N'ABC')
>>go
>>Select col1 from #NullTest Where col1 != NULL
>>Select col1 from #NullTest Where col1 = NULL
>>Select col1 from #NullTest Where col1 IS NOT NULL
>>Select col1 from #NullTest Where col1 IS NULL
>>Select col1 from #NullTest Where col1 = N'ABC'
>>Drop Table #NullTest
>>
>>Secondly, your query if changed to IS NOT NULL, will return all rows
>>that do not have a NULL ProductID.
>>"ProductID like "%Car%" or ProductID IS NOT NULL"
>>This means give me all products that have "Car" somewhere in the name
>>_and_ all products where the product id is not null.
>>You should also avoid using SELECT * syntax for result sets.
>>I'm not sure exactly what your criteria for the procedure is. Do you
>>want to pass a ProductID to the procedure and have the procedure return
>>the rows that match? If so...
>>
>>Create Proc dbo.GetProducts
>>@.ProductID nvarchar(100)
>>as
>>Begin
>> Select
>> Col1,
>> Col2,
>> Col3
>> From dbo.Products
>> Where ProductID LIKE @.ProductID
>>End
>>Go
>>If you only need to do equality comparisons, then you can change the
>>"LIKE" to an "=".
>>
>>
>>--
>>David Gugick
>>Quest Software
>>www.imceda.com
>>www.quest.com
>>
>
>
--050701000406080309060203
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>As Mike Asher posted, you can call stored procs from other stored
procs but that's not what you're talking about here in your example
(from what I can tell). You're talking about nested sub-queries, which
basically are categorized into scalar subqueries (that return a single
column, single row) and correlated subqueries (which are dependent on
one or more values from the outer query). I'm guessing you want to do
a correlated subquery like:<br>
</tt>
<blockquote><tt>select blah from products p</tt><br>
<tt>where exists (select * from orders o where o.productID =p.productID)</tt><br>
</blockquote>
<tt>or<br>
</tt>
<blockquote><tt>select blah from products p</tt><br>
<tt>where productID in (select productID from orders o where customer
= 'ACME Corp')</tt><br>
</blockquote>
<tt><br>
Something like that. Of course this won't allow code reuse and there
are better ways to write these queries (for example quite often
correlated subqueries can be rewritten with joins instead of the
subquery and in many cases perform better with the joins) but in my
understanding that's basically what you were asking. You cannot call a
stored proc in the WHERE clause of a SELECT, INSERT, UPDATE or DELETE
statement but you can include subqueries against views and/or tables.
Additionally, the "tables" in the FROM clause can alternately be
tables, views or derived tables (basically a SELECT statement wrapped
in parentheses and given a table alias).<br>
<br>
HTH<br>
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font> </span><b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"> <font face="Tahoma"
size="2">|</font><i><font face="Tahoma"> </font><font face="Tahoma"
size="2"> database administrator</font></i><font face="Tahoma" size="2">
| mallesons</font><font face="Tahoma"> </font><font face="Tahoma"
size="2">stephen</font><font face="Tahoma"> </font><font face="Tahoma"
size="2"> jaques</font><font face="Tahoma"><br>
</font><b><font face="Tahoma" size="2">T</font></b><font face="Tahoma"
size="2"> +61 (2) 9296 3668 |</font><b><font face="Tahoma"> </font><font
face="Tahoma" size="2"> F</font></b><font face="Tahoma" size="2"> +61
(2) 9296 3885 |</font><b><font face="Tahoma"> </font><font
face="Tahoma" size="2">M</font></b><font face="Tahoma" size="2"> +61
(408) 675 907</font><br>
<b><font face="Tahoma" size="2">E</font></b><font face="Tahoma" size="2">
<a href="http://links.10026.com/?link=mailto:mike.hodgson@.mallesons.nospam.com">
mailto:mike.hodgson@.mallesons.nospam.com</a> |</font><b><font
face="Tahoma"> </font><font face="Tahoma" size="2">W</font></b><font
face="Tahoma" size="2"> <a href="http://links.10026.com/?link=/">http://www.mallesons.com">
http://www.mallesons.com</a></font></span> </p>
</div>
<br>
<br>
AshVsAOD wrote:
<blockquote cite="mide79syxudFHA.584@.TK2MSFTNGP15.phx.gbl" type="cite">
<pre wrap="">Ok,
So my example was poorly constructed.
I know not to use select *, I also know my query was rubbish.
What I don't know is, can I use sub routine type code in SQL.
For example Select blah from products where {productprice}
{productprice}
whatever statement here...
"David Gugick" <a class="moz-txt-link-rfc2396E" href="http://links.10026.com/?link=mailto:david.gugick-nospam@.quest.com"><david.gugick-nospam@.quest.com></a> wrote in message
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=news:eQheovtdFHA.3880@.tk2msftngp13.phx.gbl">news:eQheovtdFHA.3880@.tk2msftngp13.phx.gbl</a>...
</pre>
<blockquote type="cite">
<pre wrap="">AshVsAOD wrote:
</pre>
<blockquote type="cite">
<pre wrap="">How would I do something like this in a stored procedure'
Basically I want to declare ProductPrice as a sub routine type of
thing..
Select * from products where {ProductPrice}
{ProductPrice}
(ProductID like "%Car%" or ProductID != NULL)
Help...
This is a simple example, the others I have require this kind of
functionality.
Thanks.
</pre>
</blockquote>
<pre wrap="">Firstly, you cannot compare to NULL in that method. Nothing is equal to
NULL, not even NULL. If you want to compare the value (or lack thereof)
of a column to NULL you have to use "Where ColName IS NULL" or "Where
ColName IS NOT NULL". As an exercise, run the following code and examine
the results:
create table #NullTest (col1 nvarchar(10))
go
Insert Into #NullTest Values (NULL)
Insert Into #NullTest Values (N'')
Insert Into #NullTest Values (N'ABC')
go
Select col1 from #NullTest Where col1 != NULL
Select col1 from #NullTest Where col1 = NULL
Select col1 from #NullTest Where col1 IS NOT NULL
Select col1 from #NullTest Where col1 IS NULL
Select col1 from #NullTest Where col1 = N'ABC'
Drop Table #NullTest
Secondly, your query if changed to IS NOT NULL, will return all rows
that do not have a NULL ProductID.
"ProductID like "%Car%" or ProductID IS NOT NULL"
This means give me all products that have "Car" somewhere in the name
_and_ all products where the product id is not null.
You should also avoid using SELECT * syntax for result sets.
I'm not sure exactly what your criteria for the procedure is. Do you
want to pass a ProductID to the procedure and have the procedure return
the rows that match? If so...
Create Proc dbo.GetProducts
@.ProductID nvarchar(100)
as
Begin
Select
Col1,
Col2,
Col3
From dbo.Products
Where ProductID LIKE @.ProductID
End
Go
If you only need to do equality comparisons, then you can change the
"LIKE" to an "=".
David Gugick
Quest Software
<a class="moz-txt-link-abbreviated" href="http://links.10026.com/?link=www.imceda.com</a>">http://www.imceda.com">www.imceda.com</a>
<a class="moz-txt-link-abbreviated" href="http://links.10026.com/?link=www.quest.com</a>">http://www.quest.com">www.quest.com</a>
</pre>
</blockquote>
<pre wrap=""><!-->
</pre>
</blockquote>
</body>
</html>
--050701000406080309060203--|||Thanks,
and sorry.
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message
news:OB6ktMvdFHA.1136@.TK2MSFTNGP12.phx.gbl...
> As Mike Asher posted, you can call stored procs from other stored procs
> but that's not what you're talking about here in your example (from what
> I can tell). You're talking about nested sub-queries, which basically
> are categorized into scalar subqueries (that return a single column,
> single row) and correlated subqueries (which are dependent on one or
> more values from the outer query). I'm guessing you want to do a
> correlated subquery like:
> select blah from products p
> where exists (select * from orders o where o.productID = p.productID)
> or
> select blah from products p
> where productID in (select productID from orders o where customer => 'ACME Corp')
>
> Something like that. Of course this won't allow code reuse and there
> are better ways to write these queries (for example quite often
> correlated subqueries can be rewritten with joins instead of the
> subquery and in many cases perform better with the joins) but in my
> understanding that's basically what you were asking. You cannot call a
> stored proc in the WHERE clause of a SELECT, INSERT, UPDATE or DELETE
> statement but you can include subqueries against views and/or tables.
> Additionally, the "tables" in the FROM clause can alternately be tables,
> views or derived tables (basically a SELECT statement wrapped in
> parentheses and given a table alias).
> HTH
> --
> *mike hodgson* |/ database administrator/ | mallesons stephen jaques
> *T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
> *E* mailto:mike.hodgson@.mallesons.nospam.com |* W*
http://www.mallesons.com
>
> AshVsAOD wrote:
> >Ok,
> >
> >So my example was poorly constructed.
> >
> >I know not to use select *, I also know my query was rubbish.
> >
> >What I don't know is, can I use sub routine type code in SQL.
> >
> >For example Select blah from products where {productprice}
> >
> >{productprice}
> >whatever statement here...
> >
> >
> >
> >"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
> >news:eQheovtdFHA.3880@.tk2msftngp13.phx.gbl...
> >
> >
> >>AshVsAOD wrote:
> >>
> >>
> >>How would I do something like this in a stored procedure'
> >>
> >>Basically I want to declare ProductPrice as a sub routine type of
> >>thing..
> >>
> >>Select * from products where {ProductPrice}
> >>
> >>{ProductPrice}
> >>(ProductID like "%Car%" or ProductID != NULL)
> >>
> >>
> >>Help...
> >>
> >>This is a simple example, the others I have require this kind of
> >>functionality.
> >>
> >>Thanks.
> >>
> >>
> >>Firstly, you cannot compare to NULL in that method. Nothing is equal to
> >>NULL, not even NULL. If you want to compare the value (or lack thereof)
> >>of a column to NULL you have to use "Where ColName IS NULL" or "Where
> >>ColName IS NOT NULL". As an exercise, run the following code and examine
> >>the results:
> >>
> >>create table #NullTest (col1 nvarchar(10))
> >>go
> >>
> >>Insert Into #NullTest Values (NULL)
> >>Insert Into #NullTest Values (N'')
> >>Insert Into #NullTest Values (N'ABC')
> >>go
> >>
> >>Select col1 from #NullTest Where col1 != NULL
> >>Select col1 from #NullTest Where col1 = NULL
> >>Select col1 from #NullTest Where col1 IS NOT NULL
> >>Select col1 from #NullTest Where col1 IS NULL
> >>Select col1 from #NullTest Where col1 = N'ABC'
> >>
> >>Drop Table #NullTest
> >>
> >>
> >>Secondly, your query if changed to IS NOT NULL, will return all rows
> >>that do not have a NULL ProductID.
> >>
> >>"ProductID like "%Car%" or ProductID IS NOT NULL"
> >>
> >>This means give me all products that have "Car" somewhere in the name
> >>_and_ all products where the product id is not null.
> >>
> >>You should also avoid using SELECT * syntax for result sets.
> >>
> >>I'm not sure exactly what your criteria for the procedure is. Do you
> >>want to pass a ProductID to the procedure and have the procedure return
> >>the rows that match? If so...
> >>
> >>
> >>Create Proc dbo.GetProducts
> >>@.ProductID nvarchar(100)
> >>as
> >>Begin
> >> Select
> >> Col1,
> >> Col2,
> >> Col3
> >> From dbo.Products
> >> Where ProductID LIKE @.ProductID
> >>End
> >>Go
> >>
> >>If you only need to do equality comparisons, then you can change the
> >>"LIKE" to an "=".
> >>
> >>
> >>
> >>
> >>--
> >>David Gugick
> >>Quest Software
> >>www.imceda.com
> >>www.quest.com
> >>
> >>
> >>
> >
> >
> >
> >
>|||Cheers!
"Mike Asher" <MikeAsher@.discussions.microsoft.com> wrote in message
news:316F437C-80F2-4127-846D-2A87C73960A2@.microsoft.com...
> > Yeah, I understand that. What I am after is. Can SQL use
sub-procedures
> > like in my original piece of code?
> Yes you can; one stored proc can call another, or itself recursively.
> Return values other than result sets can be passed back to the caller via
> output parameters. If you want to use in-line "function-type" syntax,
> though, you'll need to code the procedure as a user-defined function.

Relatively new to SQL ...

How would I do something like this in a stored procedure'
Basically I want to declare ProductPrice as a sub routine type of thing..
Select * from products where {ProductPrice}
{ProductPrice}
(ProductID like "%Car%" or ProductID != NULL)
Help...
This is a simple example, the others I have require this kind of
functionality.
Thanks.If I understand what you're trying to do correctly, you do this:
CREATE PROC ProductPrice
AS
SELECT * from products WHERE ProductID LIKE "%Car%" OR ProductID IS NOT
NULL)
GO
You'd normally want to pass a parameter though instead of hard-coding a
search term like "%car%". Also, the where clause you gave isn't correct...I
think you might have meant "= null" instead of not equals.
"AshVsAOD" wrote:

> How would I do something like this in a stored procedure'
> Basically I want to declare ProductPrice as a sub routine type of thing..
> Select * from products where {ProductPrice}
> {ProductPrice}
> (ProductID like "%Car%" or ProductID != NULL)
>
> Help...
> This is a simple example, the others I have require this kind of
> functionality.
> Thanks.
>
>|||Yeah, I understand that. What I am after is. Can SQL use sub-procedures
like in my original piece of code?

Thanks for the help anyway.
"Mike Asher" <MikeAsher@.discussions.microsoft.com> wrote in message
news:FD3207AA-F1D0-460A-8979-E37E611A08B3@.microsoft.com...[vbcol=seagreen]
> If I understand what you're trying to do correctly, you do this:
> CREATE PROC ProductPrice
> AS
> SELECT * from products WHERE ProductID LIKE "%Car%" OR ProductID IS NOT
> NULL)
> GO
> You'd normally want to pass a parameter though instead of hard-coding a
> search term like "%car%". Also, the where clause you gave isn't
correct...I[vbcol=seagreen]
> think you might have meant "= null" instead of not equals.
>
> "AshVsAOD" wrote:
>
thing..[vbcol=seagreen]|||AshVsAOD wrote:
> How would I do something like this in a stored procedure'
> Basically I want to declare ProductPrice as a sub routine type of
> thing..
> Select * from products where {ProductPrice}
> {ProductPrice}
> (ProductID like "%Car%" or ProductID != NULL)
>
> Help...
> This is a simple example, the others I have require this kind of
> functionality.
> Thanks.
Firstly, you cannot compare to NULL in that method. Nothing is equal to
NULL, not even NULL. If you want to compare the value (or lack thereof)
of a column to NULL you have to use "Where ColName IS NULL" or "Where
ColName IS NOT NULL". As an exercise, run the following code and examine
the results:
create table #NullTest (col1 nvarchar(10))
go
Insert Into #NullTest Values (NULL)
Insert Into #NullTest Values (N'')
Insert Into #NullTest Values (N'ABC')
go
Select col1 from #NullTest Where col1 != NULL
Select col1 from #NullTest Where col1 = NULL
Select col1 from #NullTest Where col1 IS NOT NULL
Select col1 from #NullTest Where col1 IS NULL
Select col1 from #NullTest Where col1 = N'ABC'
Drop Table #NullTest
Secondly, your query if changed to IS NOT NULL, will return all rows
that do not have a NULL ProductID.
"ProductID like "%Car%" or ProductID IS NOT NULL"
This means give me all products that have "Car" somewhere in the name
_and_ all products where the product id is not null.
You should also avoid using SELECT * syntax for result sets.
I'm not sure exactly what your criteria for the procedure is. Do you
want to pass a ProductID to the procedure and have the procedure return
the rows that match? If so...
Create Proc dbo.GetProducts
@.ProductID nvarchar(100)
as
Begin
Select
Col1,
Col2,
Col3
From dbo.Products
Where ProductID LIKE @.ProductID
End
Go
If you only need to do equality comparisons, then you can change the
"LIKE" to an "=".
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Ok,
So my example was poorly constructed.
I know not to use select *, I also know my query was rubbish.
What I don't know is, can I use sub routine type code in SQL.
For example Select blah from products where {productprice}
{productprice}
whatever statement here...
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:eQheovtdFHA.3880@.tk2msftngp13.phx.gbl...
> AshVsAOD wrote:
> Firstly, you cannot compare to NULL in that method. Nothing is equal to
> NULL, not even NULL. If you want to compare the value (or lack thereof)
> of a column to NULL you have to use "Where ColName IS NULL" or "Where
> ColName IS NOT NULL". As an exercise, run the following code and examine
> the results:
> create table #NullTest (col1 nvarchar(10))
> go
> Insert Into #NullTest Values (NULL)
> Insert Into #NullTest Values (N'')
> Insert Into #NullTest Values (N'ABC')
> go
> Select col1 from #NullTest Where col1 != NULL
> Select col1 from #NullTest Where col1 = NULL
> Select col1 from #NullTest Where col1 IS NOT NULL
> Select col1 from #NullTest Where col1 IS NULL
> Select col1 from #NullTest Where col1 = N'ABC'
> Drop Table #NullTest
>
> Secondly, your query if changed to IS NOT NULL, will return all rows
> that do not have a NULL ProductID.
> "ProductID like "%Car%" or ProductID IS NOT NULL"
> This means give me all products that have "Car" somewhere in the name
> _and_ all products where the product id is not null.
> You should also avoid using SELECT * syntax for result sets.
> I'm not sure exactly what your criteria for the procedure is. Do you
> want to pass a ProductID to the procedure and have the procedure return
> the rows that match? If so...
>
> Create Proc dbo.GetProducts
> @.ProductID nvarchar(100)
> as
> Begin
> Select
> Col1,
> Col2,
> Col3
> From dbo.Products
> Where ProductID LIKE @.ProductID
> End
> Go
> If you only need to do equality comparisons, then you can change the
> "LIKE" to an "=".
>
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>|||> Yeah, I understand that. What I am after is. Can SQL use sub-procedures
> like in my original piece of code?
Yes you can; one stored proc can call another, or itself recursively.
Return values other than result sets can be passed back to the caller via
output parameters. If you want to use in-line "function-type" syntax,
though, you'll need to code the procedure as a user-defined function.|||As Mike Asher posted, you can call stored procs from other stored procs
but that's not what you're talking about here in your example (from what
I can tell). You're talking about nested sub-queries, which basically
are categorized into scalar subqueries (that return a single column,
single row) and correlated subqueries (which are dependent on one or
more values from the outer query). I'm guessing you want to do a
correlated subquery like:
select blah from products p
where exists (select * from orders o where o.productID = p.productID)
or
select blah from products p
where productID in (select productID from orders o where customer =
'ACME Corp')
Something like that. Of course this won't allow code reuse and there
are better ways to write these queries (for example quite often
correlated subqueries can be rewritten with joins instead of the
subquery and in many cases perform better with the joins) but in my
understanding that's basically what you were asking. You cannot call a
stored proc in the WHERE clause of a SELECT, INSERT, UPDATE or DELETE
statement but you can include subqueries against views and/or tables.
Additionally, the "tables" in the FROM clause can alternately be tables,
views or derived tables (basically a SELECT statement wrapped in
parentheses and given a table alias).
HTH
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
AshVsAOD wrote:

>Ok,
>So my example was poorly constructed.
>I know not to use select *, I also know my query was rubbish.
>What I don't know is, can I use sub routine type code in SQL.
>For example Select blah from products where {productprice}
>{productprice}
>whatever statement here...
>
>"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
>news:eQheovtdFHA.3880@.tk2msftngp13.phx.gbl...
>
>
>|||Thanks,
and sorry.
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message
news:OB6ktMvdFHA.1136@.TK2MSFTNGP12.phx.gbl...
> As Mike Asher posted, you can call stored procs from other stored procs
> but that's not what you're talking about here in your example (from what
> I can tell). You're talking about nested sub-queries, which basically
> are categorized into scalar subqueries (that return a single column,
> single row) and correlated subqueries (which are dependent on one or
> more values from the outer query). I'm guessing you want to do a
> correlated subquery like:
> select blah from products p
> where exists (select * from orders o where o.productID = p.productID)
> or
> select blah from products p
> where productID in (select productID from orders o where customer =
> 'ACME Corp')
>
> Something like that. Of course this won't allow code reuse and there
> are better ways to write these queries (for example quite often
> correlated subqueries can be rewritten with joins instead of the
> subquery and in many cases perform better with the joins) but in my
> understanding that's basically what you were asking. You cannot call a
> stored proc in the WHERE clause of a SELECT, INSERT, UPDATE or DELETE
> statement but you can include subqueries against views and/or tables.
> Additionally, the "tables" in the FROM clause can alternately be tables,
> views or derived tables (basically a SELECT statement wrapped in
> parentheses and given a table alias).
> HTH
> --
> *mike hodgson* |/ database administrator/ | mallesons stephen jaques
> *T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
> *E* mailto:mike.hodgson@.mallesons.nospam.com |* W*
http://www.mallesons.com
>
> AshVsAOD wrote:
>
>|||Cheers!
"Mike Asher" <MikeAsher@.discussions.microsoft.com> wrote in message
news:316F437C-80F2-4127-846D-2A87C73960A2@.microsoft.com...
sub-procedures[vbcol=seagreen]
> Yes you can; one stored proc can call another, or itself recursively.
> Return values other than result sets can be passed back to the caller via
> output parameters. If you want to use in-line "function-type" syntax,
> though, you'll need to code the procedure as a user-defined function.

Relatively easy SQL SELECT statement issues :/

Hi All,

I am trying to perform a relatively simple SELECT query. Firstly heres my 3 tables im working on:

CREATE TABLE business_contact
(
BusContactID INT NOT NULL AUTO_INCREMENT,
Title VARCHAR(5),
Surname VARCHAR(30),
FirstName VARCHAR(30),
PRIMARY KEY (BusContactID)
) TYPE = INNODB;

CREATE TABLE company
(
CompanyID INT NOT NULL AUTO_INCREMENT,
Name VARCHAR(50) NOT NULL,
Manager VARCHAR(25),
PRIMARY KEY (CompanyID)
) TYPE = INNODB;

CREATE TABLE works_for
(
CompanyID INT NOT NULL,
BusContactID INT NOT NULL,
Index (CompanyID),
FOREIGN KEY (CompanyID) REFERENCES company (CompanyID) ON UPDATE CASCADE ON DELETE CASCADE,
Index (BusContactID),
FOREIGN KEY (BusContactID) REFERENCES business_contact (BusContactID) ON UPDATE CASCADE ON DELETE CASCADE,
PRIMARY KEY (CompanyID, BusContactID)
) TYPE = INNODB;

The 'company' table quite obviously stores details about a range of companies, the 'business_contact' about business contacts and the 'works_for' table uses the PK values from the previous tables to associate a contact with a particular employer.

What i want to do is to retrieve a list of all the contacts and (if applicable) the name of the company they work for. My knowledge of SQL is relatively limited and so far ive managed to retrieve the all the contact and the where applicable, the key value of the company a contact works for. So all i really need to do is replace the key value with the company name, but, i dont know how!! :confused:

Heres my current query:

SELECT business_contact.*, works_for.buscontactid
FROM business_contact
LEFT JOIN works_for
ON business_contact.buscontactid = works_for.buscontactid

Can anyone help me with fetching all the contacts in the table and if the contact works for a company then listing the name of the company?!?!?

Thanks in advance to anyone who can help

Damocles.SELECT business_contact.*, company.name
FROM business_contact
LEFT JOIN works_for
ON business_contact.buscontactid = works_for.buscontactid
LEFT JOIN company
ON works_for.companyid = company.companyid|||select business_contact.Title
, business_contact.Surname
, business_contact.FirstName
, company.Name
, company.Manager
from business_contact
left outer
join works_for
on business_contact.BusContactID
= works_for.BusContactID
left outer
join company
on works_for.CompanyID
= company.CompanyID|||Thanks to both for your replies, works great!

I could really do to read a decent SQL tutorial at some point!

Damocles.

Relative Performance: Native SQL vs User Functions

Hi all,
I'm interested in knowing, for a fact, what the performance overhead of
using a functional-language based query e.g.
select * from customers where dbo.ufn_stringcontains(custname, 'ar')=1
is over a conventional SQL statement:
select * from addrb4 where custname like '%'+'ar'+'%'
From the execution plan in my SQL2000 query analyser, it shows the
functional query is slightly better costing the batch of 49.97%. However,
both queries take 28 seconds to execute. I was expecting the functional
query to be slower so I'm a bit surprised.
I know the perfomance of the functional query depends on how complex the
function is. In my applications, the functions will be quite simple and where
they will be complex, a convenctional equivalent will equally complex to
build.
Your opinions, experience and test runs are welcome.
--
NB: declaration for ufn_stringcontains:
create function ufn_stringontains (@.m varchar(8000) , @.s varchar(8000) )
returns bit
as
begin
declare @.ret bit
if (@.m like '%'+@.s+'%')
set @.ret=1
else
set @.ret=0
return @.ret
endBecause of the like you are using, a table scan in involved, resulting in
mostly IO.
Try running the same query and function, but using only like to the right of
the search string (select * from addrb4 where custname like 'ar'+'%') and
have an index on custname.
You will find that the difference becomes clearer as soon as more records
can be eliminated by a index seek or scan.
Functions are faster sometimes, and sometimes they destroy performance. It
varies and there is no hard and fast rule.
Regards
Mike
"Sienko" wrote:
> Hi all,
> I'm interested in knowing, for a fact, what the performance overhead of
> using a functional-language based query e.g.
> select * from customers where dbo.ufn_stringcontains(custname, 'ar')=1
> is over a conventional SQL statement:
> select * from addrb4 where custname like '%'+'ar'+'%'
> From the execution plan in my SQL2000 query analyser, it shows the
> functional query is slightly better costing the batch of 49.97%. However,
> both queries take 28 seconds to execute. I was expecting the functional
> query to be slower so I'm a bit surprised.
> I know the perfomance of the functional query depends on how complex the
> function is. In my applications, the functions will be quite simple and where
> they will be complex, a convenctional equivalent will equally complex to
> build.
> Your opinions, experience and test runs are welcome.
> --
> NB: declaration for ufn_stringcontains:
> create function ufn_stringontains (@.m varchar(8000) , @.s varchar(8000) )
> returns bit
> as
> begin
> declare @.ret bit
> if (@.m like '%'+@.s+'%')
> set @.ret=1
> else
> set @.ret=0
> return @.ret
> end
>|||"Sienko" <Sienko@.discussions.microsoft.com> wrote in message
news:0ABAE066-2E11-4679-9AAA-8E9B8A636DD2@.microsoft.com...
> create function ufn_stringontains (@.m varchar(8000) , @.s varchar(8000) )
> returns bit
> as
> begin
> declare @.ret bit
> if (@.m like '%'+@.s+'%')
> set @.ret=1
> else
> set @.ret=0
> return @.ret
> end
You could speed it up a tad by doing early returns:
if (@.m like '%'+@.s+'%' )
return 1
else
return 0
Personally, I find this style easier to understand, as well.

Relative Performance: Native SQL vs User Functions

Hi all,
I'm interested in knowing, for a fact, what the performance overhead of
using a functional-language based query e.g.
select * from customers where dbo.ufn_stringcontains(custname, 'ar')=1
is over a conventional SQL statement:
select * from addrb4 where custname like '%'+'ar'+'%'
From the execution plan in my SQL2000 query analyser, it shows the
functional query is slightly better costing the batch of 49.97%. However,
both queries take 28 seconds to execute. I was expecting the functional
query to be slower so I'm a bit surprised.
I know the perfomance of the functional query depends on how complex the
function is. In my applications, the functions will be quite simple and wher
e
they will be complex, a convenctional equivalent will equally complex to
build.
Your opinions, experience and test runs are welcome.
NB: declaration for ufn_stringcontains:
create function ufn_stringontains (@.m varchar(8000) , @.s varchar(8000) )
returns bit
as
begin
declare @.ret bit
if (@.m like '%'+@.s+'%')
set @.ret=1
else
set @.ret=0
return @.ret
endBecause of the like you are using, a table scan in involved, resulting in
mostly IO.
Try running the same query and function, but using only like to the right of
the search string (select * from addrb4 where custname like 'ar'+'%') and
have an index on custname.
You will find that the difference becomes clearer as soon as more records
can be eliminated by a index seek or scan.
Functions are faster sometimes, and sometimes they destroy performance. It
varies and there is no hard and fast rule.
Regards
Mike
"Sienko" wrote:

> Hi all,
> I'm interested in knowing, for a fact, what the performance overhead of
> using a functional-language based query e.g.
> select * from customers where dbo.ufn_stringcontains(custname, 'ar')=1
> is over a conventional SQL statement:
> select * from addrb4 where custname like '%'+'ar'+'%'
> From the execution plan in my SQL2000 query analyser, it shows the
> functional query is slightly better costing the batch of 49.97%. However,
> both queries take 28 seconds to execute. I was expecting the functional
> query to be slower so I'm a bit surprised.
> I know the perfomance of the functional query depends on how complex the
> function is. In my applications, the functions will be quite simple and wh
ere
> they will be complex, a convenctional equivalent will equally complex to
> build.
> Your opinions, experience and test runs are welcome.
> --
> NB: declaration for ufn_stringcontains:
> create function ufn_stringontains (@.m varchar(8000) , @.s varchar(8000) )
> returns bit
> as
> begin
> declare @.ret bit
> if (@.m like '%'+@.s+'%')
> set @.ret=1
> else
> set @.ret=0
> return @.ret
> end
>|||"Sienko" <Sienko@.discussions.microsoft.com> wrote in message
news:0ABAE066-2E11-4679-9AAA-8E9B8A636DD2@.microsoft.com...

> create function ufn_stringontains (@.m varchar(8000) , @.s varchar(8000) )
> returns bit
> as
> begin
> declare @.ret bit
> if (@.m like '%'+@.s+'%')
> set @.ret=1
> else
> set @.ret=0
> return @.ret
> end
You could speed it up a tad by doing early returns:
if (@.m like '%'+@.s+'%' )
return 1
else
return 0
Personally, I find this style easier to understand, as well.|||The problem is mostly in how you use them. You could inappropriately use a
system function as well and in this case the wildcard prefix only makes the
problem worse.
For example:
SELECT <something>
FROM <some table> AS t1
WHERE DAY(DATEADD(day, 1, t1.<some date column> )) = 12
These are system supplied functions but this query is going to be costly reg
ardless if the date column is indexed or not. User defined functions are no
exception.
In your query, not only are you doing a lousy string search using the wildca
rd prefix, you are attempting to use a function to encapsulate a criteria.
You shouldn't see any difference in performance at all. In this case, the h
aystack is going to swamp in needle differences between straw.
Sincerely,
Anthony Thomas
--
"Sienko" <Sienko@.discussions.microsoft.com> wrote in message news:0ABAE066
-2E11-4679-9AAA-8E9B8A636DD2@.microsoft.com...
Hi all,
I'm interested in knowing, for a fact, what the performance overhead of
using a functional-language based query e.g.
select * from customers where dbo.ufn_stringcontains(custname, 'ar')=1
is over a conventional SQL statement:
select * from addrb4 where custname like '%'+'ar'+'%'
From the execution plan in my SQL2000 query analyser, it shows the
functional query is slightly better costing the batch of 49.97%. However,
both queries take 28 seconds to execute. I was expecting the functional
query to be slower so I'm a bit surprised.
I know the perfomance of the functional query depends on how complex the
function is. In my applications, the functions will be quite simple and wh
ere
they will be complex, a convenctional equivalent will equally complex to
build.
Your opinions, experience and test runs are welcome.
--
NB: declaration for ufn_stringcontains:
create function ufn_stringontains (@.m varchar(8000) , @.s varchar(8000) )
returns bit
as
begin
declare @.ret bit
if (@.m like '%'+@.s+'%')
set @.ret=1
else
set @.ret=0
return @.ret
endsql

Relative Dates

I need to create an SQL Statement that pulls data selected by a datetime
field. i want to be able to select dates and times relative to the time that
the query is run.
Can these date manipulations be done in the SQL statement?
Thanks
Have a look at DateDiff in BOL. Here is an example from SQL Server 2000 BOL:
USE pubs
GO
SELECT DATEDIFF(day, pubdate, getdate()) AS no_of_days
FROM titles
GO
GetDate() will allow you to compare to the time the query was run.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
|||i am not sure how i would use thet for waht i want, basically to start with i
need to get all records that were created TODAY. then go on to records
created within the last n days
"Paul Ibison" wrote:

> Have a look at DateDiff in BOL. Here is an example from SQL Server 2000 BOL:
> USE pubs
> GO
> SELECT DATEDIFF(day, pubdate, getdate()) AS no_of_days
> FROM titles
> GO
> GetDate() will allow you to compare to the time the query was run.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>
>
|||Hi Mark,
something like this should do it:
SELECT cols FROM yourtable
where DATEDIFF(day, pubdate, getdate()) = 0
SELECT cols FROM yourtable
where DATEDIFF(day, pubdate, getdate()) <= n
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
|||>i am not sure how i would use thet for waht i want, basically to start with
>i
> need to get all records that were created TODAY. then go on to records
> created within the last n days
Expanding on Paul's example, if you want to consider date and time:
DECLARE @.n int
SET @.n = 7
SELECT title
FROM titles
WHERE pubdate >= DATEADD(day, @.n * -1, GETDATE())
To consider date only:
DECLARE @.n int
SET @.n = 7
SELECT title
FROM titles
WHERE pubdate >= DATEADD(day, @.n * -1, DATEDIFF(day, 0, GETDATE())
Hope this helps.
Dan Guzman
SQL Server MVP
"Mark Shields" <MarkShields@.discussions.microsoft.com> wrote in message
news:2EE795F5-DA9D-4E51-A78B-57C929EDAE6B@.microsoft.com...[vbcol=seagreen]
>i am not sure how i would use thet for waht i want, basically to start with
>i
> need to get all records that were created TODAY. then go on to records
> created within the last n days
> "Paul Ibison" wrote:
sql

Relative Dates

I need to create an SQL Statement that pulls data selected by a datetime
field. i want to be able to select dates and times relative to the time that
the query is run.
Can these date manipulations be done in the SQL statement?
ThanksHave a look at DateDiff in BOL. Here is an example from SQL Server 2000 BOL:
USE pubs
GO
SELECT DATEDIFF(day, pubdate, getdate()) AS no_of_days
FROM titles
GO
GetDate() will allow you to compare to the time the query was run.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||i am not sure how i would use thet for waht i want, basically to start with i
need to get all records that were created TODAY. then go on to records
created within the last n days
"Paul Ibison" wrote:
> Have a look at DateDiff in BOL. Here is an example from SQL Server 2000 BOL:
> USE pubs
> GO
> SELECT DATEDIFF(day, pubdate, getdate()) AS no_of_days
> FROM titles
> GO
> GetDate() will allow you to compare to the time the query was run.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>
>|||Hi Mark,
something like this should do it:
SELECT cols FROM yourtable
where DATEDIFF(day, pubdate, getdate()) = 0
SELECT cols FROM yourtable
where DATEDIFF(day, pubdate, getdate()) <= n
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||>i am not sure how i would use thet for waht i want, basically to start with
>i
> need to get all records that were created TODAY. then go on to records
> created within the last n days
Expanding on Paul's example, if you want to consider date and time:
DECLARE @.n int
SET @.n = 7
SELECT title
FROM titles
WHERE pubdate >= DATEADD(day, @.n * -1, GETDATE())
To consider date only:
DECLARE @.n int
SET @.n = 7
SELECT title
FROM titles
WHERE pubdate >= DATEADD(day, @.n * -1, DATEDIFF(day, 0, GETDATE())
Hope this helps.
Dan Guzman
SQL Server MVP
"Mark Shields" <MarkShields@.discussions.microsoft.com> wrote in message
news:2EE795F5-DA9D-4E51-A78B-57C929EDAE6B@.microsoft.com...
>i am not sure how i would use thet for waht i want, basically to start with
>i
> need to get all records that were created TODAY. then go on to records
> created within the last n days
> "Paul Ibison" wrote:
>> Have a look at DateDiff in BOL. Here is an example from SQL Server 2000
>> BOL:
>> USE pubs
>> GO
>> SELECT DATEDIFF(day, pubdate, getdate()) AS no_of_days
>> FROM titles
>> GO
>> GetDate() will allow you to compare to the time the query was run.
>> Cheers,
>> Paul Ibison SQL Server MVP, www.replicationanswers.com
>>

Relative Dates

I need to create an SQL Statement that pulls data selected by a datetime
field. i want to be able to select dates and times relative to the time that
the query is run.
Can these date manipulations be done in the SQL statement?
ThanksHave a look at DateDiff in BOL. Here is an example from SQL Server 2000 BOL:
USE pubs
GO
SELECT DATEDIFF(day, pubdate, getdate()) AS no_of_days
FROM titles
GO
GetDate() will allow you to compare to the time the query was run.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||i am not sure how i would use thet for waht i want, basically to start with
i
need to get all records that were created TODAY. then go on to records
created within the last n days
"Paul Ibison" wrote:

> Have a look at DateDiff in BOL. Here is an example from SQL Server 2000 BO
L:
> USE pubs
> GO
> SELECT DATEDIFF(day, pubdate, getdate()) AS no_of_days
> FROM titles
> GO
> GetDate() will allow you to compare to the time the query was run.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>
>|||Hi Mark,
something like this should do it:
SELECT cols FROM yourtable
where DATEDIFF(day, pubdate, getdate()) = 0
SELECT cols FROM yourtable
where DATEDIFF(day, pubdate, getdate()) <= n
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||>i am not sure how i would use thet for waht i want, basically to start with
>i
> need to get all records that were created TODAY. then go on to records
> created within the last n days
Expanding on Paul's example, if you want to consider date and time:
DECLARE @.n int
SET @.n = 7
SELECT title
FROM titles
WHERE pubdate >= DATEADD(day, @.n * -1, GETDATE())
To consider date only:
DECLARE @.n int
SET @.n = 7
SELECT title
FROM titles
WHERE pubdate >= DATEADD(day, @.n * -1, DATEDIFF(day, 0, GETDATE())
Hope this helps.
Dan Guzman
SQL Server MVP
"Mark Shields" <MarkShields@.discussions.microsoft.com> wrote in message
news:2EE795F5-DA9D-4E51-A78B-57C929EDAE6B@.microsoft.com...[vbcol=seagreen]
>i am not sure how i would use thet for waht i want, basically to start with
>i
> need to get all records that were created TODAY. then go on to records
> created within the last n days
> "Paul Ibison" wrote:
>

reinitialize replication

Hi All,
I select the wrong database to do merge replication. Trying to delete the
replication I delete the suscription and the publication but still
replication folder in my database and all my tables have the extra columns.
How can I delete the replication from my database so I can start all over
this the right database?
Tks in advance, rgds.
Johnny
You will have to drop ROWGUID columns manually.
Regards,
Kestutis Adomavicius
Consultant
UAB "Baltic Software Solutions"
"JFB" <jfb@.newSQL.com> wrote in message
news:%23JQqZY62EHA.4072@.TK2MSFTNGP10.phx.gbl...
> Hi All,
> I select the wrong database to do merge replication. Trying to delete the
> replication I delete the suscription and the publication but still
> replication folder in my database and all my tables have the extra
columns.
> How can I delete the replication from my database so I can start all over
> this the right database?
> Tks in advance, rgds.
> Johnny
>
|||Ok, what about the Publications folder inside the database, the extra system
tables and the blue hand in the enterprise manager.
Can I delete this?
Tks for you reply
Johnny
"Kestutis Adomavicius" <kicker.lt@.nospaamm_tut.by> wrote in message
news:eT5aEc62EHA.3452@.TK2MSFTNGP14.phx.gbl...
> You will have to drop ROWGUID columns manually.
> --
> Regards,
> Kestutis Adomavicius
> Consultant
> UAB "Baltic Software Solutions"
>
> "JFB" <jfb@.newSQL.com> wrote in message
> news:%23JQqZY62EHA.4072@.TK2MSFTNGP10.phx.gbl...
> columns.
>
|||run this script in the database.
http://groups-beta.google.com/group/...a?dmode=source
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"JFB" <jfb@.newSQL.com> wrote in message
news:%23JQqZY62EHA.4072@.TK2MSFTNGP10.phx.gbl...
> Hi All,
> I select the wrong database to do merge replication. Trying to delete the
> replication I delete the suscription and the publication but still
> replication folder in my database and all my tables have the extra
> columns.
> How can I delete the replication from my database so I can start all over
> this the right database?
> Tks in advance, rgds.
> Johnny
>
|||Tks for you reply and help Hilary,
I run the script and I got this result with some errors: Can you help me to
fix this?
Rgds
Johnny
**********************************8
Server: Msg 208, Level 16, State 1, Line 3
Invalid object name 'syspublications'.
(0 row(s) affected)
(0 row(s) affected)
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'syssubscriptions'.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'sysarticleupdates'.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'systranschemas'.
(0 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'sysarticles'.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'sysschemaarticles'.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'syspublications'.
(0 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'MSpub_identity_range'.
(0 row(s) affected)
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'MSreplication_subscriptions'.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'MSsubscription_agents'.
Server: Msg 259, Level 16, State 2, Line 2
Ad hoc updates to system catalogs are not enabled. The system administrator
must reconfigure SQL Server to allow this.
Server: Msg 259, Level 16, State 2, Line 1
Ad hoc updates to system catalogs are not enabled. The system administrator
must reconfigure SQL Server to allow this.
Server: Msg 3701, Level 11, State 5, Line 1
Cannot drop the view 'sysextendedarticlesview', because it does not exist in
the system catalog.
Server: Msg 259, Level 16, State 2, Line 1
Ad hoc updates to system catalogs are not enabled. The system administrator
must reconfigure SQL Server to allow this.
dropping rowguid constraints MSmerge_delete_conflicts
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OYi$Rg82EHA.2112@.TK2MSFTNGP15.phx.gbl...
> run this script in the database.
> http://groups-beta.google.com/group/...a?dmode=source
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> Now available for purchase at:
> http://www.nwsu.com/0974973602.html
> "JFB" <jfb@.newSQL.com> wrote in message
> news:%23JQqZY62EHA.4072@.TK2MSFTNGP10.phx.gbl...
>

Regular expressions in Sql Server?

Hi,

I'm using MS SQL Server 2000 and am trying to execute a select where I want a column to match this regular expression:

((\w)*\|)*$KEY(\|(\w)*)*

as would be defined in Perl (btw, the "|" is a pipe, not an L :))

$KEY would be subsituted by some text I'm querying for.

I tried using LIKE with "[(%|)[]]$KEY[[](|%)]", eg:

select * from mytable m
where m.a like '[(%|)[]]$KEY[[](|%)]'

but it didn't work (again, the $KEY is replaced with text when I'm executing. I just have it here for an example). Is there any way I could get around this problem? I would really appreciate any help.

Thanksthe best you will do is "%|$Key|%", ie, a string that starts with something, has a pipe char, the string you are looking for, a pipe char, ending with something.

I believe "\|" in perl is how you specify a literal "|".

Regular expressions are not supported in TSQL! There are some simple expressions but not what a perl or python programmer is used to.

regular expression issue

I am trying to exclude all strings that has 'a' inside (I have
simplified the actual problem)
select 1
where 'bb b a dfg' like '%[^a]%'

However, the above does not work. By the way, I can not use 'not like'
such as:

select 1
where 'bb b a dfg' not like '%a%'
Although the above will work but the idea is that I have to use 'like'
and not 'not like'. This is partly because I have to exclude rows from
an exclusion table (a table that has many rows that will be excluded).
Actually I want to include all srings that has lets say // in it using
a regular expression. I would like to write it as (I am sure it will
not work):
select 1
where column like '%[^/][^/]%'

That should exclude strings like: 'aaa // aa aa' or 'bb bbb // bb' etc
and include strings like: 'aaa aa aa' or 'bb aa nn' etc

Is there any way to write a regular expression to do it? Otherrwise I
have to solve this problem without using regular expressions in the
exclusion table.
Thanks.The problem you pose requires NOT LIKE. There is no way to express a
LIKE string with the "not" test inside that will do what was
specified. The NOT has to be outside.

Roy Harvey
Beacon Falls, CT

On 7 Nov 2006 21:07:01 -0800, othellomy@.yahoo.com wrote:

Quote:

Originally Posted by

>I am trying to exclude all strings that has 'a' inside (I have
>simplified the actual problem)
>select 1
>where 'bb b a dfg' like '%[^a]%'
>
>However, the above does not work. By the way, I can not use 'not like'
>such as:
>
>select 1
>where 'bb b a dfg' not like '%a%'
>Although the above will work but the idea is that I have to use 'like'
>and not 'not like'. This is partly because I have to exclude rows from
>an exclusion table (a table that has many rows that will be excluded).
>Actually I want to include all srings that has lets say // in it using
>a regular expression. I would like to write it as (I am sure it will
>not work):
>select 1
>where column like '%[^/][^/]%'
>
>That should exclude strings like: 'aaa // aa aa' or 'bb bbb // bb' etc
>and include strings like: 'aaa aa aa' or 'bb aa nn' etc
>
>Is there any way to write a regular expression to do it? Otherrwise I
>have to solve this problem without using regular expressions in the
>exclusion table.
>Thanks.