[posted and mailed, please reply in news]

Robert (rkelso@kelsointeractive.com) writes:
> Currently, I use this kind of statement a lot in my ASP:
>
> sql = "update"
> sql = sql & " field1 = value1"
> sql = sql & ",field2 = value2"
> If Len(value3) Then sql = sql & ",field3 = value3"
> sql = sql & " where ...., etc, etc
>
> The important part here is checking if "value3" has a value before
> inserting it into my SQL statement. So how can I put this update
> statement into a stored procedure, pass in values for value1, value2,
> and value3, and leave off the value3 part of the update if value3
> comes in as null.

UPDATE tbl
SET field1 = @value1,
field2 = @value2,
field3 = CASE len(@value3) > 0 AND @value IS NOT NULL
THEN @value3
ELSE field3
END
WHERE ...

No reason to even think about dynamic SQL.


--
Erland Sommarskog, SQL Server MVP, sommar@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp