Is using a SP return value bad technique?

I need to write a query in an SP that returns either yes or no. Is it bad technique to use the return value of the SP for this? Should I use a scalar or output parameter instead? If so which?
Thanks,
SteveUsing a return value to return simple integer scalal values is *THE* way to do what you want.

Returning a scalar values using a recorset with just one row and one column is too expensive.

You cannot return "Yes" or "No" with return values anyway, just integers are allowed. If you need to return "yes" or "no" in a string format use output values.|||My fault. That was a complete lapse of brainpower on my part. What you described is exactly what I meant to say(either a 1 or 0 for true or false). Oh well. That's what I get for working on a Saturday.

Thanks,
Steve|||Using a return value to return simple integer scalal values is *THE* way to do what you want.

Absolutely not.

Use an ouput variable and leave the return code alone...

Even if you specify

Return -1

For example, SQL Server in some cases can and will override the value...

So if you code for it, it could be a problem.|||Brett I've never had any problem using return values. Even BOL doesn't mention it. That would be awful! :)

Anyway what i wanted to evidence is that returning as scalar value in a recordset is a bad idea. Some more info here:

http://www.sqlteam.com/item.asp?ItemID=2644|||Yeah, I remeber Bills article.

But it was after a long thread that I think Arnold or Nigel identied/explained the problem.

I then went on and posted an example of where the return value was over ridden, making an output variable the only safe way.

I should blog that one...|||Well, surely it'll be an interesting read. Please do it.

Btw this "feature" seems to be more a bug than anything else...isn't it?|||Here's the thread...

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=35642sql

Is this SQL stored prodcedure is valid

what i want to achive is the proc sh'd return a master-detail value in one go.
master value should be returned with Out Parameter and detail value as a recordset.

will it return the recordset of detail table as below...

Create Procedure ProductDetail
(
 @.ProductID int,
 @.ProductCode varchar(15) OUTPUT,
 @.ProductName varchar(60) OUTPUT,
 @.CategoryID int OUTPUT,
 @.CategoryName varchar(60) OUTPUT,
 @.Image1 varchar(256) OUTPUT,
 @.Image2 varchar(256) OUTPUT,
 @.UnitPrice smallmoney OUTPUT,
 @.UOMValue numeric(9) OUTPUT,
 @.UOMName varchar(10) OUTPUT,
 @.ShippingWeight numeric(9) OUTPUT,
 @.Directions varchar(1500) OUTPUT,
 @.Ingrediants varchar(1500) OUTPUT,
 @.Warnings varchar(1500) OUTPUT,
 @.ShortDescription varchar(1000) OUTPUT,
 @.LongDescription varchar(2000) OUTPUT,
 @.NutritionFacts varchar(1000) OUTPUT,
 @.SearchKeywords varchar(500) OUTPUT,
 @.IsTaxable varchar(15) OUTPUT,
 @.CreatedBy varchar(60) OUTPUT,
 @.CreatedOn varchar(15) OUTPUT,
 @.UpdatedBy varchar(60) OUTPUT,
 @.UpdatedOn varchar(15) OUTPUT,
 @.Status int OUTPUT
)
AS
SELECT
 @.ProductCode = ProductCode,
 @.ProductName = ProductName,
 @.CategoryID = CategoryID,
 @.CategoryName = (select CategoryName from mCategory where CategoryID=a.CategoryID),
 @.Image1 = isnull(Image1,''),
 @.Image2 = isnull(Image1,''),
 @.UnitPrice = isnull(UnitPrice,0),
 @.UOMValue = isnull(UOMValue,0),
 @.UOMName = isnull(UOMName,''),
 @.ShippingWeight = isnull(ShippingWeight,0),
 @.Directions = isnull(Directions,''),
 @.Ingrediants = isnull(Ingrediants,''),
 @.Warnings = isnull(Warnings,''),
 @.ShortDescription = isnull(ShortDesc,''),
 @.LongDescription = isnull(LongDesc,''),
 @.NutritionFacts = isnull(NutritionFacts,''),
 @.SearchKeywords = isnull(SearchKeywords,''),
 @.IsTaxable = case when isnull(IsTaxable,0)=0 then 'No' else 'Yes' End,
 @.CreatedBy = isnull((select LName + ',' + FName from mUser where UserID=InsertedBy),''),
 @.CreatedOn = InsertedOn,
 @.UpdatedBy = isnull((select LName + ',' + FName from mUser where UserID=UpdatedBy),''),
 @.UpdatedOn = UpdatedOn,
 @.Status = Convert(int,isnull(Status,0))
FROM
 mProduct a
WHERE
 ProductID = @.ProductID
SELECT
 ID as PricingDetailID,
 isnull(PricingFromQnty,0) as PricingFromQnty,
 isnull(PricingToQnty,0) as PricingToQnty,
 isnull(RangePrice,0) as RangePrice,
 Convert(int,isnull(Status,0))as Status
FROM
 dProduct
WHERE
 ProductID = @.CategoryID
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Regards,
BhairavI believe the way you are doing it is possible, but why not return two recordsets back to a dataset? Then you would have a master datatable and detail datatable. I believe this would work:

Create Procedure ProductDetail
(
 @.ProductID int
)
AS
SELECT
 ProductCode,
 ProductName,
 CategoryID,
 (select CategoryName from mCategory where CategoryID=a.CategoryID),
 isnull(Image1,''),
 isnull(Image1,''),
 isnull(UnitPrice,0),
...
FROM
 mProduct a
WHERE
 ProductID = @.ProductID
SELECT
 ID as PricingDetailID,
 isnull(PricingFromQnty,0) as PricingFromQnty,
 isnull(PricingToQnty,0) as PricingToQnty,
 isnull(RangePrice,0) as RangePrice,
 Convert(int,isnull(Status,0))as Status
FROM
 dProduct
WHERE
 ProductID = @.CategoryID
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

HTH|||thks for u'r suggesion
but can u plz explain me in more detail ...
how my dataset code will look like when a single strore proc return more than one recordset.

proc must not be called more than once for that...

Regards,
Bhairav|||Sure,
Just follow the code (I'm using Microsoft Data Access Application Blocks to call):

SqlParameter [] arParms = new SqlParameter[1];
arParms[0] = new SqlParameter("@.ProductID", SqlDbType.Int);
arParms[0].Value = 1;
DataSet myDS = SQLHelper.ExecuteDataset("connectiion", StoredProcedure, "ProductDetail", arParms);
DataTable myTable1 = myDS.Tables[0];
DataTable myTable2 = myDS.Tables[1];

That should give you an example of the calling code. There are other ways to manipulate the dataset data. If you are unfamiliar, just hollar and we can give you some direction, or search the archives of the data access forms. HTH|||thks..
its really the nice way to code
thks again

Regards,
Bhairav

Is this guaranteed: SELECT TOP 1 FROM ... ORDER BY Field1, Field2, Field3

SELECT TOP 1 FROM ... ORDER BY Field1, Field2, Field3
Will the statement below, always return the first record of the same
stand-alone SELECT statement as below:
SELECT * FROM ... ORDER BY Field1, Field2, Field3
Thanks,
JayYes, assuming you use the ORDER BY clause and the data remains constant.
Insert a new row, and it may be the new top result.
"Jay" <jay6447@.hotmail.com> wrote in message
news:1131529297.922481.160800@.g49g2000cwa.googlegroups.com...
> SELECT TOP 1 FROM ... ORDER BY Field1, Field2, Field3
> Will the statement below, always return the first record of the same
> stand-alone SELECT statement as below:
> SELECT * FROM ... ORDER BY Field1, Field2, Field3
>
> Thanks,
> Jay
>|||Didn't you see the contrary example that Razvan posted?
http://groups.google.com/group/micr...3752b9548322706
David Portas
SQL Server MVP
--|||In SQL Server 2005, we are a bit more consistent with TOP + ORDER BY
semantics than perhaps some previous releases.
Here are the basic rules:
1. ORDER BY determines the presentation order for the _output_ of a query.
2. Within the same select block, an ORDER BY implies that TOP returns the
TOP N rows (not necessarily in a specific order).
3. ORDER BY in subselects or views does *not* guarantee the output of a
containing query.
So, for TOP N... ORDER BY ... with no containing select block, both the set
and the order are guaranted.
Within a subquery, TOP N ... ORDER BY guarantees the set but not the output
order (you need a top-level ORDER BY to guarantee output order).
Conor Cunningham
SQL Server Query Optimization Team
"Jay" <jay6447@.hotmail.com> wrote in message
news:1131529297.922481.160800@.g49g2000cwa.googlegroups.com...
> SELECT TOP 1 FROM ... ORDER BY Field1, Field2, Field3
> Will the statement below, always return the first record of the same
> stand-alone SELECT statement as below:
> SELECT * FROM ... ORDER BY Field1, Field2, Field3
>
> Thanks,
> Jay
>

Is this an efficient way to return a comma string

Hi there,
I have created a sp and function that returns amongst other things a
comma seperated string of values via a one to many relationship, the
code works perfectly but i am not sure how to test its performance.. Is
this an efficient way to achieve my solution.. If not any suggestions
how i can improve it.. What are the best ways to check query speed?
MY SP:
CREATE PROCEDURE sp_Jobs_GetJobs
AS
BEGIN
SELECT j.Id, j.Inserted, Title, Reference, dbo.fn_GetJobLocations(j.id)
AS location, salary, summary, logo
FROM Jobs_Jobs j INNER JOIN Client c ON j.ClientID = c.id
ORDER BY j.Inserted DESC
END
GO
---
MY Function:
CREATE FUNCTION fn_GetJobLocations (@.JobID int)
RETURNS varchar(5000) AS
BEGIN
DECLARE @.LocList varchar(5000)
SELECT @.LocList = COALESCE(@.LocList + ', ','') + ll.location_name
FROM Jobs_Locations l inner join List_Locations ll on
ll.LocationID = l.LocationID
WHERE l.JobID = @.JobID
RETURN @.LocList
END
Any help or guidance much appreciated...First of all, what you have in your UDF is a unsupported construct. It
exploits certain physical behaviours that might seem to work in some cases,
but can fail in a variety of situations. Being undocumented, it can change
between versions, service packs or patches.
Doing this in SQL Server invariably requires some level of looping, either
using a cursor, WHILE loop, recursion etc. In SQL 2005, there are some work
arounds using FOR XML method which in some cases can be complex and error
prone.
A good approach is to retrieve the resultset to the client side and generate
the string you need to create.
Also, just noted that you use sp_ prefix to your procedure which is not at
all recommended, since they are reserved for system procedures and can
affect performance adversely.
Anith|||3rd time tonight i've posted this solution, interesting :).
Anyway, something like this (SQL Server 2005) will do the trick and will
perform blisteringly...
select j.Id, j.Inserted, Title, Reference,
(
select location_name + ',' as [text()]
from Jobs_Locations soi
where soi.Job_ID = t.Job_ID
order by location_name
for xml path( '' ), type
)
from Jobs_Jobs as j
It will give one line per job and concatenating each location seperating
them by commas.
Tony.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
<anthonykallay@.hotmail.com> wrote in message
news:1133804859.768819.33930@.g14g2000cwa.googlegroups.com...
> Hi there,
>
> I have created a sp and function that returns amongst other things a
> comma seperated string of values via a one to many relationship, the
> code works perfectly but i am not sure how to test its performance.. Is
> this an efficient way to achieve my solution.. If not any suggestions
> how i can improve it.. What are the best ways to check query speed?
>
> MY SP:
> CREATE PROCEDURE sp_Jobs_GetJobs
> AS
> BEGIN
> SELECT j.Id, j.Inserted, Title, Reference, dbo.fn_GetJobLocations(j.id)
> AS location, salary, summary, logo
> FROM Jobs_Jobs j INNER JOIN Client c ON j.ClientID = c.id
> ORDER BY j.Inserted DESC
>
> END
> GO
> ---
> MY Function:
> CREATE FUNCTION fn_GetJobLocations (@.JobID int)
>
> RETURNS varchar(5000) AS
> BEGIN
> DECLARE @.LocList varchar(5000)
> SELECT @.LocList = COALESCE(@.LocList + ', ','') + ll.location_name
> FROM Jobs_Locations l inner join List_Locations ll on
> ll.LocationID = l.LocationID
> WHERE l.JobID = @.JobID
> RETURN @.LocList
>
> END
>
> Any help or guidance much appreciated...
>

Is this a bug, CURSOR_STATUS() always return -3 ?

Hi,
With this simple test, CURSOR_STATUS() function always return -3
use Northwind
go
if object_id('dbo.TestCursor') is not null
drop proc dbo.TestCursor
go
create proc dbo.TestCursor
as
declare @.ContactName varchar(50)
declare My_Curs cursor
fast_forward
for
select ContactName from dbo.Customers
open My_Curs
fetch next from My_Curs into @.ContactName
select @.ContactName as ContactName
select
CURSOR_STATUS('variable', 'My_Curs') as CursStatvariable,
CURSOR_STATUS('local', 'My_Curs_Curs') as CursStatlocal,
CURSOR_STATUS('variable', 'My_Curs_Curs') as CursStatvariable
close My_Curs
deallocate My_Curs
select
CURSOR_STATUS('variable', 'My_Curs') as CursStatvariable,
CURSOR_STATUS('local', 'My_Curs_Curs') as CursStatlocal,
CURSOR_STATUS('variable', 'My_Curs_Curs') as CursStatvariable
go
exec dbo.TestCursor
go
I have 02 questions
1. Is this is a bug with CURSOR_STATUS() function ?
2. If the SP fails in the middle and close / deallocate are not executed,
will SQL Server close and dealocate the resources of us or the next time the
SP is execute it will throw 'A cursor with the name ... already exists' ?
According to my tests SQL Server close and dealocate the cursor automaticaly
Thak you for your helpOn Fri, 30 Sep 2005 13:35:15 -0700, S.M wrote:

>Hi,
>With this simple test, CURSOR_STATUS() function always return -3
(snip)
>I have 02 questions
>1. Is this is a bug with CURSOR_STATUS() function ?
Hi S.M.,
No. Try changing your SELECT statement (at both locations) to
select
CURSOR_STATUS('local', 'My_Curs') as CursStatlocal,
CURSOR_STATUS('global', 'My_Curs') as CursStatglobal,
CURSOR_STATUS('variable', 'My_Curs') as CursStatvariable
(That is - change the names, AND include a query for global cursor).
>2. If the SP fails in the middle and close / deallocate are not executed,
>will SQL Server close and dealocate the resources of us or the next time th
e
>SP is execute it will throw 'A cursor with the name ... already exists' ?
>According to my tests SQL Server close and dealocate the cursor automaticaly[/color
]
You didn't test well, then. Include the following extra line after the
forst select but before the close command in the stored proc:
SELECT a FROM b
This won't generate an error when creating the proc, but will generate
an error when executing it. Now execute the proc twice in a row.
Results:
(first execution)
ContactName
----
Maria Anders
CursStatlocal CursStatglobal CursStatvariable
-- -- --
-3 1 -3
Server: Msg 208, Level 16, State 1, Procedure TestCursor, Line 22
Invalid object name 'B'.
(second execution)
Server: Msg 16915, Level 16, State 1, Procedure TestCursor, Line 10
A cursor with the name 'My_Curs' already exists.
Server: Msg 16905, Level 16, State 1, Procedure TestCursor, Line 12
The cursor is already open.
ContactName
----
Ana Trujillo
CursStatlocal CursStatglobal CursStatvariable
-- -- --
-3 1 -3
Server: Msg 208, Level 16, State 1, Procedure TestCursor, Line 22
Invalid object name 'B'.
Of course, when you declare the cursor to be local, it WILL be closed
and deallocated when the SP fails, since it goes out of scope.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

is there no mod operator in analysis services ?

I am missing a mod (modulo, %) operator in analysis services.

I need to find out if a number is even or not ?
(to return the value in the middle of a set) - and if the tupples in the set is event then the average of the two in the middle.

HANNES

Hi Hannes,

Unfortunately, the MDX language in AS 2000 lacked a "mod" operator, and I'm not aware of it being added in AS 2005, either. Of course, it would be great if someone could contradict; meanwhile, I use this technique:

A Mod B ==> A - (Int(A/B) * B)

Is there any performance differences between function type IF and TF?

I could not found more references in BOA about functions that return table.
Does anybody know if there is any performance differences between these two
type of functions?
IF = Inlined table-function
TF = Table function
Thanks,
Lijun
Lijun Zhang (nospam@.nospam.nospam) writes:
> I could not found more references in BOA about functions that return
> table. Does anybody know if there is any performance differences between
> these two type of functions?
> IF = Inlined table-function
> TF = Table function
Yes, there is.
An inline function is in fact not a function at all; it is a macro. The
optimiser pastes the text of the function into the query and optimizes
the result.
A multi-step function returns data to a table variable, and the result
of the function is opaque to the optimizer.
Thus, in the former case the optimizer have more information, and
thus better possibilities to create a better execution plan.
But sometimes it can be too much information, so in fact it leads
to poorer performance. But in the long run, inline functions will
give you better performance.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
|||Yes, there may be a significant difference, although of course that
depends on exactly what you are doing.
Despite the similar syntax *inline* table-valued functions are
implemented very differently from *multi-statement* table-valued
functions.
A inline table-valued function consists of a single query that works
very like a view. That is, when the function is referenced in another
query the SQL from both the calling query and the function itself is
considered together so as to arrive at an optimal execution plan.
With a multi-statement table-valued function that kind of optimization
isn't possible. In a multi-statement function the function code is
executed more like a stored procedure and then a result returned to the
calling code for further processing.
If you want to encapsulate a single query in a function then use an
inline TVF, or use a view.
If you need to put multiple statements in a function then you'll have
to use a multi-statement TVF.
David Portas
SQL Server MVP

Is there any performance differences between function type IF and TF?

I could not found more references in BOA about functions that return table.
Does anybody know if there is any performance differences between these two
type of functions?
IF = Inlined table-function
TF = Table function
Thanks,
LijunLijun Zhang (nospam@.nospam.nospam) writes:
> I could not found more references in BOA about functions that return
> table. Does anybody know if there is any performance differences between
> these two type of functions?
> IF = Inlined table-function
> TF = Table function
Yes, there is.
An inline function is in fact not a function at all; it is a macro. The
optimiser pastes the text of the function into the query and optimizes
the result.
A multi-step function returns data to a table variable, and the result
of the function is opaque to the optimizer.
Thus, in the former case the optimizer have more information, and
thus better possibilities to create a better execution plan.
But sometimes it can be too much information, so in fact it leads
to poorer performance. But in the long run, inline functions will
give you better performance.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Yes, there may be a significant difference, although of course that
depends on exactly what you are doing.
Despite the similar syntax *inline* table-valued functions are
implemented very differently from *multi-statement* table-valued
functions.
A inline table-valued function consists of a single query that works
very like a view. That is, when the function is referenced in another
query the SQL from both the calling query and the function itself is
considered together so as to arrive at an optimal execution plan.
With a multi-statement table-valued function that kind of optimization
isn't possible. In a multi-statement function the function code is
executed more like a stored procedure and then a result returned to the
calling code for further processing.
If you want to encapsulate a single query in a function then use an
inline TVF, or use a view.
If you need to put multiple statements in a function then you'll have
to use a multi-statement TVF.
David Portas
SQL Server MVP
--

Is there any performance differences between function type IF and TF?

I could not found more references in BOA about functions that return table.
Does anybody know if there is any performance differences between these two
type of functions?
IF = Inlined table-function
TF = Table function
Thanks,
LijunLijun Zhang (nospam@.nospam.nospam) writes:
> I could not found more references in BOA about functions that return
> table. Does anybody know if there is any performance differences between
> these two type of functions?
> IF = Inlined table-function
> TF = Table function
Yes, there is.
An inline function is in fact not a function at all; it is a macro. The
optimiser pastes the text of the function into the query and optimizes
the result.
A multi-step function returns data to a table variable, and the result
of the function is opaque to the optimizer.
Thus, in the former case the optimizer have more information, and
thus better possibilities to create a better execution plan.
But sometimes it can be too much information, so in fact it leads
to poorer performance. But in the long run, inline functions will
give you better performance.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp|||Yes, there may be a significant difference, although of course that
depends on exactly what you are doing.
Despite the similar syntax *inline* table-valued functions are
implemented very differently from *multi-statement* table-valued
functions.
A inline table-valued function consists of a single query that works
very like a view. That is, when the function is referenced in another
query the SQL from both the calling query and the function itself is
considered together so as to arrive at an optimal execution plan.
With a multi-statement table-valued function that kind of optimization
isn't possible. In a multi-statement function the function code is
executed more like a stored procedure and then a result returned to the
calling code for further processing.
If you want to encapsulate a single query in a function then use an
inline TVF, or use a view.
If you need to put multiple statements in a function then you'll have
to use a multi-statement TVF.
--
David Portas
SQL Server MVP
--

is there any limit to how long of a string SqlDataReader.GetString() can return?

is there any limit to how long of a string SqlDataReader.GetString() can
return?No.
And next time please don't re-post your question in every single group.
Asking in ONE group should be sufficient.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Daniel" <softwareengineer98037@.yahoo.com> wrote in message
news:%23fR04M2YGHA.3704@.TK2MSFTNGP03.phx.gbl...
> is there any limit to how long of a string SqlDataReader.GetString() can
> return?
>

is there any limit to how long of a string SqlDataReader.GetString() can return?

is there any limit to how long of a string SqlDataReader.GetString() can
return?no
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
"Daniel" <softwareengineer98037@.yahoo.com> wrote in message
news:ewcJ9M2YGHA.508@.TK2MSFTNGP02.phx.gbl...
> is there any limit to how long of a string SqlDataReader.GetString() can
> return?
>

is there any limit to how long of a string SqlDataReader.GetString() can return?

is there any limit to how long of a string SqlDataReader.GetString() can
return?no
--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
"Daniel" <softwareengineer98037@.yahoo.com> wrote in message
news:ewcJ9M2YGHA.508@.TK2MSFTNGP02.phx.gbl...
> is there any limit to how long of a string SqlDataReader.GetString() can
> return?
>

is there any limit to how long of a string SqlDataReader.GetString() can return?

is there any limit to how long of a string SqlDataReader.GetString() can
return?Only 8000 characters. No more
Nathan H. Omukwenyi
"Daniel" <softwareengineer98037@.yahoo.com> wrote in message
news:uTdm7M2YGHA.500@.TK2MSFTNGP03.phx.gbl...
> is there any limit to how long of a string SqlDataReader.GetString() can
> return?
>|||I don't believe there is a limit other than available memory.
Hope this helps.
Dan Guzman
SQL Server MVP
"Nathan H. Omukwenyi" <nathan@.e-tools.com> wrote in message
news:eXO8V8wZGHA.428@.TK2MSFTNGP02.phx.gbl...
> Only 8000 characters. No more
> Nathan H. Omukwenyi
> "Daniel" <softwareengineer98037@.yahoo.com> wrote in message
> news:uTdm7M2YGHA.500@.TK2MSFTNGP03.phx.gbl...
>|||I believe the only limitation is the underlying data type in sql. For
example a varchar can only be max 8000 chars. but text could be up to 2
gigs.
"Daniel" <softwareengineer98037@.yahoo.com> wrote in message
news:uTdm7M2YGHA.500@.TK2MSFTNGP03.phx.gbl...
> is there any limit to how long of a string SqlDataReader.GetString() can
> return?
>|||Agreed. I had only tested with the varchar SQL data type. Testing with TEXT
shows that it can handle much more but reaching the maximum is taking too
much time and resources. So I suppose Dan could be right about available
memory.
Nathan H. Omukwenyi
"Jeremy" <nospam@.here.com> wrote in message
news:O5fxDqBaGHA.4752@.TK2MSFTNGP02.phx.gbl...
>I believe the only limitation is the underlying data type in sql. For
> example a varchar can only be max 8000 chars. but text could be up to 2
> gigs.
> "Daniel" <softwareengineer98037@.yahoo.com> wrote in message
> news:uTdm7M2YGHA.500@.TK2MSFTNGP03.phx.gbl...
>

Is there any examples of inserting/create a namespace in sql

Is there any examples of inserting/create a namespace in t-sql...so my
stored procedure return a <root xmlns="http://ns"></root>
thanks a billion!!!Hello mm,
m> Is there any examples of inserting/create a namespace in t-sql...so
m> my stored procedure return a <root xmlns="http://ns"></root>
In SQL Server 2005, you can use the new "with xmlnamespaces" option to do
that. In SQL Server 2000, no such luck, there's not a good way (at least
that I can recall) to get namespaced XML from for xml query.
with xmlnamespaces(default 'http://ns')
select 'a' as 'text()'
for xml path('v'),root('root'),type
See the topic "Adding Namespaces Using WITH XMLNAMESPACES " in BOL for more
information
Thanks!
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||thanks for answer...now i can move on :)
"Kent Tegels" <ktegels@.develop.com> wrote in message
news:b87ad74176758c83649594b73f0@.news.microsoft.com...
> Hello mm,
> m> Is there any examples of inserting/create a namespace in t-sql...so
> m> my stored procedure return a <root xmlns="http://ns"></root>
> In SQL Server 2005, you can use the new "with xmlnamespaces" option to do
> that. In SQL Server 2000, no such luck, there's not a good way (at least
> that I can recall) to get namespaced XML from for xml query.
> with xmlnamespaces(default 'http://ns')
> select 'a' as 'text()'
> for xml path('v'),root('root'),type
> See the topic "Adding Namespaces Using WITH XMLNAMESPACES " in BOL for
> more information
> Thanks!
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>

Is there an SQL command for this?

Okay I have a column

Red
Red
Blue
Yellow
Blue
Blue
Blue
Blue

I want to return the value that appears most i.e. in this case Blue.

Thanks

BenThere is probably a more efficient way...

SELECT TOP 1 X, Count(*)
 FROM table
 GROUP BY X
 ORDER BY Count(*) DESC

is there an error handler in sql stored procedures?

is there an error handler in sql stored procedures? For example if i want to do a drop something and there is a lock, i want to try return if it droped it or not and if not then try again latererror handling in sql 2000 is pretty poor. basically you have to check @.@.ERROR after every statement that could possibly fail. The situation in 2005 is much better since there you can use try/catch.

for error handling on 2000:
http://www.sommarskog.se/error-handling-I.html
http://www.sommarskog.se/error-handling-II.html

for try/catch:
http://msdn2.microsoft.com/en-us/library/ms175976.aspx