BTW this is how to call the proc from VB6 :

MyCn is a global connection to the application

Private Function exec_demo(ByVal param1 As Integer, ByRef paramOUT As Integer, Optional ByRef...

Look at this carefully, you must supply the output parameter to the proc...

Declare @Return as int

Declare @Out as int

set @Out = 1000

exec @Return = dbo.demo 9, @out output

Select...

That's the way I would go to... I used this technique once before. I put all the important columns in the main table, then every other text and large...

Where do you run this code from?

This won't work if you have a mix of different datatypes. Case expression must return only 1 datatype. And unless I'm mistaken, it'll take the first case as...

btw.. Sql server doesn't need to have '{' around the dates.. this is usually to have some compatibility with MS Access.

Here's an exemple of how to execute a stored proc...

This is an asssumption, but in sqlserver 2000 you can set the compatibility level of the database. The 2000 dbs have a level of 80 (8.0), if you set...

Maybe not, but if you think you are, then I feel I should tell you this : Make sure that the user has absolutely NO WAY of accessing the column...

Select Name, XType from dbo.SysObjects O where USER_NAME (O.UID) = 'dbo' order by XType, Name

Btw I would strongly urge you to read these documents... it's an hour very well spent (especially the first one for this thread) :

Dynamic Search Conditions in T-SQL

Depends on how many columns you have to sort on... if there's only 2-3 possibilities and that the recordset is small you can always do it on the server. ...

Wish I could.. but the only thing I know about heap is how to spell it so I'll be waiting for an answer myself.

Nice, a triple synchronised post. It's a first for me.

Hey Frank, since when are you approving the use a dynamic sql without questionning the design or the task at hand???

That clears it up... obviously works now on another table.

Unless this is for administration work I would suggest you find another way to solve your problem because the use of dynamic sql is strongly suggested againsts in a production...