Glad to see you solved your issue.

I don't have any docs about Profiler at hand. You could start it and read the help, or google it.

You could also add some PRINT commands to the IF/ELSE branches to see the path it took.

Maybe this is easier than setting up a trace...

Well, it doesn't work as you expected, but it probably works the way you coded it.

Since you only gave us the code and not an explanation of the expected behaviour,...

Brandie Tarvin (12/16/2010)

---

Gianluca Sartori (12/16/2010)

---

Actually, dynamic SQL is the most efficient way to handle optional parameters in stored procedures.

Using COALESCE,...

Actually, dynamic SQL is the most efficient way to handle optional parameters in stored procedures.

Using COALESCE, ISNULL, CASE and OR...

WayneS (12/15/2010)

---

Craig Farrell (12/15/2010)

---

Actually, the WHERE 1=1 is a neat little trick to avoid doing extra if statements to determine if something starts the clause and needs an AND. ...

Yes, as far as cache-hits is concerned. The same exact code...

What a question! Really hard to give a sensible answer.

I think this one needs to broken into several points:

1) Query parsing / compilation

ORMs generate SQL code that has to be...

ADO Command Objects can help you avoiding sql injection and, whenever the procedure is called multiple times, can...

I don't know exactly what is causing the error. It seems to be related to the parameter type.

You could try to create the parameters reading from the stored procedure metadata,...

You could take a look at this book on Team-based development:

http://www.sqlservercentral.com/articles/books/71472/

The ebook is free.

Instead of constructing a sql string containing a string representation of the parameters, you'd better use a command object and pass the parameters as such.

Example:

Dim cmd, sql, prm, conn

Set cmd...

This should do:

(Emp_Name=Case when @VarEmp_Name is not null Then @VarEmp_Name Else Emp_Name end)And

Hope this helps,

Gianluca

GSquared (12/6/2010)

---

Real people are overrated. I've had plenty of tech support people fail the...

RETURN exits from current procedure and doesn't affect callers.

command-1 will be executed.