How to rewrite this basic query for SQL CE ?

Question

Post reply

How to rewrite this basic query for SQL CE ?

akhandels

Right there with Babe

Points: 731
More actions
January 17, 2011 at 10:14 am

#232313

Hi,
I have this piece of code written for SQL Server 2008. With all the limitations of SQLCE can anybody please help to rewrite this query?
It is a basic update statement, which gets the value from a pre-query.
declare @tmp_RESIDENCE_LOCATION NVARCHAR(50)
SET @tmp_RESIDENCE_LOCATION = ((SELECT TOP(1) FOREIGN_RESIDENCE_LOCATION_CODE FROM FOREIGN_RESIDENCE_LOCATION WHERE COUNTRY_ID
IN
(SELECT HOST_COUNTRY_PLACE_ID FROM COST_PROJECTION CP
WHERE CP.COST_PROJECTION_ID = 2)
)
ORDER BY SORT_INDEX)
UPDATE JURISDICTION_COUNTRY_INPUT
SET FOREIGN_RESIDENCE_LOCATION_CODE = @tmp_RESIDENCE_LOCATION
WHERE COUNTRY_TYPE = 1 AND COST_PROJECTION_ID = 2
Thanks.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Dwayne Dibley SSCrazy Points: 2350 More actions · Answer 1

Will you be executing this code from a .Net application?

akhandels Right there with Babe Points: 731 More actions · Answer 2

akhandels

Right there with Babe

Points: 731

February 17, 2011 at 6:19 am

#1288171

yes

Jeff Moden SSC Guru Points: 1004425 More actions · Answer 3

akhandels (1/17/2011)
Hi,
I have this piece of code written for SQL Server 2008. With all the limitations of SQLCE can anybody please help to rewrite this query?
It is a basic update statement, which gets the value from a pre-query.
declare @tmp_RESIDENCE_LOCATION NVARCHAR(50)
SET @tmp_RESIDENCE_LOCATION = ((SELECT TOP(1) FOREIGN_RESIDENCE_LOCATION_CODE FROM FOREIGN_RESIDENCE_LOCATION WHERE COUNTRY_ID
IN
(SELECT HOST_COUNTRY_PLACE_ID FROM COST_PROJECTION CP
WHERE CP.COST_PROJECTION_ID = 2)
)
ORDER BY SORT_INDEX)
UPDATE JURISDICTION_COUNTRY_INPUT
SET FOREIGN_RESIDENCE_LOCATION_CODE = @tmp_RESIDENCE_LOCATION
WHERE COUNTRY_TYPE = 1 AND COST_PROJECTION_ID = 2
Thanks.

The only thing I see that may cause CE some heartburn is the SELECT TOP (1). I believe you'll need to change it to just SELECT TOP 1 (ie. without the parenthesis).

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Dwayne Dibley SSCrazy Points: 2350 More actions · Answer 4

OK

We have two issues here. First, you can't use TSQL variables in SQL CE. Secondly, SQL CE processes single commands through a connection. So you need to do the following through your .NET application:

As I haven't got a copy of your database or schema to work against, the following is to demonstrate the principle of what you need to do. It is not tested or working code.

Firstly declare a recordset or datareader object and populate it with the result of the first query:

SELECT TOP 1 FOREIGN_RESIDENCE_LOCATION_CODE FROM FOREIGN_RESIDENCE_LOCATION WHERE COUNTRY_ID

IN

(SELECT HOST_COUNTRY_PLACE_ID FROM COST_PROJECTION CP

WHERE CP.COST_PROJECTION_ID = 2)

)

ORDER BY SORT_INDEX

With the value now in that object, you are ready for the next part of the query which can be executed as follows:

using (SqlCeConnection connect = new SqlCeConnection("Data Source=C:\[YourDataFileHere].sdf"))

{ connect.Open();

int location = [value from first aprt of the query];

int num = 0;

using (SqlCeCommand command = new SqlCeCommand("UPDATE JURISDICTION_COUNTRY_INPUT

SET FOREIGN_RESIDENCE_LOCATION_CODE = @tmp_RESIDENCE_LOCATION

WHERE COUNTRY_TYPE = 1 AND COST_PROJECTION_ID = 2", connect))

{ command.Parameters.Add("@tmp_RESIDENCE_LOCATION", SqlDbType.[Int or whatever datatype it is], location);

command.ExecuteNonQuery();

}

HTH