SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to rewrite this basic query for SQL CE ?


How to rewrite this basic query for SQL CE ?

Author
Message
akhandels
akhandels
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 Visits: 117
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.
Dwayne Dibley
Dwayne Dibley
SSC-Addicted
SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)

Group: General Forum Members
Points: 440 Visits: 3171
Will you be executing this code from a .Net application?
akhandels
akhandels
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 Visits: 117
yes
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85450 Visits: 41079
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Dwayne Dibley
Dwayne Dibley
SSC-Addicted
SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)

Group: General Forum Members
Points: 440 Visits: 3171
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search