SQL Server Solutions

Query in SQL Server to get the Parameter list of given Store Procedure

2009-11-21T11:59:00.000-08:00

Write a Query in SQL Server to get the Parameter list of given Store Procedure.

Suppose we want to get the name of the parameter for the Store Procedure
Course_Insert_sp then folllowing needs to be executed.

SELECT * FROM sys.parameters D INNER JOIN Sys.Objects O ON O.object_id=D.object_id WHERE O.name='Course_Insert_sp'

Write a SQL Statement to Get the Definition of the Store Procedures

2009-11-21T11:58:00.000-08:00

SQL Statement to Get the Definition of the System Store Procedures.

SELECT definition FROM sys.system_sql_modules

SQL Statement to Get the Definition of the User defined Store Procedures.

SELECT definition FROM sys.sql_modules

Query to Get the Forgien Key Name Their Primary Table and Referrence Table

2009-11-21T11:54:00.001-08:00

Following Query will give you the List of Forgien Key in a Database with Their Primary and Referrence Tables Name.

Sample Query:

SELECT F.name AS ForgienKey,O2.name AS ParentTable,O3.name AS RefTable FROM sys.foreign_keys F
INNER JOIN Sys.Objects O2 ON O2.object_id=F.parent_object_id
INNER JOIN Sys.Objects O3 ON O3.object_id=F.referenced_object_id

If you want to get forgien key for a particular table then you can use like this:

SELECT F.name AS ForgienKey,O2.name AS ParentTable,O3.name AS RefTable FROM sys.foreign_keys F
INNER JOIN Sys.Objects O2 ON O2.object_id=F.parent_object_id
INNER JOIN Sys.Objects O3 ON O3.object_id=F.referenced_object_id
Where O3.name='Tbl_User'

Query to get the name of the identity column for a given table

2009-11-21T11:53:00.000-08:00

Query to get the name of the identity column for a given table.
To Get the Identity column name for Tbl_Customer we need to execute the following query.

SELECT D.name AS ColumnName, O.name AS TableName
FROM sys.identity_columns D INNER JOIN Sys.Objects O ON O.object_id=D.object_id
Where O.name='Tbl_Customer

Query to Get the dependencies for given Table Name

2009-11-21T11:51:00.000-08:00

Following Query is used to get the dependencies for the given Table.
Suppose we need to check the dependencies for the table tbl_Customer

Sample Query:

DECLARE @Objectname varchar(100)
SET @Objectname='tbl_Customer'

SELECT O1.name,O2.name FROM sys.sql_dependencies D INNER JOIN Sys.Objects O1 ON O1.object_id=D.object_id
INNER JOIN Sys.Objects O2 ON O2.object_id=D.referenced_major_id
WHERE O1.name=@Objectname OR O2.name=@Objectname

Interview Questions

2009-10-27T09:35:00.000-07:00

(1) How To Update Description Value for a Column in Table using SQL Command?
We can Update Description to Column using sp_updateextendedproperty System Store Procedure.
Sample Command to Update Description for Column in a Table:

EXEC sys.sp_updateextendedproperty @name=N'MS_Description', @value=N'My Description for Column Here' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'MyTableName', @level2type=N'COLUMN',@level2name=N'ColumnName'

(2) How To Delete Description Value for a Column in Table using SQL Command?
We can Delete Description from Column using sp_dropextendedproperty System Store Procedure.
Sample Command to Delete Description from Column in a Table:

EXEC sys.sp_dropextendedproperty @name=N'MS_Description', @value=N'My Description for Column Here' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'MyTableName', @level2type=N'COLUMN',@level2name=N'ColumnName'

(3) How we can add Description to the Column using Sql Command?
We can Add Description to Column using sp_addextendedproperty System Store Procedure.
Sample Command to Insert Description for Column in a Table:

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'My Description for Column Here' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'MyTableName', @level2type=N'COLUMN',@level2name=N'ColumnName'

(4) How To Get Description Value for all Column in Table using SQL Command?
To Get Description of Columns we need to use system function sys.fn_listextendedproperty.
Command To Get Description Data for all Columns:

SELECT * FROM fn_listextendedproperty(NULL, 'SCHEMA',
'dbo', 'TABLE', 'YourTable Name Here', 'COLUMN', NULL)

(5) How To Get Description Value for Single Column in Table using SQL Command?
To Get Description of Single Column we need to use system function sys.fn_listextendedproperty. we need to pass the column Name is this case.
Command To Get Description Data for Single Columns:

SELECT * FROM fn_listextendedproperty(NULL, 'SCHEMA',
'dbo', 'TABLE', 'Table Name Here', 'COLUMN', 'Column Name Here')

(6) How We can get the DB name using SQL Command?
Following is the Command to get the DB name using Command analyzer

SELECT DB_NAME()

(7) What is the use of Set NOCOUNT ON;?
By Default When we execute any command it return us the number of record affected. if we don't want to return the number of records affected then we can use

SET NOCOUNT ON;

Store Procedure To Create Script for Create Table

2009-10-27T09:17:00.001-07:00

Following is the store procedure to create script for create table. this will create script for all constraint, description of columns, and index etc.

Store Procedure Script:

ALTER PROCEDURE sp_CreateTableScript
(
@TableName SYSNAME,
@IncludeConstraints BIT = 1,
@IncludeIndexes BIT = 1,
@NewTableName SYSNAME = NULL,
@UseSystemDataTypes BIT = 0
)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @MainDefinition TABLE
(
FieldValue NVARCHAR(500)
)
DECLARE @DBName SYSNAME
DECLARE @ClusteredPK BIT
DECLARE @TableSchema NVARCHAR(255)
SET @DBName = DB_NAME(DB_ID())
SELECT @TableName = name FROM sysobjects WHERE id = OBJECT_ID(@TableName)
DECLARE @ShowFields TABLE
(
FieldID INT IDENTITY(1,1),
DatabaseName VARCHAR(100),
TableOwner VARCHAR(100),
TableName VARCHAR(100),
FieldName VARCHAR(100),
ColumnPosition INT,
ColumnDefaultValue VARCHAR(100),
ColumnDefaultName VARCHAR(100),
IsNullable BIT,
DataType VARCHAR(100),
MaxLength INT,
NumericPrecision INT,
NumericScale INT,
DomainName VARCHAR(100),
FieldListingName VARCHAR(110),
FieldDefinition CHAR(1),
IdentityColumn BIT,
IdentitySeed INT,
IdentityIncrement INT,
IsCharColumn BIT
)
DECLARE @HoldingArea TABLE
(
FldID SMALLINT IDENTITY(1,1),
Flds VARCHAR(4000),
FldValue CHAR(1) DEFAULT(0)
)
DECLARE @PKObjectID TABLE
(
ObjectID INT
)
DECLARE @Uniques TABLE
(
ObjectID INT
)
DECLARE @HoldingAreaValues TABLE
(
FldID SMALLINT IDENTITY(1,1),
Flds VARCHAR(4000),
FldValue CHAR(1) DEFAULT(0)
)
DECLARE @Definition TABLE
(
DefinitionID SMALLINT IDENTITY(1,1),
FieldValue NVARCHAR(500)
)
INSERT INTO @ShowFields
(
DatabaseName,
TableOwner,
TableName,
FieldName,
ColumnPosition,
ColumnDefaultValue,
ColumnDefaultName,
IsNullable,
DataType,
MaxLength,
NumericPrecision,
NumericScale,
DomainName,
FieldListingName,
FieldDefinition,
IdentityColumn,
IdentitySeed,
IdentityIncrement,
IsCharColumn
)
SELECT
DB_NAME(),
TABLE_SCHEMA,
TABLE_NAME,
COLUMN_NAME,
CAST(ORDINAL_POSITION AS INT),
COLUMN_DEFAULT,
dobj.name AS ColumnDefaultName,
CASE WHEN c.IS_NULLABLE = 'YES' THEN 1 ELSE 0 END,
DATA_TYPE,
CAST(CHARACTER_MAXIMUM_LENGTH AS INT),
CAST(NUMERIC_PRECISION AS INT),
CAST(NUMERIC_SCALE AS INT),
DOMAIN_NAME,
COLUMN_NAME + ',','' AS FieldDefinition,
CASE WHEN ic.object_id IS NULL THEN 0 ELSE 1 END AS IdentityColumn,
CAST(ISNULL(ic.seed_value,0) AS INT) AS IdentitySeed,
CAST(ISNULL(ic.increment_value,0) AS INT) AS IdentityIncrement,
CASE WHEN st.collation_name IS NOT NULL THEN 1 ELSE 0 END AS IsCharColumn
FROM
INFORMATION_SCHEMA.COLUMNS c
JOIN sys.columns sc ON c.TABLE_NAME = OBJECT_NAME(sc.object_id) AND c.COLUMN_NAME = sc.Name
LEFT JOIN sys.identity_columns ic ON c.TABLE_NAME = OBJECT_NAME(ic.object_id) AND c.COLUMN_NAME = ic.Name
JOIN sys.types st ON COALESCE(c.DOMAIN_NAME,c.DATA_TYPE) = st.name
LEFT OUTER JOIN sys.objects dobj ON dobj.object_id = sc.default_object_id AND dobj.type = 'D'
WHERE c.TABLE_NAME = @TableName
ORDER BY
c.TABLE_NAME, c.ORDINAL_POSITION
SELECT TOP 1 @TableSchema = TableOwner
FROM @ShowFields
INSERT INTO @HoldingArea (Flds) VALUES('(')
INSERT INTO @Definition(FieldValue)
VALUES('CREATE TABLE ' + CASE WHEN @NewTableName IS NOT NULL THEN @NewTableName ELSE @TableSchema + '.' + @TableName END)
INSERT INTO @Definition(FieldValue)
VALUES('(')
INSERT INTO @Definition(FieldValue)
SELECT
CHAR(10) + FieldName + ' ' +
CASE
WHEN DomainName IS NOT NULL AND @UseSystemDataTypes = 0 THEN
DomainName + CASE WHEN IsNullable = 1 THEN ' NULL ' ELSE ' NOT NULL ' END
ELSE UPPER(DataType) +
CASE WHEN IsCharColumn = 1 THEN '(' + CAST(MaxLength AS VARCHAR(10)) + ')' ELSE '' END +
CASE WHEN IdentityColumn = 1 THEN
' IDENTITY(' + CAST(IdentitySeed AS VARCHAR(5))+ ',' + CAST(IdentityIncrement AS VARCHAR(5)) + ')' ELSE '' END +
CASE WHEN IsNullable = 1 THEN ' NULL ' ELSE ' NOT NULL ' END +
CASE WHEN ColumnDefaultName IS NOT NULL AND @IncludeConstraints = 1 THEN
'CONSTRAINT [' + ColumnDefaultName + '] DEFAULT' + UPPER(ColumnDefaultValue) ELSE '' END
END +
CASE WHEN FieldID = (SELECT MAX(FieldID) FROM @ShowFields) THEN '' ELSE ',' END
FROM @ShowFields
IF @IncludeConstraints = 1
BEGIN
INSERT INTO @Definition(FieldValue)
SELECT
',CONSTRAINT [' + name + '] FOREIGN KEY (' + ParentColumns + ') REFERENCES [' + ReferencedObject + '](' + ReferencedColumns + ')'
FROM
(
SELECT
ReferencedObject = OBJECT_NAME(fk.referenced_object_id), ParentObject = OBJECT_NAME(parent_object_id),fk.name,
REVERSE(SUBSTRING(REVERSE((
SELECT cp.name + ','
FROM
sys.foreign_key_columns fkc
JOIN sys.columns cp ON fkc.parent_object_id = cp.object_id AND fkc.parent_column_id = cp.column_id
WHERE fkc.constraint_object_id = fk.object_id
FOR XML PATH('')
)), 2, 8000)) ParentColumns,
REVERSE(SUBSTRING(REVERSE((
SELECT cr.name + ','
FROM
sys.foreign_key_columns fkc
JOIN sys.columns cr ON fkc.referenced_object_id = cr.object_id AND fkc.referenced_column_id = cr.column_id
WHERE fkc.constraint_object_id = fk.object_id
FOR XML PATH('')
)), 2, 8000)) ReferencedColumns
FROM sys.foreign_keys fk
) a
WHERE ParentObject = @TableName
INSERT INTO @Definition(FieldValue)
SELECT',CONSTRAINT [' + name + '] CHECK ' + definition FROM sys.check_constraints
WHERE OBJECT_NAME(parent_object_id) = @TableName
INSERT INTO @PKObjectID(ObjectID)
SELECT DISTINCT
PKObject = cco.object_id
FROM
sys.key_constraints cco
JOIN sys.index_columns cc ON cco.parent_object_id = cc.object_id AND cco.unique_index_id = cc.index_id
JOIN sys.indexes i ON cc.object_id = i.object_id AND cc.index_id = i.index_id
WHERE
OBJECT_NAME(parent_object_id) = @TableName AND
i.type = 1 AND
is_primary_key = 1
INSERT INTO @Uniques(ObjectID)
SELECT DISTINCT
PKObject = cco.object_id
FROM
sys.key_constraints cco
JOIN sys.index_columns cc ON cco.parent_object_id = cc.object_id AND cco.unique_index_id = cc.index_id
JOIN sys.indexes i ON cc.object_id = i.object_id AND cc.index_id = i.index_id
WHERE
OBJECT_NAME(parent_object_id) = @TableName AND
i.type = 2 AND
is_primary_key = 0 AND
is_unique_constraint = 1
SET @ClusteredPK = CASE WHEN @@ROWCOUNT > 0 THEN 1 ELSE 0 END
INSERT INTO @Definition(FieldValue)
SELECT ',CONSTRAINT ' + name + CASE type WHEN 'PK' THEN
' PRIMARY KEY ' + CASE WHEN pk.ObjectID IS NULL THEN ' NONCLUSTERED ' ELSE ' CLUSTERED ' END
WHEN 'UQ' THEN ' UNIQUE ' END + CASE WHEN u.ObjectID IS NOT NULL THEN ' NONCLUSTERED ' ELSE '' END + '(' +
REVERSE(SUBSTRING(REVERSE((
SELECT
c.name + + CASE WHEN cc.is_descending_key = 1 THEN ' DESC' ELSE ' ASC' END + ','
FROM
sys.key_constraints ccok
LEFT JOIN sys.index_columns cc ON ccok.parent_object_id = cc.object_id AND cco.unique_index_id = cc.index_id
LEFT JOIN sys.columns c ON cc.object_id = c.object_id AND cc.column_id = c.column_id
LEFT JOIN sys.indexes i ON cc.object_id = i.object_id AND cc.index_id = i.index_id
WHERE
i.object_id = ccok.parent_object_id AND
ccok.object_id = cco.object_id
FOR XML PATH('')
)), 2, 8000)) + ')'
FROM
sys.key_constraints cco
LEFT JOIN @PKObjectID pk ON cco.object_id = pk.ObjectID
LEFT JOIN @Uniques u ON cco.object_id = u.objectID
WHERE
OBJECT_NAME(cco.parent_object_id) = @TableName
END
INSERT INTO @Definition(FieldValue)
VALUES(')')
INSERT INTO @Definition(FieldValue)
VALUES('GO')
IF @IncludeIndexes = 1
BEGIN
INSERT INTO @Definition(FieldValue)
SELECT
ISNULL(('CREATE ' + type_desc + ' INDEX [' + [name] COLLATE SQL_Latin1_General_CP1_CI_AS + '] ON [' + OBJECT_NAME(object_id) + '] (' +
REVERSE(SUBSTRING(REVERSE((
SELECT name + CASE WHEN sc.is_descending_key = 1 THEN ' DESC' ELSE ' ASC' END + ','
FROM
sys.index_columns sc
JOIN sys.columns c ON sc.object_id = c.object_id AND sc.column_id = c.column_id
WHERE
OBJECT_NAME(sc.object_id) = @TableName AND
sc.object_id = i.object_id AND
sc.index_id = i.index_id
ORDER BY index_column_id ASC
FOR XML PATH('')
)), 2, 8000)) + ')' ),'')
FROM sys.indexes i
WHERE
OBJECT_NAME(object_id) = @TableName
AND CASE WHEN @ClusteredPK = 1 AND is_primary_key = 1 AND type = 1 THEN 0 ELSE 1 END = 1
AND is_unique_constraint = 0
AND is_primary_key = 0
END
INSERT INTO @Definition(FieldValue)
SELECT 'EXEC sys.sp_addextendedproperty @name=N'''+name+''', @value=N'''+CONVERT(varchar(100),value)+''' , @level0type=N''SCHEMA'',@level0name=N''dbo'', @level1type=N''TABLE'',@level1name=N'''+@TableName+''',
@level2type=N''COLUMN'',@level2name=N'''+objName+''''
FROM ::fn_listextendedproperty(NULL, 'SCHEMA',
'dbo', 'TABLE', @TableName, 'COLUMN', NULL) WHERE CONVERT(varchar(100),value)<>''

INSERT INTO @MainDefinition(FieldValue)
SELECT FieldValue FROM @Definition
ORDER BY DefinitionID ASC
SELECT * FROM @MainDefinition
END

How to execute the above store procedure and get the create table script:

EXEC sp_CreateTableScript 'TableName'

Create Script File For Each Store Procedure And Save into Seperate SQL File For Each

2009-10-27T09:04:00.001-07:00

With the help of sqlcmd utility we can save the script of store procedure in separate file for each store procedure.

Name of the file will be same as the name of the store procedure.
Sample Code to Create Script and Save:

select N'sqlcmd -U sa -P sa -S localhost -d DataBaseName -Q "exec sp_helptext ' + name + N' " -h-1 -k 1 >> c:\DB\sp\' + name + N'.sql' from sys.objects where type='P'

When you will execute the above command the following output will be generated based on the number of store procedure in your database:

sqlcmd -U sa -P sa -S localhost -d DataBaseName -Q "exec sp_helptext User_Authenticate_sp " -h-1 -k 1 >> c:\DB\sp\User_Authenticate_sp.sql
sqlcmd -U sa -P sa -S localhost -d DataBaseName -Q "exec sp_helptext User_GetPassword_sp " -h-1 -k 1 >> c:\DB\sp\User_GetPassword_sp.sql
sqlcmd -U sa -P sa -S localhost -d DataBaseName -Q "exec sp_helptext sp_upgraddiagrams " -h-1 -k 1 >> c:\DB\sp\sp_upgraddiagrams.sql
sqlcmd -U sa -P sa -S localhost -d DataBaseName -Q "exec sp_helptext sp_helpdiagrams " -h-1 -k 1 >> c:\DB\sp\sp_helpdiagrams.sql
sqlcmd -U sa -P sa -S localhost -d DataBaseName -Q "exec sp_helptext sp_helpdiagramdefinition " -h-1 -k 1 >> c:\DB\sp\sp_helpdiagramdefinition.sql

Save these output data into a batch file execute this batch file. this will save the store procedure script on C:\DB\sp

Parse XML Data in SQL Server 2005 without using OPENXML Method

2009-10-05T06:07:00.000-07:00

In SQL Server 2005 we can parse the XML Data without using OPENXML Methods that was used in SQL Server 2000.
There are few methods defined in SQL Server 2005 for XML DataType like:

nodes
query
value

Sample Code:

DECLARE @XmlData xml
SET @XmlData='<Library>
    <Subject name="ASP.NET">
      <Book ID="1">
        <Author>Lakhan Pal Garg</Author>
        <Title>ASP.NET Tips</Title>
        <Price>$100</Price>
      </Book>
      <Book ID="2">
        <Author>Lakhan Pal Garg</Author>
        <Title>SQL Server Tips</Title>
        <Price>$90</Price>
      </Book>
    </Subject>
    <Subject name="XML">
      <Book ID="3">
        <Author>Peter</Author>
        <Title>XSLT Tutorial</Title>
        <Price>$140</Price>
      </Book>
      <Book ID="4">
        <Author>Rihana</Author>
        <Title>XML Parsing in SQL Server</Title>
        <Price>$120</Price>
      </Book>
    </Subject>
</Library>'

select R.i.value('@ID', 'varchar(30)')   [BookID],
       R.i.query('Author').value('.', 'varchar(30)')   [Author],
       R.i.query('Title').value('.', 'varchar(30)')   [Title],
       R.i.query('Price').value('.', 'varchar(30)')   [Price]
from @XmlData.nodes('/Library/Subject/Book') R(i)

In the above Select Statement we have used @XmlData.nodes and this will return a node list we used the Alias for this "R" and i is the index of the node. now to read the value of a attribute we can use R.i.value('@ID','INT') [BookID] here BookID is Alias name for column. and to read the value of an element that is child of Book we need to write like this R.i.query('Author').value('.','varchar(30)') [AuthorName] Author is the name of Child element of Book.

Create XML Data From SQL Server Using For XML Explicit

2009-10-05T06:05:00.000-07:00

There are different ways to get data in XML format from SQL Server like:

For XML Auto
For XML RAW
For XML Explicit

But in the first and second method we can't customize the format of XML. if we need customize XML then we need to use "For XML Explicit Method" With the Help of "For XML Explicit" we can create XML in the required format.

'Tag' and 'Parent' Column are used to determine the hierarchy of xml.
Now this code will generate a parent node name root and Session as Child of Root.

[Session!2!ID] this will create an attribute of Session Node Named ID.
and [Session!2!Notes!element] this will create a Child element of Session Named Notes.

Sample Code:
SELECT 1 AS Tag,
NULL AS Parent,
NULL AS [Session!2!ID],
NULL AS [Session!2!Notes!element],
0 AS [root!1!Customer!hide]
UNION ALL
SELECT 2 AS Tag,
1 AS Parent,
Session.SessionID AS [Session!2!ID],
Session.Notes AS [Session!2!Notes!element],
1 AS [root!1!Customer!hide]
FROM tbl_Session AS Session WHERE Session.SessionID<20
FOR XML EXPLICIT

Get Total Number of a Particular Day between Two Dates

2009-09-26T03:22:00.000-07:00

From this Set of Query we can find out the occurance of a particular day between two given dates.

if you want to get calculate total no of sat then set @DayNumber=7 and for Sun Set @DayNumber=1, Mon @DayNumber=2 and so on..

DECLARE @Count int, @Startdate Datetime,@EndDate datetime, @DayNumber int
SET @Count=0
SET @Startdate=GETDATE()-1000
SET @EndDate=GETDATE()
SET @DayNumber=7
DECLARE @StartDay Int

WHILE(@EndDate>@Startdate)
BEGIN
SET @StartDay=DATEPART(dw, @Startdate)
if(@StartDay=7)
BEGIN
SET @Count=@Count+1
SET @Startdate=@Startdate+7
END
else
BEGIN
SET @StartDay=@DayNumber-@StartDay
SET @Startdate=@Startdate+@StartDay
END
END
SELECT @Count

Store Procedure to be Used in Custom Pagging

2009-09-25T23:33:00.001-07:00

With the help of the below set of query we can get the number of record that we want to show to user.

For this we need to pass two parameters. first is the @RecordsToPick (Number of Records that you want to picjk for the page) and the second is @PageNumber (Page number for which you want to get the records from Database.)

USE DBName
GO
DECLARE @RecordsToPick smallint, @PageNumber smallint
SET @RecordsToPick = 10
SET @PageNumber = 2

DECLARE @StartRow INT
DECLARE @EndRow INT
SET @StartRow = ((@PageNumber-1) * @RecordsToPick)
SET @EndRow = @StartRow + @RecordsToPick

SELECT * FROM ( SELECT UserName,UserID,City,State,Country ROW_NUMBER()OVER(ORDER BY UserID) AS RowNumber
FROM as_TblMembers) As AliasName WHERE RowNumber > @StartRow AND RowNumber <= @EndRow GO

In the above store procedure first we will get the @StartRow and @EndRow to get the number of first record and last record respectively.

ROW_NUMBER()OVER(ORDER BY UserID) AS RowNumber

ROW_NUMBER() will assign a unique number to each query order by UserID. with the help of it is easy for us to get the required result.

Create Web Service

2009-09-23T13:06:00.000-07:00

MS SQL Server 2005 provides us functionality to create web service using HTTP Endpoints. First of all you need to create HTTP ENDPOINT only then HTTP/SOAP can access SQL Server.

Different values that can be used to create web service are:
State can have the following values

STARTED Means Listening and Responding
DISABLED Means Neither Listening nor Responding
STOPPED Means Listening But Not Responding

HTTP or TCP can be used as transport protocol. AS HTTP Can have following arguments.

Path: virtual path on the web server where the webservice will reside.
Authentication: it can be of three types
- INTEGRATED: Most Secure
- DIGEST: Not Secure
- BASIC: Least Secure
Ports: can be of two types
- CLEAR: (HTTP - port 80 by default)
- SSL: (HTTPS - port 443 by default)
Site: Name of the server where service is running

Arguments of For SOAP:

WEBMETHOD: Name of the Webmethod
NAME: write name of the store procedure here
BATCHES: can be of two types
- ENABLED means multiple SOAP request/response message pairs can be identified as part of a single SOAP session
- DISABLED means only SOAP request/response message pairs can be identified as part of a single SOAP session
DATABASE : Write name of the database
WSDL: Specify how the Document Generation Will Occur
- DEFAULT means response to the request will be generated using default format.
- NONE means no response is generated or returned for query.
NAMESPACE: URL of the web service

CREATE ENDPOINT wsUserData
STATE= STARTED
AS HTTP
(
PATH = '/myWebservices'
AUTHENTICATION= INTEGRATED,
PORTS= CLEAR,
SITE='localhot'
)
FOR SOAP
(
WEBMETHOD 'GetUserData'
(NAME='myDB.dbo.GetUserData_sp'),
BATCHES= DISABLED,
WSDL= DEFAULT,
DATABASE = 'myDB',
NAMESPACE= 'http://localhost/myWebservices'
)