Thanks for the feedback Seth,

I'm still in need of a quicker function to replace mine, anyone can do it set based? I'll test it to see if it outperforms mine!

Thanks,

Hi Seth,

Thanks for the quick reply,

I've tested the code, and I'm disapointed to see that the code is not performant. It takes twice the time to run against 200 000...

create PROCSelectForReport

as

@Parameter nvarchar(3)

BEGIN

if @Parameter = 'ALL'

BEGIN

Select blablabla

--With no where clause (you get all the rows)

END

else

BEGIN

select blablabla

where region = @Parameter

END

END

Hope this helps,

This is how you do a where on more than 1 column, if you want it all, just add "OR" clauses.

Here:

SELECT COUNT(DEClearedDate) AS DECleared,

...

The cool thing about this is that your boss was right, 😉

Here's a solution I was building, but I'm a bit late,

USE tempdb

GO

IF EXISTS (SELECT *

...

You can use 2 different ways to exit the procedure.

1. Implement a try catch with a raiserror (If i remember well, you need to raise an error with a...

Here, I don't really feel like reading this bcp thing, I know how to work it out, since I've built some myself, but I simply hate it. Here's a...

Ok Winston, this is untested, since I don't have the tables structure or any data, but it should put you on track as to what you need to do.

SELECT ol.OrderID,

...

Can you post your data?

With some data, We'll see what we can do, and explain why it did not work.

Hi,

You cannot append 2 stored procedures results. What you will need to do is

altering the 2 procedures so that they insert their respective results at the

end of the procedure.

create...

Wow.. I can't believe I didn't see that. I was trying to fit the distinct keyword somewhere around the code, without doing a subquery. Now that I see it, it...

The second one for sure, since it does not populate an insert everytime you need the table. If 20 users use this procedure, 20 tables will be created and deleted...

Nice!

Thanks Lynn, I'll look the columnProperty.

I knew there must've been another way, on the other hand, I could always join the sys.columns.

Thanks,

Cheers,

J-F

Here, done some quick testing:

both take around 15secs to run:

/* Test the difference between isNull and Coalesce on a 1 000 000 records table.*/

IF EXISTS (SELECT 1

...

Why? Is it quicker to do a coalesce over a IsNull?

And why not in a where clause?

Cheers,

J-F