Lowell (12/8/2011)

---

It sounds like you were looping through every stored proc name;

if the role is going to have EXECUTE priviledges on all procs, without exception, i think you can simply...

Please don't be tough to Ninja, maybe it was a tough day...

Lynn Pettis (12/6/2011)

---

Ninja - You'll have to show me what you were trying to accomplish, just not clicking today.

Here is another way to write my query, just requires more typing:

select

...

Can't deliver coffee to Montreal from Toronto, TH does not provide that...

Do you think this is correct?

where org.Business_Line = @BL and

1 = CASE WHEN @Due = 1 AND NextReviewDate between @start1 and @end1 THEN 1

WHEN @Due = 2 AND NextReviewDate...

Ninja's_RGR'us (12/6/2011)

---

Declare @Begindate & @EndDate

Do your cases first to set the range then use between in the select.

If you insist on the where (making it non sargable)

AND 1 = CASE...

I figured it out:

All I need to do is to define parameters for the sp_executesql

Here is the working code:

.........

EXEC sp_executesql

@sql,

...

From the error message it seems that sp_executesql must take parameters with the type of 'ntext/nchar/nvarchar' only, so I modified the sp again as following:

......

declare @NewTimeLine nvarchar(3)

declare @NewConsultant nvarchar(3)

set @NewTimeLine...

Actually I did.

ALTER Proc [dbo].[spGetStatisticByCategoryBLTimeLineConsultant](@Type varchar(50),@BL varchar(255), @TimeLine int, @Consultant int)

AS

declare @param datetime

SET @param =

CASE

WHEN @timeline...

Gianluca Sartori (12/5/2011)

---

You can use sp_executesql:

ALTER Proc [dbo].[spGetStatisticByCategoryBLTimeLineConsultant](@Type varchar(50),@BL varchar(255), @TimeLine int, @Consultant int)

AS

DECLARE @param datetime

SET @param =

CASE

WHEN @timeline = 0 THEN DATEADD(yy,-100,getdate())...

Thanks all responds, the final version is here:

select BL = ISNULL(NULLIF(Business_Line, ''), 'Others')

FROM Isc_OrgUnits

Group By Business_Line

ORDER BY

CASE

WHEN len(Business_Line)>0

...

I know, I tried to adopt it like here, it doesn't...

GSquared (11/30/2011)

---

You can nest NullIf inside the IsNull.

ISNULL(NULLIF(Business_Line, ''), 'Others')

That will take a blank and turn it into a Null, and then run the ISNULL on it. If...

In stead of using so many "case" in the select, I found this query is more elegant:

SELECT distinct ISNULL(Business_Line, 'Others')

FROM Org

ORDER BY ISNULL(Business_Line, 'Others')

The problem...

Lowell (11/29/2011)

---

i think you just need something like this:

SELECT BL = CASE

WHEN Business_Line IS NULL

...