Want to use CASE WHEN in a typed dataset (xsd) Update query

[MMock]

Hi. Pretty sure I can't do this in which case I will write 3 different queries, but I'd like to do it in one.
This is a C# program using Dataset xsd files to query my db tables.
I wrote this...

UPDATE Trips 
SET PrevailingWage =  
	CASE
	WHEN @pwValue = 'Yes' THEN 1
	WHEN @pwValue = 'No' THEN 0
	WHEN @pwValue = 'Unknown' THEN null
	END
WHERE Control = @Control

and named it qryUpdatePrevailingWage. When I invoke qryUpdatePrevailingWage from the code, it thinks there's only one parameter to pass it.

qryTrips.qryUpdatePrevailingWage(newPWValue, tripControl);

No overload for method 'qryUpdatePrevailingWage' takes 2 arguments

[techgnome]

Bern there, done that ... you can certainly do that .... although I'd do it like this:

UPDATE Trips 
SET PrevailingWage =  
	CASE @pwValue
            WHEN 'Yes' THEN 1
	    WHEN 'No' THEN 0
            ELSE null
	END
WHERE Control = @Control

EDIT - I'm getting old ... I totally missed what you're doing there... What does the full definition of the sproc look like? There's two variabes inuse there, but are they parameters to it as well?


-tg

[Zvoni]

In cases like this i'd go with a CTE, and then LEFT JOIN it

Untested!

WITH CTE AS (SELECT * FROM (VALUES ('Yes', 1), ('No', 0)) X(v1, v2)) 
UPDATE T 
SET T.PrevailingWage=C.v2 
FROM Trips T 
LEFT JOIN CTE C 
ON C.v1=@pwValue 
WHERE T.Control=@Control

[MMock]

Thanks for the posts. It is not a stored procedure. It is a query. I have datasets/xsd files. So I go into dsTrips.xsd and in my QueriesTableAdapter "box" (for lack of a better word, it's a box that lists all the queries I've written so far) I right click and say Add Query. I write my code. I save it and give it a name. Then in my code...

                        using (dsTripsTableAdapters.QueriesTableAdapter qryTrips = new dsTripsTableAdapters.QueriesTableAdapter())
                        {
                            qryTrips.qryUpdatePrevailingWageCTE(...
                        }

when I type that open paren, intellisense tells me what the query wants for parameters. For the CTE one I just tried from zvoni, it doesn't recognize any parameters. For the TG one I just tried, it only wants an integer parameter called Control. Which was my problem in OP. Maybe I just can't do it in a query and need a sproc. I wanted to just write a simple query and have it in the code.