CODEWITHSUNDEEP
sqlt-sqlsql-server-2008stored-procedures
Moon
Moon

Reputation: 35395

Is there a better approach than a SQL Stored Procedure?

I have created a stored procedure GetNotifications that returns all notifications for a specific user. Other developers are using this SP at many different places.

Now we have a need to implement a paging functionality so that we do not flood users with all notifications at the same time.

I cannot modify the existing SP since it's being used.

I can create another SP with paging functionality in it but I really don't want to do that since it requires a lot of repeating code and of course it would suck if we change the business logic for getting notifications in future.

This is something I can do: create a new SP that internally calls the GetNotifications and then implement the paging on the returned result-set. But then wouldn't it be unnecessary load on the server since the GetNotifications will return all the results regardless?

Can you think of a better way to approach this problem?

Upvotes: 2

Views: 106

Answers (2)

Dylan Smith
Dylan Smith

Reputation: 22255

Have one stored proc that takes in 2 params: @PageNumber, @RowsPerPage

If 0 is passed in for both params return all rows, otherwise do paging.

Update your existing code to pass in 0/0 for the args, then your new code can pass in actual values if/when it wants paging.

As suggested in the comments, if you specify default values of 0/0 for the params you don't even need to update the existing code.

Upvotes: 3

Stuart Ainsworth
Stuart Ainsworth

Reputation: 12940

Modify the stored procedure with an optional parameter to return either the paged functionality, or all results (the default). This should give you the functionality you need without breaking existing code.

Upvotes: 8

Related Questions