Registering Error: 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: 
</tt>
<blockquote><tt>select blah from products p</tt> 
<tt>where exists (select * from orders o where o.productID =p.productID)</tt> 
</blockquote>
<tt>or 
</tt>
<blockquote><tt>select blah from products p</tt> 
<tt>where productID in (select productID from orders o where customer
= 'ACME Corp')</tt> 
</blockquote>
<tt> 
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 
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
-- 
 mike
hodgson | database administrator
| mallesons stephen jaques 
T +61 (2) 9296 3668 | F +61
(2) 9296 3885 | M +61
(408) 675 907 
E
<a href="http://links.10026.com/?link=mailto:mike.hodgson@.mallesons.nospam.com">
mailto:mike.hodgson@.mallesons.nospam.com</a> | W <a href="http://links.10026.com/?link=/">http://www.mallesons.com">
http://www.mallesons.com</a> 
</div>
 
 
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.

Friday, March 30, 2012

Relatively new to SQL ...

No comments:

Post a Comment

Registering Error

Blog Archive

About Me