Friday, July 23, 2010
Thursday, June 3, 2010
implement paging in SQL Server stored procedure
CREATE PROCEDURE [dbo].[GetMemberList]
@startIndex INT = 1,
@maxRecords INT = 10
AS
BEGIN TRY
-- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @start_id INT, @total_rec INT;
SET ROWCOUNT @startIndex;
SELECT @start_id = Id, @total_rec = COUNT(Id) OVER() FROM Members
ORDER BY Id;
SET ROWCOUNT @maxRecords;
SELECT mem.*, @total_rec AS TotalRecords FROM Members mem
WHERE mem.Id <= @start_id
ORDER BY mem.Id;
SET ROWCOUNT 0;
END TRY
BEGIN CATCH
EXEC [IErrorLog];
END CATCH
@startIndex INT = 1,
@maxRecords INT = 10
AS
BEGIN TRY
-- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @start_id INT, @total_rec INT;
SET ROWCOUNT @startIndex;
SELECT @start_id = Id, @total_rec = COUNT(Id) OVER() FROM Members
ORDER BY Id;
SET ROWCOUNT @maxRecords;
SELECT mem.*, @total_rec AS TotalRecords FROM Members mem
WHERE mem.Id <= @start_id
ORDER BY mem.Id;
SET ROWCOUNT 0;
END TRY
BEGIN CATCH
EXEC [IErrorLog];
END CATCH
Friday, February 19, 2010
Convert multiple rows into one row - SQL Server
As I need to send email to many people, i need to convert multiple emails into a single row delimited by semi-colon(;), i had lots of solutions, but which is an old type of solution which needs more lines of code. As i want to use one or two line code which would resolve, i found three methods for my solution which is very simple.
Method 1:
DECLARE @str varchar(4000)
SET @str = (SELECT CONTACT_EMAIL + ';' FROM table FOR XML PATH(''))
SET @str = SUBSTRING(@str,1,LEN(@str)-1)
SELECT @str
Method 2:
DECLARE @str varchar(4000)
SELECT @str = COALESCE(@str + ';', '') + CONTACT_EMAIL FROM table
SELECT @str
Method 3:
DECLARE @str varchar(4000)
SELECT DISTINCT STUFF( (SELECT CONTACT_EMAIL + ';' from table FOR XML PATH('')),1,1,'')
SELECT @str
Multiple rows returned:
CONTACT_EMAIL
abc1@domain.com
abc2@domain.com
abc3@domain.com
3 row(s) affected.
After executing one of the methods, i got the result as
CONTACT_EMAIL
abc1@domain.com;abc2@domain.com;abc3@domain.com;
1 row(s) affected.
NOTE: I tried these methods in SQL SERVER 2005. Some of them might not work in earlier versions.
Method 1:
DECLARE @str varchar(4000)
SET @str = (SELECT CONTACT_EMAIL + ';' FROM table FOR XML PATH(''))
SET @str = SUBSTRING(@str,1,LEN(@str)-1)
SELECT @str
Method 2:
DECLARE @str varchar(4000)
SELECT @str = COALESCE(@str + ';', '') + CONTACT_EMAIL FROM table
SELECT @str
Method 3:
DECLARE @str varchar(4000)
SELECT DISTINCT STUFF( (SELECT CONTACT_EMAIL + ';' from table FOR XML PATH('')),1,1,'')
SELECT @str
Multiple rows returned:
CONTACT_EMAIL
abc1@domain.com
abc2@domain.com
abc3@domain.com
3 row(s) affected.
After executing one of the methods, i got the result as
CONTACT_EMAIL
abc1@domain.com;abc2@domain.com;abc3@domain.com;
1 row(s) affected.
NOTE: I tried these methods in SQL SERVER 2005. Some of them might not work in earlier versions.
Subscribe to:
Posts (Atom)