Update using XML part2

ALTER proc [dbo].[sp_Update_ModelPrice]
(
@xmlString varchar(max)
)
as
DECLARE @index int
--set @xmlString='<brand><model id="1"><category id="1"><subCatid>1</subCatid><price>12</price></category></model></brand>'
BEGIN
EXEC sp_xml_preparedocument @index OUTPUT, @xmlString
update dbo.tblModelPrice
set Price=xmlprice

FROM OPENXML (@index, 'brand/model/category')
WITH (xmlModelId int '../@id',xmlCatid Varchar(10) '@id',xmlsubCat varchar(100) 'subCatid',xmlprice varchar(100) 'price')
where ModelID=xmlModelId and ModelCategoryID=xmlCatid and ModelSubCategoryID=xmlsubCat

EXEC sp_xml_removedocument @index

END

Update tale using XML date

1. Create this table:

   CREATE TABLE Employee(eid int, fname varchar(20), lname varchar(20))
   

2. Add sample data:

   INSERT INTO Employee VALUES (1, 'Nancy', 'Davolio')
   INSERT INTO Employee VALUES (2, 'Andrew', 'Fuller')
   

3. Create this stored procedure in the database:

   CREATE PROC sp_update_employee @empdata ntext     
   AS     
   DECLARE @hDoc int       
   exec sp_xml_preparedocument @hDoc OUTPUT,@empdata  
   UPDATE Employee     
   SET     
         Employee.fname = XMLEmployee.fname,    
         Employee.lname = XMLEmployee.lname      
   FROM OPENXML(@hDoc, '/root/Employee')   
                WITH Employee XMLEmployee    
   WHERE  Employee.eid = XMLEmployee.eid    
   EXEC sp_xml_removedocument @hDoc    
   SELECT   *    
   from      Employee 
   FOR XML AUTO    

delete object from database

DECLARE @Sql NVARCHAR(500) DECLARE @Cursor CURSOR
SET @Cursor = CURSOR FAST_FORWARD FOR
SELECT DISTINCT sql = 'ALTER TABLE [' + tc2.TABLE_NAME + '] DROP [' + rc1.CONSTRAINT_NAME + ']'
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc1
LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc2 ON tc2.CONSTRAINT_NAME =rc1.CONSTRAINT_NAME
OPEN @Cursor FETCH NEXT FROM @Cursor INTO @Sql
WHILE (@@FETCH_STATUS = 0)
BEGIN
Exec SP_EXECUTESQL @Sql
FETCH NEXT FROM @Cursor INTO @Sql
END
CLOSE @Cursor DEALLOCATE @Cursor
GO
EXEC sp_MSForEachTable 'DROP TABLE ?'
GO
DECLARE @procedureName varchar(500)
DECLARE cur CURSOR
FOR SELECT [name] FROM sys.objects WHERE type = 'p'
OPEN cur
FETCH NEXT FROM cur INTO @procedureName
WHILE @@fetch_status = 0
BEGIN
EXEC('DROP PROCEDURE ' + @procedureName)
FETCH NEXT FROM cur INTO @procedureName
END
CLOSE cur
DEALLOCATE cur



























#region Enail
                            var MailDetails = (from objemail in objDb.tblEmailSettings where objemail.EmailSettingsID == EmailSettingsID && objemail.IsActive == true select objemail).Single();
                            if (userDetail != null)
                            {
                                if (MailDetails.EmailSettingsID > 0)
                                {
                                    Utility formail = new Utility();
                                    string Tomail = userDetail.Email;
                                    //string FromMail = System.Configuration.ConfigurationManager.AppSettings["MailUid"].ToString();
                                    string FromMail = "kesri.software@gmail.com";
                                    string Subject = MailDetails.EmailSubject;
                                    string Body = MailDetails.EmailBody;
                                    //[First Name]&lt;br /&gt;  [Middle Name]&lt;br /&gt;  [Last Name]&lt;br /&gt;  [Email]&lt;br /&gt;  [Password]&lt;br /&gt;  [ApplicationId]&lt;br /&gt;  [Coupon Code]&lt;br /&gt;  [Message]&lt;br /&gt;  [Site Address]&lt;br /&gt;
                                    Body = Body.Replace("[First Name]", userDetail.First_Name);
                                    Body = Body.Replace("[Middle Name]", "");
                                    Body = Body.Replace("[Last Name]", userDetail.Last_Name);
                                    Body = Body.Replace("[Email]", userDetail.Email);

                                    string pas = ClsDataEncryption.Decrypt(userDetail.Password.Trim(), "passKey");

                                    Body = Body.Replace("[Password]", pas);
                                    Body = Body + "  http://www.globicle.com";
                                    Body = HttpUtility.HtmlDecode(Body);
                             
                                    bool u = formail.SendMail(Tomail, FromMail, Subject, Body);
                                    if (u == true)
                                    {
                                        return Json(new ClsJsonResult { Result = true });
                                    }
                                    else
                                    {
                                        return Json(new ClsJsonResult { Result = false });
                                    }
                                }
                            }
                            #endregion

insert from xml in sql stored procedure

CREATE PROCEDURE [dbo].[InsertSP]
@strXML varchar(1000)
AS
Declare @intPointer int
exec sp_xml_preparedocument @intPointer output, @strXML
Insert into Months
Select * from OpenXml(@intPointer,'/months/month',2)
WITH ( nMonthNo int, cMonth varchar(15))

exec sp_xml_removedocument @intPointer
RETURN
GO

and call the procedure as follows,

exec insertsp '<months><month><nMonthNo>1</nMonthNo><cMonth>January</cMonth></month><month><nMonthNo>2</nMonthNo><cMonth>February</cMonth></month></months>'

Roll back and transaction in sql server procedure

CREATE PROCEDURE DeleteDepartment
(
   @DepartmentID    int
)
AS

-- This sproc performs two DELETEs.  First it deletes all of the
-- department's associated employees.  Next, it deletes the department.

-- STEP 1: Start the transaction
BEGIN TRANSACTION

-- STEP 2 & 3: Issue the DELETE statements, checking @@ERROR after each statement
DELETE FROM Employees
WHERE DepartmentID = @DepartmentID

-- Rollback the transaction if there were any errors
IF @@ERROR <> 0
 BEGIN
    -- Rollback the transaction
    ROLLBACK

    -- Raise an error and return
    RAISERROR ('Error in deleting employees in DeleteDepartment.', 16, 1)
    RETURN
 END


DELETE FROM Departments
WHERE DepartmentID = @DepartmentID

-- Rollback the transaction if there were any errors
IF @@ERROR <> 0
 BEGIN
    -- Rollback the transaction
    ROLLBACK

    -- Raise an error and return
    RAISERROR ('Error in deleting department in DeleteDepartment.', 16, 1)
    RETURN
 END

-- STEP 4: If we reach this point, the commands completed successfully
--         Commit the transaction....
COMMIT

pass array of items in sql server stored procedure and fetch record from it.

First of all,u need to pass a string from ur .net app. like '1,2,3,4,5,6,7' or in any format.
Then use this function to split your comma seprated string and here is the stored procedure to fetch record.



CREATE procedure [dbo].[test](@parameter varchar(5000)) As
begin
declare @val varchar(max);
select * from emp where convert(varchar,Id) in(select * from dbo.fnSplit(@parameter,'.') )

end

Split function in SqlProcedure

CREATE FUNCTION [dbo].[fnSplit](
    @sInputList VARCHAR(8000) -- List of delimited items
  , @sDelimiter char(1)  -- delimiter that separates items
) RETURNS @List TABLE (item VARCHAR(8000))

BEGIN
DECLARE @sItem VARCHAR(8000)
WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0
 BEGIN
 SELECT
  @sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,@sInputList,0)-1))),
  @sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList))))

 IF LEN(@sItem) > 0
  INSERT INTO @List SELECT @sItem
 END

IF LEN(@sInputList) > 0
 INSERT INTO @List SELECT @sInputList -- Put the last item in
RETURN
END