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.

Related Tags

I have a "tagged" aplication, my model is something like:
-Item-
Id_Item
Item_Name
-Tag-
Id_Tag
Tag_Name
-Item-Tag-
Id_Item
Id_Tag
I want a stored procedure that returns me all items names in a column
and in other column all tags (separated by commas)
Item_Name Item_Tags
C#Course C#, .NET, Tutorial, Programming
Can you give some advise of how to implement it?
Best Regards
Fabio CavassiniHello, Fabio
See: http://www.aspfaq.com/show.asp?id=2529
Razvan

re-Index advice

We periodically rebuild our indexes when we notice defragmentation of
indexes however after rebuilding the indexes we do not recompile the
stored procedure. My experience has been that after rebuiling the
indexes we ususlly notice a performance gain right away however I was
wondering if it is also necessary to recompile all our stored
procedures after the indexes are rebuilt. What is the best practise?
ThanksWhen you reindex an index it will by default update the stats which
invalidate the plans for anything that references them. That in turn will
force the recompile of the plan the next time it is used. So in short you do
not need to recompile after an index rebuild as it is already done for you.
--
Andrew J. Kelly SQL MVP
"shub" <shubtech@.gmail.com> wrote in message
news:1154619914.120880.28460@.b28g2000cwb.googlegroups.com...
> We periodically rebuild our indexes when we notice defragmentation of
> indexes however after rebuilding the indexes we do not recompile the
> stored procedure. My experience has been that after rebuiling the
> indexes we ususlly notice a performance gain right away however I was
> wondering if it is also necessary to recompile all our stored
> procedures after the indexes are rebuilt. What is the best practise?
> Thanks
>|||shub wrote:
> We periodically rebuild our indexes when we notice defragmentation of
> indexes however after rebuilding the indexes we do not recompile the
> stored procedure. My experience has been that after rebuiling the
> indexes we ususlly notice a performance gain right away however I was
> wondering if it is also necessary to recompile all our stored
> procedures after the indexes are rebuilt. What is the best practise?
> Thanks
>
Defragging the indexes shouldn't require recompiling anything, unless
the indexes were so badly fragmented that the optimize was ignoring them
and using a sub-optimal execution plan. I personally have never had to
do this.
You don't say how you "notice" fragmentation, but you might consider an
automated approach that runs nightly or weekly. See my site for one
possible method:
http://realsqlguy.com/twiki/bin/view/RealSQLGuy/DefragIndexesAsNeeded
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thanks Andrew that makes sense. What would be the best way to check to
make sure that the stored proc's are indeed getting recompiled?
Thanks
shub wrote:
> We periodically rebuild our indexes when we notice defragmentation of
> indexes however after rebuilding the indexes we do not recompile the
> stored procedure. My experience has been that after rebuiling the
> indexes we ususlly notice a performance gain right away however I was
> wondering if it is also necessary to recompile all our stored
> procedures after the indexes are rebuilt. What is the best practise?
> Thanks|||Well it is not something one generally has to check on. If you know the
stats are getting updated (you can see with DBCC SHOWSTTISTICS) then you can
count on any plans that reference those stats to get recompiled the next
time they are run. This is true in all cases unless you have used the KEEP
PLAN or KEEP FIXED PLAN hints in the query.
--
Andrew J. Kelly SQL MVP
"shub" <shubtech@.gmail.com> wrote in message
news:1154694889.877119.181210@.p79g2000cwp.googlegroups.com...
> Thanks Andrew that makes sense. What would be the best way to check to
> make sure that the stored proc's are indeed getting recompiled?
> Thanks
>
> shub wrote:
>> We periodically rebuild our indexes when we notice defragmentation of
>> indexes however after rebuilding the indexes we do not recompile the
>> stored procedure. My experience has been that after rebuiling the
>> indexes we ususlly notice a performance gain right away however I was
>> wondering if it is also necessary to recompile all our stored
>> procedures after the indexes are rebuilt. What is the best practise?
>> Thanks
>

registry access

Dear Professional
Through Sql Server Stored procedure can I fetch the userid and password
which saved in Registry.
Thanks
NOOR
hi Noor,
"Noor" <noor@.ngsol.com> ha scritto nel messaggio
news:%23SKzZyyuEHA.1264@.TK2MSFTNGP12.phx.gbl
> Dear Professional
> Through Sql Server Stored procedure can I fetch the userid and
> password which saved in Registry.
> Thanks
> NOOR
you can have a look at xp_regread undocumented strored procedure...
http://www.mssqlcity.com/FAQ/Devel/xp_regread.htm
please note that undocumented features can be modified/dropped at any time,
without notification... and there's no support for them
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

Registering assembly in SQL Server 2005

Hi,

I have created a C# stored procedure that has a typed dataset (XSD). When I try register that assembly in SQL Server 2005, I am getting the following error.

CREATE ASSEMBLY failed because method "add_InvoiceDTRowChanging" on type "InvoiceDTDataTable" in external_access assembly "SS.StoredProc" has a synchronized attribute. Explicit synchronization is not allowed in external_access assemblies. (Microsoft SQL Server, Error: 6213)

I am using SEP CTP for both VS and SQL Server. I do not have any app.config file in my project. Can anyone help me resolve this?

Thanks,
Prakash

Exactly as the error messsage says, the assembly you try to catalogue contains something that is not allowed under the permission set you try to do it under. In this case it looks to be the method add_InvoiceDTRowChanging in the class for one of the tables. If you look at that method you should see it has (probably) a HostProtectionAttribute with a property taking about synchronization. And, as the error mesage says, this is not allowed in a assembly set for EXTERNAL ACCESS. Change the permission set to be UNSAFE, and you shoudl be OK.

Notice that when you set UNSAFE you may have to mark your database as TRUSTWORTHY and assign the owner of the database UNSAFE PERMISSION rights.

Niels
|||Hi Neil,

Thanks for your insight. The problem I am having is, in the class I dont have any method that reads as add_InvoiceDTRowChanging. Also I did checked for the word 'Host' through out my solution and I dont find this word.

Thanks,
Prakash|||Hi Prakash,
I believe that in your stored procedure you have a CLR Event of some kind. The add_InvoiceDTRowChanging method is created automatically for you as part of the Event Handler. Unfortunately, the way that the CLR currently handles Event synchronization is not allowed under SAFE or EXTERNAL_ACCESS assemblies. Hopefully this will be fixed in a future release. Your assembly should work under the UNSAFE permission set.
Steven
|||

Hi Steven,

When registering with UNSAFE, I successfully added the Assembly. When I try to create a instance of the typed dataset in my c# stored procedure, I am getting the following error:

Msg 6522, Level 16, State 1, Procedure InsertInvoice, Line 0
A .NET Framework error occurred during execution of user defined routine or aggregate 'InsertInvoice':
System.InvalidOperationException: Cannot load dynamically generated serialization assembly. In some hosting environments assembly load functionality is restricted, consider using pre-generated serializer. Please see inner exception for more information. > System.IO.FileLoadException: LoadFrom(), LoadFile(), Load(byte[]) and LoadModule() have been disabled by the host.
System.IO.FileLoadException:
at System.Reflection.Assembly.nLoadImage(Byte[] rawAssembly, Byte[] rawSymbolStore, Evidence evidence, StackCrawlMark& stackMark, Boo
...
System.InvalidOperationException:
at System.Xml.Serialization.Compiler.Compile(Assembly parent, String ns, CompilerParameters parameters, Evidence evidence)
at System.Xml.Serialization.TempAssembly.GenerateAssembly(XmlMapping[] xmlMappings, Type[] types, String defaultNamespace, Evidence evidence, CompilerParameters parameters, Assembly assembly, Hashtable assemblies)
at System.Xml.Serialization.TempAssembly..ctor(XmlMapping[] xmlMappings, Type[] types, String defaultNamespace, String location, Evidence evidence)
at System.Xml.Serialization.XmlSerializer.GenerateTempAssembly(XmlMapping xmlMapping, Type type, String defaultNamespace, String location, Evidence evidence)
at System.Xml.Serialization.XmlSerializer..ctor(Type type, XmlAttributeOverrides overrides, Type[] extraTypes, XmlRootAttribute root, String defaultNamespace, String location, Evidence evidence)
at System.Xml.Serialization.XmlSerializer..ctor(Type type, XmlRootAttribute root)
at System.Data.Common.ObjectStorage.ConvertXmlToObject(XmlReader xmlReader, XmlRootAttribute xmlAttrib)
at System.Data.XmlDataLoader.LoadColumn(DataColumn column, Object[] foundColumns)
at System.Data.XmlDataLoader.LoadTable(DataTable table, Boolean isNested)
at System.Data.XmlDataLoader.LoadData(XmlReader reader)
at System.Data.DataSet.ReadXml(XmlReader reader, Boolean denyResolving)
...

When I tried to create a new class and just copy pasted the same code that was generated by the typed data set, I was able to create a new instance of that class. Why I cannot create instance of a typed dataset?

Thanks,
Prakash

|||This just works like a charm with the Visual Studio RTM bits

Thanks,
Prakash|||I'm glad you got it to work. As you found, Visual Studio RTM deployment takes care of this automatically for you.
However, if you're doing this outside of Visual Studio or with beta bits, then you need to generate the Xml Serialization assembly yourself using sgen. How to do this is described in the following blog posting:
http://blogs.msdn.com/sqlclr/archive/2005/07/25/Vineet.aspx
Thanks,
Steven|||Hi Steven,

I got into another trap now. When I try to create a new instance of StringReader class in C# stored proc I am getting the same error. I am passing a XML string and I am trying to use the StringReader Class to populate the typed dataset. When I use the following code, I am running into the error.

StringReader sr = new StringReader(data);

Can you please help?

Thanks,
Prakash|||Actually this turns to be a BIG issue for me now. After careful examining, I found out that its all because of Generic type in my typed dataset. I have created a AddressCollection generics of type Address. The typed dataset has a column of datatype AddressCollection.

When I load the XML file that I get as parameter from UI, it cannot understand that type and its throwing exception. But I did created the AddressCollection assembly into the SQLServer with UNSAFE permission.

Anyone has any idea of dealing TypedDataset with Generics inside SQL Server stored procedure?

Thanks,
Prakash|||

Hi Prakash,

Generics are not currently supported under SQLCLR. However if you're not using them on the SQL side of things (eg, as a return value or parameter for your stored proc) then it shouldn't be throwing an error. If you attach your code or send it to me ( stevehem at microsoft dot com) I'll take a look.

Steven

|||<Steven Hemingray - MSFT@.discussions.microsoft..com> wrote in message news:381fd38d-a93c-49fd-8f28-c4680a22993c@.discussions.microsoft.com... Generics are not currently supported under SQLCLR. However if you're not using them on the SQL side of things (eg, as a return value or Hi Steven, Can you clarify this statement? Do you mean that absolutely no use of generics is supported within SQLCLR routines? Including use of generic collections, etc? Or am I taking this too far out of context? -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457--|||

What I meant was that using generics inside SQLCLR is fine as long as they're not visible to the T-SQL side. This is the same restriction as on many .NET objects (arrays, regex, etc.) where you can use them all you want on the CLR side, but in order to interface with the T-SQL side you need to wrap them in constructs that T-SQL can understand.

In response to Prakash, I was trying to determine why the use of generics would throw a .NET serialization exception and not one of the 'generics are not supported' error messages .

Registering a SP in master?

(SQL Server 2000, SP3a)
Hello all!
A while ago I seem to recall a post that talked about the possibility of registering a
stored procedure in the master database, such that it became a "global" procedure with
public scope without having to prefix the execution with "master". However, I can't seem
to figure out how to perform this registration process.
Any ideas?
John PetersonAny procedure in master, that is owned by dbo, and begins with sp_ will be
accessible from any database without specifying the db or owner name.
However, you still must grant permissions to those users who are allowed to
execute the procedure.
USE Master
CREATE PROC sp_myproc
AS ...
RETURN
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:#9qNHMdkDHA.1284@.TK2MSFTNGP09.phx.gbl...
> (SQL Server 2000, SP3a)
> Hello all!
> A while ago I seem to recall a post that talked about the possibility of
registering a
> stored procedure in the master database, such that it became a "global"
procedure with
> public scope without having to prefix the execution with "master".
However, I can't seem
> to figure out how to perform this registration process.
> Any ideas?
> John Peterson
>|||Ah, gotcha! So kind of by virtue of residing in master and having the "sp_" prefix and
being owned by dbo, it'll be accessible to all other databases without having to
explicitly prefix the database or owner name. Thanks, Kalen! (And thanks for the
reminder about the permissions!)
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:u3G8bTdkDHA.2772@.TK2MSFTNGP12.phx.gbl...
> Any procedure in master, that is owned by dbo, and begins with sp_ will be
> accessible from any database without specifying the db or owner name.
> However, you still must grant permissions to those users who are allowed to
> execute the procedure.
> USE Master
> CREATE PROC sp_myproc
> AS ...
> RETURN
>
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:#9qNHMdkDHA.1284@.TK2MSFTNGP09.phx.gbl...
> > (SQL Server 2000, SP3a)
> >
> > Hello all!
> >
> > A while ago I seem to recall a post that talked about the possibility of
> registering a
> > stored procedure in the master database, such that it became a "global"
> procedure with
> > public scope without having to prefix the execution with "master".
> However, I can't seem
> > to figure out how to perform this registration process.
> >
> > Any ideas?
> >
> > John Peterson
> >
> >
>|||Yes, sp_ actually stands for 'special' because it gives your objects a
special behavior. :-)
Another note on permissions... permissions are granted to users, and most
database logins do not have specific user names in master. So your choices
really are between not granting permission to anyone, so only 'sa' can use
the procedure, and granting to public, so everyone can. Most of the system
procs (sp_help%, etc) have execute granted to public.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:ujiLi6dkDHA.2068@.TK2MSFTNGP09.phx.gbl...
> Ah, gotcha! So kind of by virtue of residing in master and having the
"sp_" prefix and
> being owned by dbo, it'll be accessible to all other databases without
having to
> explicitly prefix the database or owner name. Thanks, Kalen! (And thanks
for the
> reminder about the permissions!)
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:u3G8bTdkDHA.2772@.TK2MSFTNGP12.phx.gbl...
> > Any procedure in master, that is owned by dbo, and begins with sp_ will
be
> > accessible from any database without specifying the db or owner name.
> > However, you still must grant permissions to those users who are allowed
to
> > execute the procedure.
> >
> > USE Master
> > CREATE PROC sp_myproc
> > AS ...
> > RETURN
> >
> >
> >
> > --
> > HTH
> > --
> > Kalen Delaney
> > SQL Server MVP
> > www.SolidQualityLearning.com
> >
> >
> > "John Peterson" <j0hnp@.comcast.net> wrote in message
> > news:#9qNHMdkDHA.1284@.TK2MSFTNGP09.phx.gbl...
> > > (SQL Server 2000, SP3a)
> > >
> > > Hello all!
> > >
> > > A while ago I seem to recall a post that talked about the possibility
of
> > registering a
> > > stored procedure in the master database, such that it became a
"global"
> > procedure with
> > > public scope without having to prefix the execution with "master".
> > However, I can't seem
> > > to figure out how to perform this registration process.
> > >
> > > Any ideas?
> > >
> > > John Peterson
> > >
> > >
> >
> >
>

Register dll

I would like to register the cpx.dll as an external stored procedure for
"xp_cpx.
Please let me the procedures to complete this task?
Thanks"Joe P." <Joe P.@.discussions.microsoft.com> wrote in message
news:5A5FCB65-476C-40B6-A1B7-0DD7409BC355@.microsoft.com...
> I would like to register the cpx.dll as an external stored procedure for
> "xp_cpx.
> Please let me the procedures to complete this task?
> Thanks
>
From the Books Online
Adding an Extended Stored Procedure to SQL Server
A DLL that contains extended stored procedure functions acts as an extension
to Microsoft® SQL ServerT. To install the DLL, copy the file to the
directory containing the standard SQL Server DLL files (C:\Program
Files\Microsoft SQL Server\Mssql\Binn by default).
To add each extended stored procedure function in an extended stored
procedure DLL, a SQL Server system administrator must run the
sp_addextendedproc system stored procedure, specifying the name of the
function and the name of the DLL in which that function resides. For
example, this command registers the function xp_hello, located in
xp_hello.dll, as a SQL Server extended stored procedure:
sp_addextendedproc 'xp_hello', 'xp_hello.dll'
It is not necessary to stop and restart SQL Server. This is in a sample only
available if you select Dev Tools during setup.

Register dll

I would like to register the cpx.dll as an external stored procedure for
"xp_cpx.
Please let me the procedures to complete this task?
Thanks
"Joe P." <Joe P.@.discussions.microsoft.com> wrote in message
news:5A5FCB65-476C-40B6-A1B7-0DD7409BC355@.microsoft.com...
> I would like to register the cpx.dll as an external stored procedure for
> "xp_cpx.
> Please let me the procedures to complete this task?
> Thanks
>
From the Books Online
Adding an Extended Stored Procedure to SQL Server
A DLL that contains extended stored procedure functions acts as an extension
to Microsoft SQL ServerT. To install the DLL, copy the file to the
directory containing the standard SQL Server DLL files (C:\Program
Files\Microsoft SQL Server\Mssql\Binn by default).
To add each extended stored procedure function in an extended stored
procedure DLL, a SQL Server system administrator must run the
sp_addextendedproc system stored procedure, specifying the name of the
function and the name of the DLL in which that function resides. For
example, this command registers the function xp_hello, located in
xp_hello.dll, as a SQL Server extended stored procedure:
sp_addextendedproc 'xp_hello', 'xp_hello.dll'
It is not necessary to stop and restart SQL Server. This is in a sample only
available if you select Dev Tools during setup.

Register dll

I would like to register the cpx.dll as an external stored procedure for
"xp_cpx.
Please let me the procedures to complete this task?
Thanks"Joe P." <Joe P.@.discussions.microsoft.com> wrote in message
news:5A5FCB65-476C-40B6-A1B7-0DD7409BC355@.microsoft.com...
> I would like to register the cpx.dll as an external stored procedure for
> "xp_cpx.
> Please let me the procedures to complete this task?
> Thanks
>
From the Books Online
Adding an Extended Stored Procedure to SQL Server
A DLL that contains extended stored procedure functions acts as an extension
to Microsoft SQL ServerT. To install the DLL, copy the file to the
directory containing the standard SQL Server DLL files (C:\Program
Files\Microsoft SQL Server\Mssql\Binn by default).
To add each extended stored procedure function in an extended stored
procedure DLL, a SQL Server system administrator must run the
sp_addextendedproc system stored procedure, specifying the name of the
function and the name of the DLL in which that function resides. For
example, this command registers the function xp_hello, located in
xp_hello.dll, as a SQL Server extended stored procedure:
sp_addextendedproc 'xp_hello', 'xp_hello.dll'
It is not necessary to stop and restart SQL Server. This is in a sample only
available if you select Dev Tools during setup.