I think this works

SELECT CONVERT(BIGINT, 1234567890123456) AS CCNumber

All interesting points for the initial validation.  I'm wondering, once the credit card has been validated once,  Do you still have to do lots of manipulation with those numbers, or...

Ya I'm kind of sick of that don't use dynamic sql discussion.  If they want to hang themselves, I let 'em.  I'm not here to save the world anymore.  But...

Should be able to...  can you post the code?

If worse comes to worse, you can always do the distinct query in a derived table, then apply the dynamic sort.

The table is created in the exec statement.  It is automatically destroyed when that connection is dropped (before the execution goes back to the procedure).

You'll have to create that table...

Is there any way to get the rowcount in profiler (directly, without re-rerunning the query)?  I'm guessing no, but hopping for a yes on this one!

Here's where I show my in-experience.  Bigint assumes that the card number will NEVER have a leading 0.  Now I have never seen that on a card but I would make sure...

Varchar would be my guess (without any experience in that domain).

Still need to have a dynamic sort... or 1 query / sp / function per variation to have the optimal query plan...

That's alright, we're all here to learn .

To complete this, I strongly suggest you validate the input order variable :

if @SortOrder IN ('jobid', 'empName', 'empid')

begin

end

else

begin

raiserror ('Invalid sort order', 13, 1) --this avoids any possible sql injection

end

What movies was that?

I'm sure Jeff will come up with a more complete answer, but one example is that you'll create that view, then a few weeks later, someone else will use this...

You should be able to get this to work...

SELECT     TOP 100 PERCENT ITEMNMBR, SUM(TRXQTY) AS Expr1, SUM(CASE WHEN datediff(month, docdate, getdate()) = 0 THEN TRXQTY ELSE 0 END) AS period1

FROM         dbo.IV30300

GROUP...

First question : data warehouse or transactional database (reporting or working)?