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 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 there Limit to # of Matrixes Displayed in RDL?

I have a report that consists of 12 matrixes. In Dev Studio all the data for
all matrixes returns correctly, but when the rdl is on the Report Server and
the Report is run, the 12th ( last ) matrix only displays column group and
row headings, no detail data within the matrix cells. Also from within Dev
Studio when I export the report as a "Web Archive", the "web" page exported
displays incorrectly just like the report does on the report server. Why
does it not display the same as from within Dev Studio?
Thanks in advance.Found an incorrect non-zero value in the groups before row headers property
settings which was causing no data in the detail cells. No further help
needed.
"mike" wrote:
> I have a report that consists of 12 matrixes. In Dev Studio all the data for
> all matrixes returns correctly, but when the rdl is on the Report Server and
> the Report is run, the 12th ( last ) matrix only displays column group and
> row headings, no detail data within the matrix cells. Also from within Dev
> Studio when I export the report as a "Web Archive", the "web" page exported
> displays incorrectly just like the report does on the report server. Why
> does it not display the same as from within Dev Studio?
> Thanks in advance.

is there any editable parameters?

i have written a function in report properties, but error returns when i
tried to add 1 to a report parameter, saying that parameter.value is
read-only.
anyways to have editable parameters'
thanks in advance~I have asked almost the same question a few days ago. Apparently no one can
tell us if this can be done.
"Jasonymk" wrote:
> i have written a function in report properties, but error returns when i
> tried to add 1 to a report parameter, saying that parameter.value is
> read-only.
> anyways to have editable parameters'
> thanks in advance~