Stored procedure advise

[John]

Hi

I have a winform app with sql server 2008 backend. I am using strongly typed
dataset with dataadapter to access data from an sql server table. I am using
below four SQL with the dataadapter to access First, Next, Prev and Last
records respectively;

First
SELECT TOP 1 <field list>
FROM Clients
WHERE (Status = @Status)
ORDER BY CompanyID

Next
SELECT TOP 1 <field list>
FROM Clients
WHERE (CompanyID > ?) AND (Status = @Status)
ORDER BY CompanyID

Prev
SELECT TOP 1 <field list>
FROM Clients
WHERE (CompanyID < ?) AND (Status = @Status)
ORDER BY CompanyID DESC

Last
SELECT TOP 1 <field list>
FROM Clients
WHERE (Status = @Status)
ORDER BY CompanyID DESC

My questions are;

1. What modifications do I need for when @Status is null?

2. What modifications do I need for when ORDER BY is a field other than
CompanyID?

3. Am I better off implementing these as stored procedures, in CLR etc.?

Many Thanks

Regards

 

[Uri Dimant]

John
1. What modifications do I need for when @Status is null?

You can use COALESCE function or checking the variable for NULL

WHERE (Status = COALESCE(@Status,Status))

or

WHERE (Status =@Status OR @Status IS NULL)

Read Erland's article

http://www.sommarskog.se/dyn-search-2005.html





2. What modifications do I need for when ORDER BY is a field other than

CompanyID?

Do you get the column as a varibale

ORDER BY CASE WHEN @Col = 'company' THEN company END ASC,

CASE WHEN @Col = 'city' THEN city END ASC,

3. Am I better off implementing these as stored procedures, in CLR etc.?

I do not think you need CLR to use in that case

 

[Paul]

Lookup information about paging data and just set your record count to 1.

What happens in your example when you have multiple clients from the same
company?

 

[--CELKO--]

I have a winform app with SQL Server 2008 backend. I am using strongly typed dataset with dataadapter to access data from an SQL Server table. I amusing below four SQL with the data adapter to access First, Next, Prev andLast records [sic: rows are not records] respectively; <<

This is the SQL side of the house, so we don't know about "winform
app" or "dataadapter " nor do we have any concept of First, Next,or
Previous records since rows have no ordering.
"A problem well stated is a problem half solved." -- Charles F.
Kettering

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. If you know how, follow ISO-11179 data element naming
conventions and formatting rules (i.e. the data element "status" too
vague; you need "<something>_status" instead). Temporal data should
use ISO-8601 formats. Code should be in Standard SQL as much as
possible and not local dialect (avoid dialect like "SELECT TOP n").

If you are trying to get an ordered subset, you can use the ROW_NUMBER
() function in a query.