xml modify
declare @xml xml
set @xml=’<ContactInfo>
<LoginUserID>135</LoginUserID>
<Result>true</Result>
<MyLandLine1>
<MyPhone>
<Id>0</Id>
<Phone>AAA</Phone>
<ContactType>0</ContactType>
<PhoneType />
</MyPhone>
<MyPhone>
<Id>0</Id>
<Phone>BBB</Phone>
<ContactType>1</ContactType>
<PhoneType>Landline1</PhoneType>
</MyPhone>
</MyLandLine1>
</ContactInfo>’
SET @xml.modify(‘delete /ContactInfo/MyLandLine1/MyPhone[Phone[text() = "AAA"]]’)
select @xml
================
====================
select @XMLResult
@RecCount = @XMLResult.value(‘count(/AccountsList/Records/Record/RowNum[1])’,‘varchar(10)’) set @XMLResult.modify(‘replace value of (/AccountsList[1]/Count/text())[1] with sql:variable(“@RecCount”)’)
condition in COUNT using NULLIF
SELECT g.hair, ( COUNT( * ) ) AS girl_count, ( COUNT( NULLIF( did_date, 0 ) ) ) AS did_date_count, ( COUNT( NULLIF( did_date, 1 ) ) ) AS did_not_date_count FROM @girl g GROUP BY g.hair
SQL XML
INSERT INTO BL_NetworkMembers
(ContactID, NetworksID)
SELECT
Contacts.ContactID,
Network.NetworksID
FROM (
SELECT
NetworkCol.value(‘../../Email[1]‘,’VARCHAR(150)’) AS Email ,
NetworkCol.value(‘../../UID[1]‘,’bigint’) AS UID,
NetworkCol.value(‘NetworkID[1]‘,’int’) as NetworksID
FROM @XMLString.nodes(‘//Contacts/Contact/Networks/Network’) tabNetwork(NetworkCol)
)Network
JOIN @ContactTemp Contacts
ON Contacts.Email = Network.Email
AND Contacts.UID = Network.UID
WHERE ISNULL(Network.NetworksID,”) <>”
==================================================================
SELECT tab.col.value(”(..//../UID)[1]”,”bigint”) AS UID,
tab.col.value(”ChildName[1]”,”VARCHAR(100)”) AS ChildName,
tab.col.value(”ChildDOB[1]”,”SMALLDATETIME”) AS ChildDOB
FROM @XMLString.nodes(”/TestInfo/MyChildren/MyChild”) tab(col)
–<TestInfo>
–<UID>45</UID>
–<DOB>1923-03-27T00:00:00</DOB>
–<Relegion>29</Relegion>
–<RaceEthinicity>4</RaceEthinicity>
–<AnnualIncome>14</AnnualIncome>
–<Gender>1</Gender>
–<RelationShipStatus>26</RelationShipStatus>
–<AnniversaryDate>April/DD/1931</AnniversaryDate>
–<SpouceDOB>March/DD/1933</SpouceDOB>
–<Children>2</Children>
– <MyChildren>
– <MyChild>
–<ChildName>test</ChildName>
–<ChildDOB>1908-03-07T00:00:00</ChildDOB>
–</MyChild>
– <MyChild>
–<ChildName>asdfaf</ChildName>
–<ChildDOB>1923-05-27T00:00:00</ChildDOB>
–</MyChild>
–</MyChildren>
– <MyLanguages>
– <MyLanguage>
–<LanguageID>1</LanguageID>
–<LanguageName>English</LanguageName>
–</MyLanguage>
– <MyLanguage>
–<LanguageID>2</LanguageID>
–<LanguageName>Chinese</LanguageName>
–</MyLanguage>
– <MyLanguage>
–<LanguageID>3</LanguageID>
–<LanguageName>Spanish</LanguageName>
–</MyLanguage>
–</MyLanguages>
–</TestInfo>
ROOT,path
————-
select
UserDetails.UserName,
UserDetails.AddressLine1,
UserDetails.AddressLine2,
UserDetails.UserID,
states=(select state,stateid from state for xml path(‘State’),type)
from UserDetails
for xml path (‘Record’), ROOT(‘Records’)
=================================================
Get all values from node ,not the first one
—————————————–
select UserID=@LoginUserID,FieldName=’Landline1′,SharingType=2,
tab.col.value(‘(StatusContact/text())[1]‘,’VARCHAR(25)’) AS Active ,
isnull(tab.col.value(‘(Id/text())[1]‘,’VARCHAR(25)’),0) AS sort
FROM @XMLString.nodes(‘MingleCard/StatusCollections/Status/LandLine1/ContactStatus’) tab(col)
—-where tab.col.value(‘LandLine1Status[1]‘,’VARCHAR(25)’)=1
……
…….
<landline1>
- <ContactStatus>
<StatusContact>1</StatusContact>
<Id>1</Id>
</ContactStatus>
- <ContactStatus>
<StatusContact>1</StatusContact>
<Id>2</Id>
</ContactStatus>
- <ContactStatus>
<StatusContact>1</StatusContact>
<Id>3</Id>
</ContactStatus>
</landline1>
…….
===================================================================
DATALENGTH
===================================================================
SET @XMLROWCOUNT = (SELECT Result=”true”,
WorkExperienceID=isnull(WorkExperienceID,””),
CompanyName=isnull(CompanyName,””),WebsiteUrl=isnull(WebsiteUrl,””),IndustryID=isnull(IndustryID,””),
CompanyServices=isnull(Services,””),
StreetAddress=isnull(StreetAddress,””),City=isnull(City,””),Zipcode=isnull(Zipcode,””),JobTitle=isnull(JobTitle,””),
CountryID=isnull(MingleCountryCode,””)
FROM dbo.U_MyWorkExperience
WHERE UID=@loginId and ProfessionalInfo=1
FOR XML PATH(”ProfessionalInformation”))
IF DATALENGTH(@XMLROWCOUNT) > 0
BEGIN
SELECT @XMLROWCOUNT
END
ELSE
BEGIN
SELECT Result=”true”,MingleCountryCode AS CountryID FROM U_MyRegistration WHERE UserID = @loginId FOR XML PATH(”ProfessionalInformation”)
–Select Result = ”false” FOR XML PATH(”ProfessionalInformation”)
END
—————————-@XMLResult.Modify xml—————————————————–
SELECT @RecCount = @XMLResult.value(‘count(/AccountsList/Records/Record/RowNum[1])’,'varchar(10)’)
set @XMLResult.modify(‘replace value of (/AccountsList[1]/Count/text())[1] with sql:variable(“@RecCount”)’)
——————————xml custom structure———————————-
declare @XMLResult xml
SET @XMLResult = (SELECT Result=’true’,
WorkExperienceID=isnull(WorkExperienceID,”),
MyJobTitle=isnull(JobTitle,”),
isnull(CompanyName,”) AS ‘Company/Name’,
isnull(WebsiteUrl,”) AS ‘Company/WebSite’,
isnull(Services,”) AS ‘Company/Services’,
isnull(IndustryID,”) AS ‘Company/Industry/IndustryID’,
(SELECT IndustryName FROM BL_IndustryTypes WHERE IndustryID = U_MyWorkExperience.IndustryID) AS ‘Company/Industry/IndustryName’,
isnull(StreetAddress,”) AS ‘Company/CompanyAddress/StreetAddress1′,
isnull(City,”) AS ‘Company/CompanyAddress/Country/CityName’,
isnull(MingleCountryCode,”) AS ‘Company/CompanyAddress/Country/CountryID’,
(SELECT CountryName FROM BL_Country WHERE MingleCountryCode = U_MyWorkExperience.MingleCountryCode) AS ‘Company/CompanyAddress/Country/CountryName’,
isnull(CompanyEmail,”) AS ‘Company/CompanyEmail/Email’,
isnull(CompanyPhone,”) AS ‘Company/CompanyPhone/Phone’
–City=isnull(City,”),Zipcode=isnull(Zipcode,”)
FROM dbo.U_MyWorkExperience
WHERE LoginUserID=2 and ProfessionalInfo=1
FOR XML PATH(‘ProfessionalInformation’))
select @XMLResult
=———————————————————————————-
OUTPUT
=———————————————————————————-
declare @T table ( id bigint)
insert into w_sponsor(userid,SponsorLogo)
OUTPUT Inserted.sponsorid into @T
values (1,’1′)
=—-
declare @T table ( id bigint)
update w_sponsor set userid=1
OUTPUT Inserted.sponsorid into @T where sponsorid >10
select * from @T
=======================
CTE
=======================
WITH CTE_Orders AS
(
SELECT Orderid, Shipname, shippeddate ,ROW_NUMBER() OVER (ORDER BY shippeddate DESC) AS RowNumber
FROM orders
)
SELECT Orderid, Shipname, shippeddate FROM CTE_Orders WHERE RowNumber BETWEEN 1 AND 30 ORDER BY RowNumber ASC;
2) Makes querying hierarchical data easier
WITH sql_cte (emp)
AS (
SELECT emp FROM sql_org WHERE mgr = ‘Joe’
UNION ALL
SELECT a.emp FROM sql_org a JOIN sql_cte b ON b.emp = a.mgr )
SELECT * FROM sql_cte
=============================================
Error handling
=====================================================
BEGIN TRY
– Generate a constraint violation error.
DELETE FROM Production.Product
WHERE ProductID = 980;
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() as ErrorState,
ERROR_MESSAGE() as ErrorMessage;
END CATCH
———————–update method ———————————————
UPDATE C SET C.FName = A.[First Name], C.LName=A.[Last Name],
C.SourceType = A.SourceType,
C.SourceTag = A.SourceTag
FROM U_MyContacts C
INNER JOIN Mingle_ImportAddress.dbo.BL_ImportAddressForUpadte A WITH (NOLOCK)
ON C.LoginUserID=A.UserID and C.LoginUserID= @LoginUserID
AND [Email ID]=EMail and IsRedundantEmail=”true”
===========================converting email to bigint
SELECT @UID = convert(bigint,convert(varbinary(260),reverse(convert(varbinary(260),
CAST((CONVERT(varchar,@PartnerId))+@AccountEmail
AS varbinary(260)) ))))
SQL Server
differences between sqlserver2000 and 2005
1)-In SQL SERVER 2000 there where maximum 16 instances but in 2005 you can have up to 50 instances.
2)-Database mirror concept supported in SQL SERVER 2005 which was not present in SQL SERVER 2000.
3)-SQL SERVER 2005 has reporting services for reports which is a newly added feature and does not exist for SQL
SERVER 2000.It was a separate installation for SQL Server 2000.
4)-SQL Server 2005 introduces a dedicated administrator connection (DAC) to access a running server even if the
server is not responding or is otherwise unavailable. This enables you to execute diagnostic functions or
Transact-SQL statements so you can troubleshoot problems on a server. which was not present in SQL SERVER 2000.
***********
Fill factor is the term associated with indexes actually with clustured indexes
Whenever a clustured index is created sql server physically orders data in basis of the clustured column. As
you must be aware that the data is sql server is stored in 8K data page. Fill factor value setting advice SQL
Server to leave specified amount of space free on each page inorder to accommodate new data if no fill factor
is set SQL Server data page will be filled completely and incase a new record comes it will have to make space
in the data page by reorganizing rest of the pages which is an over head. And hence it is suggested to keep
reasonable value for fill factor considering future requirements.
CREATE CLUSTERED INDEX … FILLFACTOR = 33 creates a clustered index with a FILLFACTOR of 33 percent
If no value is specified, the default is 0.A fill factor value of 0 does not mean that pages are 0 percent
full.It is treated similarly to a fill factor value of 100 in that SQL Server creates clustered indexes with
full data pages and nonclustered indexes with full leaf pages
You can change the default FILLFACTOR setting by executing sp_configure.
If you set fill factor to 100, SQL Server creates both clustered and nonclustered indexes with each page 100
percent full. Setting fill factor to 100 is suitable only for read-only tables, to which additional data is
never added.
Creating a clustered index with a FILLFACTOR affects the amount of storage space the data occupies because SQL
Server redistributes the data when it creates the clustered index.
Smaller fill factor values cause each index to take more storage space, allowing room for subsequent insertions
without requiring page splits.
****************
Composite key:- A primary key that consistsof two or more
attributes is known as composite key
candidate key:- A key that uniquely identifies rows in a table .Any of identified candidate keys can be used as
primary key.
Alternate Key:- Any of the candidate keys that is not part
of the primary key is called an alternate key.It accepts null Values
*************
Magic Table in SQL Server:
Magic tables are used to put all the deleted and updated rows. We can retrieve the
column values from the deleted rows using the keyword “deleted”
DELETE Vtbl1
OUTPUT Deleted.KeyID, Deleted.Name INTO @DeletedTable WHERE KeyID > 3
SELECT * FROM @DeletedTable
( you can’t use magic table outside of the trigger in sql 2000 without output clause)
**********
The UNION ALL command is equal to the UNION command, except that UNION ALL selects all values.
A UNION statement effectively does a SELECT DISTINCT on the results set. If you know that all the records
returned are unique from your union, use UNION ALL instead, it gives faster results.
***
The following example forces the current identity value in the EmployeeID column in the Employee table to a
value of 300
DBCC CHECKIDENT ("HumanResources.Employee", RESEED, 300);
**********
Using Inline Query Hint
USE AdventureWorks
GO
SELECT c.ContactID
FROM Person.Contact c
WITH (INDEX(AK_Contact_rowguid))
INNER JOIN Person.Contact pc
WITH (INDEX(PK_Contact_ContactID))
ON c.ContactID = pc.ContactID
GO
option hint
===========
SELECT c.ContactID
FROM Person.Contact c
INNER JOIN Person.Contact pc
ON c.ContactID = pc.ContactID
OPTION (TABLE HINT(c, INDEX (AK_Contact_rowguid)),
TABLE HINT(pc, INDEX (PK_Contact_ContactID)))
script
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N’[dbo].[BL_Relatonships]‘) AND type in (N’U'))
BEGIN
CREATE TABLE [dbo].[BL_Relatonships](
[RelationShipID] [tinyint] NOT NULL,
[RelationshipName] [varchar](50) NULL,
[Description] [varchar](50) NULL,
[RelationshipMasterID] [tinyint] NULL,
[SortOrder] [tinyint] NULL
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N’[dbo].[BL_MingleUsers]‘) AND type in (N’U'))
BEGIN
CREATE TABLE [dbo].[BL_MingleUsers](
[UserID] [bigint] NOT NULL,
[UserUniqueID] [uniqueidentifier] NOT NULL CONSTRAINT [DF_BL_MingleUsers_UserUniqueID] DEFAULT (newid()),
[UserEmail] [varchar](50) NULL,
[Password] [varchar](50) NULL,
[LoggedInIpAddress] [varchar](50) NULL,
[UserLogoPath] [varchar](256) NULL,
[IsAcitive] [bit] NOT NULL CONSTRAINT [DF_U_UserLogin_IsAcitive] DEFAULT ((1))
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N’[dbo].[U_AddressBook]‘) AND type in (N’U'))
BEGIN
CREATE TABLE [dbo].[U_AddressBook](
[AddressBookID] [bigint] IDENTITY(1,1) NOT NULL,
[FName] [varchar](100) NULL,
[LName] [varchar](100) NULL,
[MName] [varchar](100) NULL,
[EMail] [varchar](50) NULL,
[CreatedDate] [smalldatetime] NULL CONSTRAINT [DF_U_Addressbook_CreatedDate] DEFAULT (getdate()),
[LoginUserId] [bigint] NULL
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N’[dbo].[U_ContactConnection]‘) AND type in (N’U'))
BEGIN
CREATE TABLE [dbo].[U_ContactConnection](
[ContactConnectionId] [bigint] IDENTITY(1,1) NOT NULL,
[LoginUserID] [bigint] NOT NULL,
[ConnectionID] [bigint] NULL,
[AddressBookID] [bigint] NULL,
[SharingContactsCount] [int] NULL
) ON [PRIMARY]
END
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N’MS_Description’ , N’SCHEMA’,N’dbo’, N’TABLE’,N’U_ContactConnection’, N’COLUMN’,N’ConnectionID’))
EXEC sys.sp_addextendedproperty @name=N’MS_Description’, @value=N’Friend id’ , @level0type=N’SCHEMA’,@level0name=N’dbo’, @level1type=N’TABLE’,@level1name=N’U_ContactConnection’, @level2type=N’COLUMN’,@level2name=N’ConnectionID’
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N’[dbo].[U_ContactRelationship]‘) AND type in (N’U'))
BEGIN
CREATE TABLE [dbo].[U_ContactRelationship](
[RelationId] [bigint] IDENTITY(1,1) NOT NULL,
[ShareMe] [bit] NOT NULL CONSTRAINT [DF_U_ContactRelationship_ShareMe] DEFAULT ((1)),
[KeepPrivate] [bit] NOT NULL CONSTRAINT [DF_U_ContactRelationship_KeepPrivate] DEFAULT ((0)),
[PersonalRelationship] [int] NOT NULL,
[ProfessionalRelationship] [int] NOT NULL,
[Notes] [varchar](1000) NULL,
[Message] [varchar](1000) NULL,
[Location] [varchar](256) NULL,
[AddressBookID] [bigint] NOT NULL,
[ConnectionRequest] [bit] NOT NULL CONSTRAINT [DF_U_ContactRelationship_ConnectionRequest] DEFAULT ((0)),
[RequestStatus] [varchar](50) NULL
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N’[dbo].[BL_RelatonShipMaster]‘) AND type in (N’U'))
BEGIN
CREATE TABLE [dbo].[BL_RelatonShipMaster](
[RelationshipMasterID] [tinyint] NULL,
[Name] [varchar](50) NOT NULL,
[Description] [varchar](150) NULL
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N’[dbo].[BL_FolderMembers]‘) AND type in (N’U'))
BEGIN
CREATE TABLE [dbo].[BL_FolderMembers](
[ContactID] [bigint] NULL,
[FoldersID] [bigint] NULL,
[Description] [varchar](50) NULL,
[SharedContactsCount] [bigint] NULL
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N’[dbo].[BL_UserFolders]‘) AND type in (N’U'))
BEGIN
CREATE TABLE [dbo].[BL_UserFolders](
[FolderID] [bigint] NOT NULL,
[FolderName] [varchar](30) NULL,
[Description] [varchar](50) NULL,
[SortOrder] [tinyint] NULL,
[ContactsCount] [int] NULL,
[ParentID] [bigint] NULL,
[LoginUserID] [bigint] NULL
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N’[dbo].[AddressBook_Insertion]‘) AND type in (N’P', N’PC’))
BEGIN
EXEC dbo.sp_executesql @statement = N’–exec [UserLogin_Validate] ”admin”, ”admin”,”123”,””,””
CREATE PROCEDURE [dbo].[AddressBook_Insertion]
(
@Email VARCHAR(150),
@Fname VARCHAR(100),
@Lname VARCHAR(100),
@Network INT
)
AS
BEGIN
BEGIN TRY
BEGIN
INSERT INTO U_AddressBook(FName,LName,EMail)
VALUES (@Fname,@Lname,@Email)
END
END TRY
BEGIN CATCH
SELECT ”ERROR””Result”
END CATCH
END
’
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N’[dbo].[BL_NetworkMembers]‘) AND type in (N’U'))
BEGIN
CREATE TABLE [dbo].[BL_NetworkMembers](
[AddressBookID] [int] NOT NULL,
[NetworksID] [int] NOT NULL
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N’[dbo].[BL_Networks]‘) AND type in (N’U'))
BEGIN
CREATE TABLE [dbo].[BL_Networks](
[NetworkID] [int] NOT NULL,
[NetworkName] [varchar](50) NULL,
[NetworkDesc] [varchar](150) NULL,
[NetworkPhotoPath] [varchar](250) NULL,
[Active] [bit] NULL CONSTRAINT [DF_BL_Networks_Active] DEFAULT ((1)),
[Visible] [bit] NULL CONSTRAINT [DF_BL_Networks_Visible] DEFAULT ((1)),
[parentNetworkID] [int] NULL,
[CreatedDate] [smalldatetime] NULL,
[UpdatedDate] [smalldatetime] NULL
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N’[dbo].[GetAllNetworks]‘) AND type in (N’P', N’PC’))
BEGIN
EXEC dbo.sp_executesql @statement = N’
CREATE procedure [dbo].[GetAllNetworks]
as
–Used To get all the network names
begin
select Networkid=case when isnull(c.Networkid,0) =0 then p.Networkid else c.Networkid end,ChildNetworkName=c.Networkname,c.NetworkDesc,p.Networkname from dbo.BL_Networks p
left outer join BL_Networks c on P.Networkid =c.parentnetworkID
where ( p.parentnetworkID = 0)
end’
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N’[dbo].[GetAllRelationships]‘) AND type in (N’P', N’PC’))
BEGIN
EXEC dbo.sp_executesql @statement = N’–select * from dbo.BL_Relatonships
CREATE procedure [dbo].[GetAllRelationships]
as
begin
select RelationshipID,Head=[name],Relationshipname from BL_RelatonShipMaster p
left outer join BL_Relatonships c on P.RelationshipMasterID =c.RelationshipMasterID order by RelationshipID,sortorder
end
‘
END
Xml – sql 2005
DECLARE @XMLString XML
SET @XMLString =’
<Customers>
<Customer>
<FirstName>Kevin</FirstName>
<LastName>Goff</LastName>
<City>Camp Hill</City>
<Schools>
<HighSchool>Don Bosco1</HighSchool>
<UG>ST1</UG>
<PG>NSS1</PG>
</Schools>
</Customer>
<Customer>
<FirstName>Steve</FirstName>
<LastName>Goff</LastName>
<City> Philadelphia </City>
<Schools>
<HighSchool>Don Bosco2</HighSchool>
<UG>ST2</UG>
<PG>NSS2</PG>
</Schools>
</Customer>
</Customers>’
SELECT
tab.col.value(‘FirstName[1]‘,’VARCHAR(20)’)AS FirstName,
tab.col.value(‘LastName[1]‘,’VARCHAR(20)’)AS LastName,
tab.col.value(‘City[1]‘,’VARCHAR(20)’)AS City,
tab.col.value(‘Schools[1]/UG[1]‘,’VARCHAR(30)’) AS UG
FROM @XMLString.nodes(‘//Customer’) tab(col)
====================================================
select
UserDetails
.UserName ,UserDetails.AddressLine1,UserDetails.AddressLine2,UserDetails.UserID, State,
State from
UserDetails
join State on UserDetails.StateID=State. StateID for xml path (‘Record’), ROOT(‘Records’)
====================================================
declare @T table ( id bigint)
insert into w_sponsor(userid,SponsorLogo)
OUTPUT Inserted.sponsorid into @T values (1,’1′)
=======================
declare @T table ( id bigint)
update w_sponsor set userid=1
OUTPUT Inserted.sponsorid into @T where sponsorid >10
select * from @T
=======================
WITH CTE_Orders AS
(
SELECT Orderid, Shipname, shippeddate ,ROW_NUMBER() OVER (ORDER BY shippeddate DESC) AS RowNumber
FROM orders
)
SELECT Orderid, Shipname, shippeddate FROM CTE_Orders WHERE RowNumber BETWEEN 1 AND 30 ORDER BY RowNumber ASC;
=====================
Here 2 simple methods are explained which can accomplish paging at the Database level.
Method 2
———–
create procedure Paging_Sp2
@RecsPerPage int,
@Page int
as
begin
SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY Productid) AS Rownum, * FROM Products) AS Products_T
WHERE Rownum >= @RecsPerPage*(@Page-1) AND Rownum <= @RecsPerPage*(@Page)
end
Mail ,GetBodyAsHtml,GetToList from Db
Private Function GetBodyAsHtml(ByVal Bodytext As String, ByVal HeaderText As String, ByVal FooterText As String) As String
Dim sb = New System.Text.StringBuilder
sb.Append(“Tasks”)
sb.Append(“
| “) sb.Append(“
“) Return sb.ToString Try cmdSelect.Connection = _sqlConnection cmdSelect.CommandText = “Select Name=MemEmail from Member1 where id= ‘” & Strid & “‘” da = New SqlDataAdapter(cmdSelect) For Each row As DataRow In ds.Tables(“Tasks”).Rows ’sends Mail Dim strPath As String = “” Try ‘getting body for mail Arrtoid = Split(ToId, “,”) If Trim(FromId) “” Then NewMessage.From = New MailAddress(FromId) For Each strT In Arrtoid NewMessage.Subject = Subject If Trim(AttachmentPath) “” Then NewMessage.IsBodyHtml = True Catch ex As Exception End Function ========================================================================= Convert Datatable To Html File,SaveAsHtmlFile (ByVal renderedOutput As StringBuilderPrivate Function ConvertToHtmlFile(ByVal sentDataTable As DataTable) As String ‘ Check if the Sent DataTable is not empty or a Null ‘Get a worker object. ‘Open tags and write the top portion.
“) Return SaveAsHtmlFile(HtmlStringBuilder) HtmlStringBuilder = Nothing End Function Private Function SaveAsHtmlFile(ByVal renderedOutput As StringBuilder) As String desktop application Application.StartupPath & “..\..\..\Images”Dim path As String = Application.StartupPath & “..\..\..\Images” Date validations in a text box…javascript<html xmlns=”http://www.w3.org/1999/xhtml“> function isInteger(s){ function stripCharsInBag(s, bag){ function daysInFebruary (year){ function isDate(dtStr){ function ValidateForm(){
|
