June 8, 2006 at 4:57 pm
Trying to convert a query from our coldfusion page into a stored procedure in sql2000. Don't know T-SQL sytax well, just from CF usage, which is similar but not the same.
Need to update a table with up to 5 parameters which are fields, but none are required. If provided, then set them to the new value, otherwise leave alone.
In CF, query uses ifs such as: update table set if value1 exists then field1 = val(field1), if value2 exists then field2 = val(field2), etc... for all possible fields in the table. My attempt at the query looks like:
CREATE PROCEDURE [dbo].[updatetable1] @param1 varchar(80) = NULL, @param2 varchar(80) = NULL, @param3 varchar(20) = NULL, @param4 varchar(80) = NULL, @param5 varchar(80) = NULL, @User_ID int, @Company_ID int AS update table1 set
if @param1 is not NULL
field1 = @param1,
if @param2 is not NULL
Field2 = @param2,
if @param3 is not NULL
field3 = @param3,
if @param4 is not NULL
field4 = @param4,
if @param5 is not NULL
field5 = @param5,
field6 = GETDATE()
where User_ID = @User_ID and Company_ID = @Company_ID
go
Error msgs don't like the first if or any of the fieldx = @fieldx statements, so obviously it doesn't work. Any help or best resources available for T-SQL sytax?
June 8, 2006 at 5:04 pm
UPDATEtable1
SETfield1 = ISNULL(@param1, field1)
,field2 = ISNULL(@param2, field2)
...
where User_ID = @User_ID and Company_ID = @Company_ID
This updates the fields either way, just that if the value of the parameter is null then it sets it back to its current value. Otherwise you could do it with CASE statements or dynamic SQL, though I like this way the best.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply