Order By in SQL Update Stored Proc

[yaanmonkey]

Hi,

i have an ASP.net page that has a gridview where the source is:

set rowcount @Top

SELECT
ID,
Title,
FirstName,
Surname,

FROM Leads_Source

WHERE
(ISNULL(Surname, '') LIKE @Surname + '%') AND
(ISNULL(County, '') LIKE @County + '%') AND
(ISNULL(Postcode, '') LIKE @Postcode + '%') AND
(ISNULL(HomePhone, '') LIKE @HomeTel + '%') AND
(isnull(AssignedTo,'')='') AND
(CallComplete_YN=0)

ORDER BY Surname

Which works fine as it allows the user to select the number of rows and the criteria to filter the data for the gridview.

What i am trying to do is allow the user to then select a staff member and update all the rows with his/her ID therefore assigning these rows to them.

i tried pretty much the same syntax but using:

SET AssignedTo= @AssignedTo

but it complains about the ORDER BY statement.

I need to order the records because the user will want to see them alphabetically and it was ordering them by ID. I obviously want the user to update only the records that they see on screen.

I am using SQL Server 2005 and ASP.NET 2.

Any help would be greatly appreciated. I am a bit of a newbie to this.

Many thanks,

Ian

 

[bborissov]

You have an extra comma after SurName in field list of your query:

....
Surname[COLOR=red],[/color]

FROM         Leads_Source
...

must be

....
Surname

FROM         Leads_Source
...

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[yaanmonkey]

Thanks bborisov,

That was because i cut out some of the fields in the select statement to keep it smaller on the post.

The select works fine, it is only when i come to use similar syntax and use:

SET AssignedTo=@AssignedTo (Passed to the stored proc)

that SQL says there is a problem with the ORDER BY statement.

Should you be able to use ORDER BY in an Update Stored Proc or is there another way of doing it?

The only other thing that is in there that i can see may cause an issue is:

SET rowcount=@Top

Although SQL didn't complain about this.

Thanks,

 

[ca8msm]

An update statement doesn't return any records so how can it be ordered?


____________________________________________________________
Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]

Need help finding an answer? Try the Search Facility or read FAQ222-2244.