European Windows 2012 Hosting BLOG

BLOG about Windows 2012 Hosting and SQL 2012 Hosting - Dedicated to European Windows Hosting Customer

SQL Server 2016 Hosting - HostForLIFE.eu :: Isolation Level In SQL Transaction

clock February 28, 2017 08:37 by author Peter

Isolation is one of the properties of SQL Transaction. Isolating / separating transactions from each other to maintain Data Integrity in Database is called Isolation.Before going to implementation part of isolation, we will understand why isolation is required in database ?

Why Is Isolation Level Required ?
While developing large enterprise/public networking kind of applications where a huge number of users access same Database, same Table and at the same Time, Data concurrency situation may occur. We will discuss this situation into 4 parts:

  • Loss of Data
  • Dirty Read
  • Phantom Read
  • Inconsistency Analysis

Loss of Data
Let's take an example - Suppose, there are 2 users accessing the same table, at the same moment, to update the same row. Each transaction is unaware of the other transaction. User A updates the row and then User B updates the same row. What happened here is the last transaction made my User B overwrites the updated record of User A and User A lost his/her data in the table.

Dirty Read
This is otherwise known as Uncommitted Dependency. Let's take another example - Suppose, User A and User B are accessing a table row at the same time. User A wants to read and User B wants to update the row. In the friction of time difference, transactions are executed. So, when User B not yet updated the row (during the update process), User A reads that row and got the old record which may not be correct for his/her operation. This situation is known as Dirty Read.

Phantom Read
This is also known as Phantom Problem. Let's again take another example - Suppose User A is granted to insert a row but the same time User B inserted that row. Now, when User A tries to insert, he/she can't. Then, he/she will get angry and say- "Hey you committed that this is available for me to insert, but you cheated on me and granted someone else to do so !" . You may/might get this problem while the reservation of Train/Movie ticket.

Inconsistency Analysis
This is also known as Non-Repeatable Problem. Let's take the same example of User A and User B. Suppose, User A executes a transaction having three queries - a stored procedure or transaction or individual query with a batch. 1st query is to read a table row, the 2nd query is to update that, and the 3rd query is to read that again. By doing this, User A wants to generate the report. As we know User B is not a gentleman he always spoils the intention of User A, he accessed the table row in between the two Read queries of User A and did some operation like Delete ! Now, User A has already modified the data and when he wants to read it again, he is surprised ! He got inconsistency in data.

In the above-mentioned points, we understood that - as User A is a weak person, User B always plays with User A and forcefully does it's job by dominating him. But as a Database Administrator or Developer, we need to help the User A. Now, we have a weapon called "Isolation Level" by using which we can cooperate User A to maintain its integrity.

Isolation Levels
SQL Server provides 5 Isolation levels to implement with SQL Transaction to maintain data concurrency in the database.

Isolation level is nothing but locking the row while performing some task, so that other transaction can not access or will wait for the current transaction to finish its job.

Let's write a transaction without Isolation level.
BEGIN TRANSACTION MyTransaction 
BEGIN TRY 
UPDATE Account SET Debit=100 WHERE Name='John Cena' 
UPDATE ContactInformation SET Mobile='1234567890' WHERE Name='The Rock' 
COMMIT TRANSACTION MyTransaction 
PRINT 'TRANSACTION SUCCESS' 
END TRY 
BEGIN CATCH 
ROLLBACK TRANSACTION MyTransaction 
PRINT 'TRANSACTION FAILED' 
END CATCH  


In the above code snippet, we have not implemented Isolation Level. Therefore, there are chances of data concurrency. Now, we will go through all the five Isolation levels and find the solution for our situation.

Read Uncommitted

When this level is set, the transaction can read uncommitted data resulting in the Dirty Read problem. With this isolation level, we allow a transaction to read the data which is being updated by other transaction and not yet committed. Suppose User A is trying to read a row which is being updated by User B. Here, we are allowing User A to read the un-updated/uncommitted data i.e old data.

Example
SET TRANSACTION ISOLATION LEVEL  
READ UNCOMMITTED 
BEGIN TRANSACTION MyTransaction 
BEGIN TRY 
UPDATE Account SET Debit=100 WHERE Name='John Cena' 
UPDATE ContactInformation SET Mobile='1234567890' WHERE Name='The Rock' 
COMMIT TRANSACTION MyTransaction 
PRINT 'TRANSACTION SUCCESS' 
END TRY 
BEGIN CATCH 
ROLLBACK TRANSACTION MyTransaction 
PRINT 'TRANSACTION FAILED' 
END CATCH 
Read Committed

This prevents Dirty Read. When this level is set, the transaction can not read the data that is being modified by the current transaction. This will force user to wait for the current transaction to finish up its job. Suppose User A is trying to read a row which is being updated by User B. Here, we are asking User A to wait for the User B to finish its update task, and giving the updated/correct data to User A. But the problem with this level is - it can't resolve Phantom Read or Inconsistency Analysis i.e it asks User A to wait for Read but not for update or insert.

Example
SET TRANSACTION ISOLATION LEVEL  
READ COMMITTED 
BEGIN TRANSACTION MyTransaction 
BEGIN TRY 
UPDATE Account SET Debit=100 WHERE Name='John Cena' 
UPDATE ContactInformation SET Mobile='1234567890' WHERE Name='The Rock' 
COMMIT TRANSACTION MyTransaction 
PRINT 'TRANSACTION SUCCESS' 
END TRY 
BEGIN CATCH 
ROLLBACK TRANSACTION MyTransaction 
PRINT 'TRANSACTION FAILED' 
END CATCH 
Repeatable Read

This level does every work that Read Committed does. but it has one additional benefit. User A will wait for the transaction being executed by User B to execute it's Update query as well, like Read Query. But Insert query doesn't wait, this also creates Phantom Read problem.

Example
SET TRANSACTION ISOLATION LEVEL  
REPEATABLE READ
Snapshot

This level takes a snapshot of current data. Every transaction works on its own copy of data. When User A tries to update or insert or read anything, we ask him to re-verify the table row once again from the starting time of its execution, so that he can work on fresh data. with this level. We are not giving full faith to User A that he is going to work on fresh data but giving high-level changes of data integrity.

Example
SET TRANSACTION ISOLATION LEVEL  
SNAPSHOT 
Serializable

This is the maximum level of Isolation level provided by SQL Server transaction. We can prevent Phantom Read problem by implementing this level of isolation. It asks User A to wait for the current transaction for any kind of operation he wants to perform.

Example
SET TRANSACTION ISOLATION LEVEL  
SERIALIZABLE

Isolation level also has a problem called "Dead Lock"- "Both the transactions lock the object and waits for each other to finish up the job". Dead Lock is very dangerous because it decreases the concurrency and availability of database and the database object. We will discuss Dead-Lock in the later part of this topic.

Hope, I covered the necessary points that can help many of us to implement Isolation level in the project with the identification of real situations. Believe me friends, it is a good practice to implement Transaction with "Isolation Level" in every type of projects.

HostForLIFE.eu SQL Server 2016 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

 



SQL Server 2016 Hosting - HostForLIFE.eu :: FORMATMESSAGE

clock February 21, 2017 08:25 by author Peter

In this post, I will show you how to using bigint with FORMATMESSAGE. SQL Server 2016 added the FORMATMESSAGE function.  According to Books On Line, FORMATMESSAGE constructs a message from an existing message in sys.messages or from a provided string. The functionality of FORMATMESSAGE resembles that of the RAISERROR statement. However, RAISERROR prints the message immediately, while FORMATMESSAGE returns the formatted message for further processing.

So let's take a look at this new function, run the following
SELECT FORMATMESSAGE('Signed int %i, %i', 50, -50)
SELECT FORMATMESSAGE('Unsigned int %u, %u', 50, -50);


Here is the output if you run that

--------------------------------------------
Signed int 50, -50
Unsigned int 50, 4294967246

Here is what the type specifications that you can use are

Type specification         Represents
d or i                          Signed integer
o                                Unsigned octal
s                                String
u                               Unsigned integer
x or X                         Unsigned hexadecimal

We used i to denote a signed integer, we also used u to denote a unsigned integer

Let's look at another example, this time we are using a variable. The variable will be an integer and we  are using i as the type specification

DECLARE @Val int = 1
SELECT FORMATMESSAGE('The value you supplied %i is incorrect!', @Val);


Here is the output
---------------------------------------
The value you supplied 1 is incorrect!

That worked without a problem. Now let's use a variable of the bigint data type, we are using the same type specification as before

DECLARE @Val bigint = 1
SELECT FORMATMESSAGE('The value you supplied %i is incorrect!', @Val);

Here is the output
---------------------------------------------------------------------------
Error: 50000, Severity: -1, State: 1. (Params:).


The error is printed in terse mode because there was error during formatting.
Tracing, ETW, notifications etc are skipped.

As you can see that did not work, so what can we do?
One thing we can do is converting the value to a varchar and then use s as the type specification
DECLARE @Val bigint = 1
SELECT FORMATMESSAGE('The value you supplied %s is incorrect!',
   CONVERT(VARCHAR(100),@Val));

You will again get this as output
---------------------------------------

So converting to varchar worked, but what if we want to use a bigint data type without converting to a varchar?

Another way is to use I64d as the type specification
DECLARE @Val bigint = 1<br />
SELECT FORMATMESSAGE('The value you supplied %I64d is incorrect!', @Val);


You will get this
---------------------------------------

The value you supplied 1 is incorrect!
So there you have it, if you want to use bigint with FORMATMESSGAE use I64d as the type specification, or convert to varchar and use s as the type specification.

HostForLIFE.eu SQL Server 2016 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

 



SQL Server 2016 Hosting - HostForLIFE.eu :: How to Auto Increment ID Column Using nvarchar DataType In SQL Server?

clock February 14, 2017 07:53 by author Peter

This article is about Auto Increment ID Column using nvarchar datatype in SQL Server 2016 . SQL server provides identity property to get auto-increment record values where the datatype used is “int”. This article explains a way to use nvarchar datatype column to get auto-increment record values.
e.g. S00001, E00001, INV00001. (Here there are prefix like S, E, INV used to denote the record ids.)

This article provides a solution to use alphanumeric record ids incremented automatically.

Solution
Create an Employee table as below,

Here “ID” column is for storing record id as similar to Serial No of any entry made. “EmployeeNo” column is used to store the alphanumeric auto increment id. “Name” stores the name of the Employee. Create a Windows Form with a TextBox and a Button control


The above figure shows the use of a textbox to enter name in the ”employee” table where the id is auto-incremented.
Code
    Import the NameSpace

Create 2 methods OpenConnection() and CloseConnection() to handle the SQL Server Connection object.
        private void OpenConnection(SqlConnection con) { 
            con.ConnectionString = "Data Source=.; Initial Catalog=demodb; Integrated Security=True"; 
            if (con.State == ConnectionState.Closed) { 
                con.Open(); 
            } 
        } 
        private void CloseConnection(SqlConnection con) { 
            con.Close(); 
        } 


On the Insert button click the id is auto-incremented and the relevant data is inserted in the employee table. (id, employeeno and name)
        SqlConnection con = new SqlConnection(); 
        OpenConnection(con); 
        string id = AutoIncrementID(); 
        int idLimit = 7; 
        string eno = "E" + ZeroAppend("0000000" + id, idLimit); 
        string query = "insert into employee values ('" + id + "', '" + eno + "', '" + txtName.Text + "')"; 
        SqlCommand cmd = new SqlCommand(query, con); 
        int i = cmd.ExecuteNonQuery(); 
        if (i > 0) { 
            MessageBox.Show("Data Added", "Add", MessageBoxButtons.OK, MessageBoxIcon.Information); 
        } 
        CloseConnection(con); 

Here a method AutoIncrementID() is used to do the task of auto-incrementing based on the last entry inserted in the table.
        private string AutoIncrementID() { 
            SqlConnection con = new SqlConnection(); 
            OpenConnection(con); 
            SqlCommand cmd = new SqlCommand("SELECT ISNULL(MAX(ID),0) + 1 from employee", con); 
            SqlDataReader dr = cmd.ExecuteReader(); 
            string id = null; 
            if (dr.Read()) { 
                id = dr[0].ToString(); 
            } 
            CloseConnection(con); 
            return id; 
        } 
        //The function ZeroAppend is used to append “0” after the prefix to the code. 
        public static string ZeroAppend(string data, int idLimit) { 
            return data.Substring(data.Length - idLimit); 
        } 

Output,

HostForLIFE.eu SQL Server 2016 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

 



SQL Server 2016 Hosting - HostForLIFE.eu :: How to Implement Inner Join When Query Contains Outer Join?

clock February 7, 2017 08:50 by author Peter

This is something interesting; i.e., how to enforce the compiler to use the inner join instead of the outer join. Let's take an example. We have two tables State_Tab and City_Tab. An SQL JOIN clause is used to combine rows from two or more tables, based on a common field between them. The most common type of join is: SQL INNER JOIN (simple join). An SQL INNER JOIN returns all rows from multiple tables where the join condition is met.

State_Tab

City_Tab

Now, we write two queries, using outer join, and compare the execution plan of both the queries.

Query 1

Query 2


We can see that both the queries use the left outer join but the second query implements the inner join, instead of outer join, because for "On condition", we define the 1=1, which will be always true, so the query optimizer converts the left outer join to cross join due to the "Where condition". This cross join will convert into the inner join.



SQL Server 2016 Hosting - HostForLIFE.eu :: How to Get The Header Information Of A Table in SQL Server?

clock January 10, 2017 10:14 by author Peter

Now, I will show you how to Get The Header Information Of A Table in SQL Server. If someone asks the question, "How do I get the header information of the table in SQL Server?" the first solution that arises in our mind will be "use any condition that never matches," like 1=0 or 2=0. However, in this case, we only get the header information of the table, and no result will return.

It is true that using the above solution, we will not get any data and the result will be the header information of our table.

But this is not the best solution because in this case, SQL Server reads all rows and compares the conditions each time. That increases the turnaround time and resource complexity.

For this, SQL Server provides the "SET FMTONLY ON" keyword. "SET FMTONLY ON" returns only metadata to the client. It can be used to test the format of the response without actually running the query. This method returns header information without performing any read operation.

HostForLIFE.eu SQL Server 2016 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.



SQL Server 2016 Hosting - HostForLIFE.eu :: Clearing Down A Database Full Of Constraints In SQL Server

clock January 3, 2017 08:32 by author Peter

Have you ever been in a situation where you've got to clear down some data within a database that's chock filled with constraints but don't desire to wipe out your precious relationships, indices and all that other jazz?

I found myself in a similar situation earlier this week, and required a clear-down script that might wipe out all of the data within a whole database, without being bothered by any existing constraints. Here it is.
    USE @YourTable;  
    EXEC sp_MSForEachTable "ALTER TABLE ? NOCHECK CONSTRAINT ALL"  
    EXEC sp_MSForEachTable "DELETE FROM ?"  
    EXEC sp_MSForEachTable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL"  
    GO  

The script itself takes advantage of an undocumented stored procedure within SQL Server called sp_MSForEachTable that will actually iterate through all of the tables within a given database.

Now that we know we are going to be looping through each of the tables within the specified database, let's see what is going to happen to each of the tables.


ALTER TABLE ? NOCHECK CONSTRAINT ALL
This will disable any constraint checking that is present on the table (so, operations like deleting a primary key or a related object won't trigger any errors).

DELETE FROM ?
This will delete every record within the table.

ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL
This re-enables the constraint checking, bringing your table back to its original state, sans data.

It is very important that you properly scope this query to the table that you are targeting to avoid any crazy data loss. While I don't think that you could just leave that out and execute on master, I wouldn't want to even risk testing that out (although feel free to try it out and let me know if it nukes everything).

HostForLIFE.eu SQL Server 2016 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.



SQL Server 2016 Hosting - HostForLIFE.eu :: Collation Error In SQL Sever

clock December 20, 2016 07:56 by author Peter

ERROR - "Cannot resolve the collation conflict between SQL_Latin1_General_CP1_CI_AS and Latin1_General_CI_AS_KS_WS within the up to operation."

Don’t panic if you get this error while joining your tables. there's an easy way to solve this. It happens because of the different collation settings on 2 columns we are joining.

The first step is to figure out what are the two collations that have caused the conflicts.
Let us assume that collation has not been set at the column level and instead at the db level. Then, we've to execute two straightforward statements as below.
Statements

  • Select DATABASEPROPERTYYEX('DB1',N'Collation')
  • Select DATABASEPROPERTYYEX('DB2',N'Collation') 

One more thing to make a note of here is that if you are on SharePoint, you will get an error as following.

Latin_General_CI_AS_KS_WS.
 
If you are on any other database and use the default settings, you may get this SQL_Latin_General_CP1_CI_AS.

Now, we have to do something similar to CAST, called Collate (FOR Collation).

Refer to the example below.
      select * from Demo1.dbo.Employee emp 
    join Demo2.dbo.Details dt 
    on (emp.email =dt.email COLLATE SQL_Latin_General_CP1_CI_AS) 

HostForLIFE.eu SQL Server 2016 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.



SQL Server 2016 Hosting - HostForLIFE.eu :: LAG and LEAD Functions in SQL Server

clock November 24, 2016 07:33 by author Peter

In this post, I will tell you about the LAG and LEAD functions in SQL Server. These two functions are analytical functions in SQL Server. In actual scenarios we need to analyze the data, for example, comparing previous sales data.

The Lag and Lead functions support the window partitioning and ordering clauses in SQL Server. The Lag and Lead functions do not support the window frame clause.

LAG
The Lag function gives the previous column values based on ordering.

LEAD
The Lead function gives the next column values based on ordering.

Demo
    CREATE TABLE DBO.SALES 
    ( 
    PROD_ID INT , 
    SALES_YEAR INT, 
    SALES_AMOUNT INT  
    ) 

    INSERT INTO DBO.SALES(PROD_ID,SALES_YEAR,SALES_AMOUNT) VALUES(1,2009,10000) 
    INSERT INTO DBO.SALES(PROD_ID,SALES_YEAR,SALES_AMOUNT) VALUES(1,2010,9000) 
    INSERT INTO DBO.SALES(PROD_ID,SALES_YEAR,SALES_AMOUNT) VALUES(1,2011,8000) 
    INSERT INTO DBO.SALES(PROD_ID,SALES_YEAR,SALES_AMOUNT) VALUES(1,2012,7000) 
    INSERT INTO DBO.SALES(PROD_ID,SALES_YEAR,SALES_AMOUNT) VALUES(1,2013,14000) 
    INSERT INTO DBO.SALES(PROD_ID,SALES_YEAR,SALES_AMOUNT) VALUES(1,2014,18000) 
    INSERT INTO DBO.SALES(PROD_ID,SALES_YEAR,SALES_AMOUNT) VALUES(1,2015,15000) 
    INSERT INTO DBO.SALES(PROD_ID,SALES_YEAR,SALES_AMOUNT) VALUES(2,2013,12000) 
    INSERT INTO DBO.SALES(PROD_ID,SALES_YEAR,SALES_AMOUNT) VALUES(2,2014,8000) 
    INSERT INTO DBO.SALES(PROD_ID,SALES_YEAR,SALES_AMOUNT) VALUES(2,2015,16000) 
    INSERT INTO DBO.SALES(PROD_ID,SALES_YEAR,SALES_AMOUNT) VALUES(3,2012,7000) 
    INSERT INTO DBO.SALES(PROD_ID,SALES_YEAR,SALES_AMOUNT) VALUES(3,2013,8000) 
    INSERT INTO DBO.SALES(PROD_ID,SALES_YEAR,SALES_AMOUNT) VALUES(3,2014,9700) 
    INSERT INTO DBO.SALES(PROD_ID,SALES_YEAR,SALES_AMOUNT) VALUES(3,2015,12500) 

    SELECT * FROM DBO.SALES

The following example shows the Previous Year Sales Amount.
SELECT *, LAG(SALES_AMOUNT) OVER(ORDER BY PROD_ID ,SALES_YEAR) [Prevoius Year Sales] FROM DBO.SALES

The following example shows the Next Year Sales Amount.
    SELECT * , 
    LEAD(SALES_AMOUNT) OVER(ORDER BY PROD_ID ,SALES_YEAR) [Next Year Sales]  
    FROM DBO.SALES 

The following example shows the Previous Year Next Year Sales Amount using the partition by clause.
    SELECT *, 
     
       LAG(SALES_AMOUNT) OVER(PARTITION BY PROD_ID ORDER BY PROD_ID ,SALES_YEAR) [PREVOIUS YEAR SALES] , 
       LEAD(SALES_AMOUNT) OVER(PARTITION BY PROD_ID ORDER BY PROD_ID ,SALES_YEAR) [NEXT YEAR SALES]  
    FROM DBO.SALES     

The following example shows an offset other than 1.

The offset is by default 1. If we want an offset other than 1 then we need to provide 2 argument values in the Lag and Lead functions.
    SELECT * , 
       LAG(SALES_AMOUNT,2)  OVER(ORDER BY PROD_ID ,SALES_YEAR) [PREVOIUS YEAR SALES] , 
       LEAD(SALES_AMOUNT,2)  OVER( ORDER BY PROD_ID ,SALES_YEAR) [NEXT YEAR SALES]  
    FROM DBO.SALES 

The following example shows replacing the null with various values:

    SELECT * , 
       LAG(SALES_AMOUNT,2,0)  OVER(ORDER BY PROD_ID ,SALES_YEAR) [PREVOIUS YEAR SALES] , 
       LEAD(SALES_AMOUNT,2,0)  OVER( ORDER BY PROD_ID ,SALES_YEAR) [NEXT YEAR SALES]  
    FROM DBO.SALES 

 

HostForLIFE.eu SQL Server 2016 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.



SQL Server 2016 Hosting - HostForLIFE.eu :: Stored Procedure With Real Time Scenario In SQL Server

clock November 10, 2016 08:00 by author Peter

In this article, I am going to tell you how to create a procedure in the real time scenario. A stored procedure is a set of SQL statements, which has been created and stored in the database as an object. Stored procedure will accept the input and output parameters, so that a single procedure can be used over the network by several users, using different input. Stored procedure will reduce the network traffic and increase the performance.

Real time scenario
Step 1: Create a table to describe and create the stored procedure.
    create table Product 
    ( 
          ProductId int primary key, 
          ProductName varchar(20) unique, 
          ProductQty int, 
          ProductPrice float 
    ) 


Step 2: Insert some value to the describe scenario.
    insert product values(1,'Printer',10,4500) 
    insert product values(2,'Scanner',15,3500) 
    insert product values(3,'Mouse',45,500)  


Step 3: Check your table with the inserted value.
    select * from product  

Step 4: Real time scenario is given below:
Create a stored procedure, which is used to perform the requirements, given below:

Before inserting, check the detail about the product name. If the product name is available, update an existing product qty + inserted product qty,

  • Before inserting, check the detail about the product name.
  • If the product name is available, check the product price.
  • If the existing product price is less, the inserted product product price replaces the existing product price with the inserted product price.
  • If first and second conditions are not satisfied, insert the product information, as new record into the table.

    create procedure prcInsert  
    @id int, 
    @name varchar(40), 
    @qty int, 
    @price float 
    as 
    begin 
     declare @cnt int 
     declare @p float 
     select @cnt=COUNT(ProductId)from Product where [email protected] 
     if(@cnt>0) 
     begin 
      update Product set [email protected] where [email protected] 
      select @p=ProductPrice from Product where [email protected] 
      if(@p<@price) 
      begin 
       update Product set [email protected] where [email protected] 
      end 
     end 
     else 
     begin 
      insert Product values(@id,@name,@qty,@price) 
     end 
    end  

HostForLIFE.eu SQL Server 2016 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.



SQL Server 2016 Hosting - HostForLIFE.eu :: How to Convert String To Color in SQL Server 2016?

clock September 28, 2016 21:00 by author Peter

In this post, I will show you how to Convert String To Color in SQL Server. The following code will describe how to convert String To Color In SQL Server 2016. And now write code below:

CREATE FUNCTION dbo.fn_conversion_string_color(  
@in_string VARCHAR(200)  
RETURNS NVARCHAR(500)  
AS  
BEGIN  
DECLARE @fsetprefix BIT, -- append '0x' to the output   
@pbinin VARBINARY(MAX), -- input binary stream   
@startoffset INT, -- starting offset    
@cbytesin INT, -- length of input to consider, 0 means total length   
@pstrout NVARCHAR(MAX),   
@i INT,   
@firstnibble INT ,  
@secondnibble INT,   
@tempint INT,  
@hexstring CHAR(16)  
  
SELECT @fsetprefix = 1,  
@pbinin = SUBSTRING(HASHBYTES('SHA1', @in_string), 1, 3),  
@startoffset = 1,  
@cbytesin = 0   
  
-- initialize and validate   

IF (@pbinin IS NOT NULL)   
BEGIN    
SELECT @i = 0,   
@cbytesin = CASE  WHEN (@cbytesin > 0 AND @cbytesin <= DATALENGTH(@pbinin))  
  THEN @cbytesin  
  ELSE DATALENGTH(@pbinin)  
  END,   
@pstrout =  CASE  WHEN (@fsetprefix = 1)  
  THEN N'0x'  
  ELSE N''  
  END,   
@hexstring = '0123456789abcdef'   
   
--the output limit for nvarchar(max) is 2147483648 (2^31) bytes, that is 1073741824 (2^30) unicode characters   
  
IF (  
((@cbytesin * 2) + 2 > 1073741824)  
OR ((@cbytesin * 2) + 2 < 1)  
OR (@cbytesin IS NULL )  
)   
RETURN NULL   
   
IF (  
( @startoffset > DATALENGTH(@pbinin) )  
OR (@startoffset < 1 )  
OR (@startoffset IS NULL )  
)   
RETURN NULL   
   
-- adjust the length to process based on start offset and total length   
  
IF ((DATALENGTH(@pbinin) - @startoffset + 1) < @cbytesin)   
SELECT @cbytesin = DATALENGTH(@pbinin) - @startoffset + 1   
  
-- do for each byte   
WHILE (@i < @cbytesin)   
BEGIN   
-- Each byte has two nibbles  which we convert to character   

SELECT @tempint = CAST(SUBSTRING(@pbinin, @i + @startoffset, 1) AS INT)   
SELECT @firstnibble = @tempint / 16   
SELECT @secondnibble = @tempint % 16   
    
-- we need to do an explicit cast with substring for proper string conversion.     

SELECT @pstrout = @pstrout +   
  CAST(SUBSTRING(@hexstring, (@firstnibble+1), 1) AS NVARCHAR) +   
  CAST(SUBSTRING(@hexstring, (@secondnibble+1), 1) AS NVARCHAR)   
SELECT @i = @i + 1   
END   
END   
RETURN  '#' + UPPER(RIGHT(@pstrout, 6))  
 END  

HostForLIFE.eu SQL 2016 Hosting
European best, cheap and reliable ASP.NET hosting with instant activation. HostForLIFE.eu is #1 Recommended Windows and ASP.NET hosting in European Continent. With 99.99% Uptime Guaranteed of Relibility, Stability and Performace. HostForLIFE.eu security team is constantly monitoring the entire network for unusual behaviour. We deliver hosting solution including Shared hosting, Cloud hosting, Reseller hosting, Dedicated Servers, and IT as Service for companies of all size.




About HostForLIFE.eu

HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

We have offered the latest Windows 2012 Hosting, ASP.NET 4.5 Hosting, ASP.NET MVC 5 Hosting, SQL 2014 Hosting and SQL 2014 Hosting.

Tag cloud

Sign in