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 :: Stuff Function In SQL Server

clock August 16, 2018 09:23 by author Peter

Stuff is a function in SQL Server used to perform special operations on a string value.
The below operations can be performed,

  • Remove string part from string expression.
  • Insert/Append string at specified index.

Syntax
select STUFF(string_value, start_index, no_of_chars_to_replace, replace_string); 

Remove String Part
select STUFF('hai_hello',0,2,''); 

Important Note
Start Index begins from 1 in STUFF Function.

Proper Index
select STUFF('hai_hello',1,2,''); 

Insert String Content
You can insert a string content by specifying the index location and set number of characters to replace to zero. Note that the third parameter value should be zero.
DECLARE @testString varchar(3) = 'abc'; 
select STUFF('hai_hello', 1, 0, @testString); 

Replace String Content
You cannot replace string by specifying the old characters here.
But you can replace the string by specifying start location and number of characters to replace.

Note
The third parameter value should be the length to replace.

Example 1
DECLARE @testString varchar(3) = 'abc'; 
select STUFF('hai_hello', 1, DATALENGTH (@testString), @testString); 

Example 2
select STUFF('hai_hello',1,2,'abc');

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 Replace Newline Character From The SQL Server Field?

clock August 14, 2018 11:41 by author Peter

In this post, we will learn about how to replace newline characters from SQL fields. In this post, we have to use replace function for replace string and also use char function to remove newline characters and replace them. Here I will give you the syntax of replace function, how to use char function, and the meaning of 10 in char function.
Syntax
Description of parameter value,

  • string - Source string
  • string_to_replace - String to search for in string1
  • replacement_string - Replacement string will be replaced string_to_replace with replacement_string in string1

What Char(10) in SQL. Execute the below selected query for checking what char(10) contains. Char(10) displays blank result in SQL query result which means it's \r or \n
SELECT CHAR(10) 

Below query replaces char(10) to html <br /> tag,
REPLACE(EventNote, CHAR(10),'<br />')

See the 4th result in the screenshot for replacing string display with HTML <br /> tag.

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.



European SQL 2017 Hosting - HostForLIFE.eu :: To Overcome "The Given Key Was Not Present In The Dictionary" Exception In MySQL

clock August 9, 2018 08:37 by author Peter

In this article, I will give one of the solutions to overcome the "The given key was not present in the dictionary" exception in MySQL. Developers may face this error in many situations, but I faced this error in query execution after migrating the MSSQL Database to MySQL.

Recently, I have migrated a database from MSSQL to MySQL. After migration, I tried to run the simple select query with joins which already worked in my application with MSSQL connection string. When I ran the same query with MySQL Connection string, I got the “The given key was not present in the dictionary” Exception. But if I run the same query in MySQL Workbench, it's working fine.

So, here is my sample query which I tried to execute through MySqlDataAdapter.
Select * from usersdetails; 
nMySqlDataAdapter = new MySqlDataAdapter(xszQuery, zSqlConn); 


When this query passes MySqlDataAdapter, I'm getting the exception “The given key was not present in the dictionary”. But the same query is working fine in MySQL Workbench.

It's the same process if I pass the query like,
Select usersID,usersname from usersdetails; 
nMySqlDataAdapter = new MySqlDataAdapter(xszQuery, zSqlConn); //Working fine without any exception. 

I have searched for many solutions on Google, but none worked for me. Some solutions said that error in connection string needs to include the charset=utf8 parameter with the connection string. But that didn't work for me.

Then I found something:
Select usersID,usersname from usersdetails; // this query working fine, no error 
Select usersID,usersname,usersAddress from usersdetails; // this query returns exception 
Select usersID,userGUID from usersdetails; // this query working fine, no error 
Select usersID,userGUID,userDepartment from usersdetails;  // this query returns exception 


So, the problem is something with the columns which I trying to select, then I found what makes each column differ from the other.

What I found is “Collation” value of each column is different based on column datatype, I don’t know in what basis these values are assigned during migration.

What is Collation in MySQL?
A collation is a set of rules that defines how to compare and sort character strings. Each collation in MySQL belongs to a single character set. Every character set has at least one collation, and most have two or more collations.

A collation orders characters based on weights. Each character in a character set maps to a weight. Characters with equal weights compare as equal, and characters with unequal weights compare according to the relative magnitude of their weights.

To know more about “Collation” click here.

I followed two different methods to overcome this exception.

Method 1
By selecting “Table default” value in “Collation” option for the all columns in a table we can overcome this exception.


Method 2
During migration from MSSQL to MySQL, on manual editing step, we can see the tables, column names, and respective datatypes. In the following image on step 3, by default MySQL had taken “CHARACTER SET ‘utf8mb4’ ” as charset value for some columns. We can edit this section, just select and delete “CHARACTER SET ‘utf8mb4’ ” and apply changes. Now all columns with “Collation” value become “Table default” after migration.

By using these two ways we can overcome this exception. In this article, I have given one of the ways to overcome “The given key was not present in the dictionary” exception. If anybody knows any other way to overcome this exception, please mention in the comment  box. I hope this article is very useful.

European SQL 2017 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.



European SQL 2016 Hosting - HostForLIFE.eu :: Indexes In SQL Server

clock August 7, 2018 09:19 by author Peter

One of the most important routes to high performance in SQL server database is an index. It is a database object which is used to speed up the querying process by providing quick access to rows in the database tables. By using Indexes we can save time and can improve the performance of database queries and applications. An Index contains keys built from one or more columns in the table mapped to the storage location of the specified data. When we create an index on any column, SQL server internally maintains a separate table called index table, so that whenever a user tries to retrieve the data from the existing table,  depending on the index Table SQL server goes directly to the table and retrieves the required data very quickly.

In the Table we can use a maximum of 250 Indexes. The Index Type refers to the way the index is stored internally by SQL server. So a Table can contain two types of indexes:

  • Clustered Index
  • Non-clustered Index

Clustered Indexes
The only time the data rows in a table are stored in sorted (ascending order only) order structure is when the table contains a clustered index. When a table has a clustered index, then it is called a clustered table. If a table has no clustered index, its data rows are stored in an unordered structure. A table can have only 1 clustered Index on it, which will be created when a primary key constraint is used in a Table.

Non-Clustered Indexes
Non-clustered Indexes will not have any arrangement order (unordered structure) of the data in the table. In a table, we can create 249 non clustered Indexes.If we don't mention clustered indexes in a table then a default is stored as non-clustered Indexes.

European 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.



European SQL 2016 Hosting - HostForLIFE.eu :: Using UNION With Different Tables, Fields And Filtering

clock July 31, 2018 09:22 by author Peter

The command "UNION" is the perfect way to join two tables with the same data context. Whether they have or do not have the same fields, you need to classify the data.
Look at this selected query.
Select t.* From ( 
(Select 1 as typePerson, tenName as namePerson, tenSalary as payMoney From Teachers Where tenAge>30) 
UNION 
(Select 0 as typePerson, stdName as namePerson, 0 as payMoney From Students Where stdYearFinish=2016) 
) t 
Where t.namePerson like ‘Maria%’ 
Order By t.tenName , t.typePerson;*


It creates a temporary alias “t”;
It classifies each data row “typePerson”, 1 (true) for teachers and 0 (false) to Students;
It filters the age of the teachers;
It filters the end year on the school to the year 2016;
After the UNION, it filters by the field name Person that begins with Maria.

Observations
All fields must be at the same position and the same data type, you can make all kinds of selects, joins, where etc. The "UNION ALL" command is better than UNION if you want to select all rows. If they are the same**, this is not the case. In this sample, you can make it a View.

CONCLUSION

You must be careful of the position of the fields and the type, and you can use cast too.

This selection is just an example.
The SQL UNION ALL operator is used to combine the result sets of 2 or more SELECT statements. It does not remove duplicate rows between the various SELECT statements (all rows are returned).

European 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.



SQL Server 2016 Hosting - HostForLIFE.eu :: Auto Query Generator In MSSQL Server

clock July 26, 2018 08:01 by author Peter

If you’re a developer, irrespective of the platform, you  have to work with databases. Creating SQL statements for tables is quite often a monotonous job and it gets hectic especially when dealing with gigantic tables that have hundreds of columns. Writing SQL statements manually every time becomes a tiresome process. Before explaining the script, I want to share the reason to write this script and how it is helping my peers. We have code standard on the database side. Below points are standards.

  • Need to maintain a separate stored procedure to every table
  • Don’t use * in the query instead specify the column
  • Use the correct data type and size of a column
  • Every parameter should be nullable in a stored procedure.

I am developing an application which is related to machines using .NET and SQL Server. The database design consists of some master tables and transactional tables. All the transactional table has more than 30 columns.

To meet my code standards, I need to mention all columns with correct data type and size in stored procedure parameters like below,
CREATEproc [dbo].[USP_PCNitemCreation] ( @Id int, @machineName varchar(50)=NULL, @furnacename varchar(50)=NULL, @minValue int=NULL, @maxValue int=NULL, @createdDate datetime=nullvarchar(100)=NULL ) 

All the queries should specify the column instead of using the start(*).
select machineName,furnacename from trn_furnace where [email protected] 

It consumes more time and is a boring task. So, I plan to write the script to is cut down on the time it takes and boring repeated work. We cannot automate the logic, but we can automate the repeated task.

Then I write the below script which really cuts down on all of our above pain points.

Auto Query Generator Stored Procedure for MSSQL Server,
CREATEproc [dbo].[USP_QuerycreationSupport] ( @table_Name varchar(100)=NULL ) AS  
BEGINDECLARE @InserCols   NVARCHAR(max)DECLARE @Inserparam  NVARCHAR(max)DECLARE @Insertquery NVARCHAR(max)DECLARE @Selectquery NVARCHAR(max)DECLARE @Update      NVARCHAR(max)DECLARE @DeleteQuery NVARCHAR(max) 
  -- sp paramSELECT '@'+c.NAME+Space(1)+Casecast(t.Nameasnvarchar(40))WHEN'nvarchar'THEN  
  t.NAME    +'('+cast(c.max_length asnvarchar(30))+')'  
WHEN'varchar'THEN  
  t.NAME+'('+cast(c.max_length asnvarchar(30))+')'  
WHEN'char'THEN  
  t.NAME+'('+cast(c.max_length asnvarchar(30))+')'  
WHEN'decimal'THEN  
  t.NAME        +'(18,2)'  
  ELSE t.nameend+'=null,'AS colss FROM sys.columns c innerjoin sys.types t ON c.user_type_id = t.user_type_id leftouterjoin sys.index_columns ic ON ic.object_id= c.object_idand ic.column_id = c.column_id leftouterjoin sys.indexes i ON ic.object_id= i.object_idand ic.index_id = i.index_id WHERE c.object_id=object_id(@table_Name)SELECT'Insert query'SET @InserCols=(selectdistinct  
  (  
         select sc.NAME+','  
         FROM   sys.tables st innerjoinsys.columns sc  
         ON st.object_id= sc.object_id  
         WHERE  st.NAME= @table_Name forxmlpath(''),  
                type).value('.','NVARCHAR(MAX)'))  
  -- Return the result of the functionSELECT @InserCols=LEFT(@InserCols,Len(@InserCols)-1)  
  --select @InserColsSET @Inserparam=(selectdistinct  
  (  
         select'@'+sc.NAME+','  
         FROM   sys.tables st innerjoinsys.columns sc  
         ON st.object_id= sc.object_id  
         WHERE  st.NAME= @table_Name forxmlpath(''),  
                type).value('.','NVARCHAR(MAX)'))  
  -- Return the result of the functionSELECT @Inserparam=LEFT(@Inserparam,Len(@Inserparam)-1)  
  --select @InserparamSET @Insertquery='insert into '[email protected]_Name+'('[email protected]+')'+'values'+'('[email protected]+')'SELECT @InsertquerySELECT'Update Query'SET @Update=(selectdistinct  
  (  
         select sc.NAME+'[email protected]'+sc.NAME+','  
         FROM   sys.tables st innerjoinsys.columns sc  
         ON st.object_id= sc.object_id  
         WHERE  st.NAME= @table_Name forxmlpath(''),  
                type).value('.','NVARCHAR(MAX)'))  
  -- Return the result of the functionSELECT @Update=LEFT(@Update,Len(@Update)-1)  
  --select @UpdateSET @Update='UPdate '[email protected]_Name+' set '[email protected] @Update  
  -- For select QuerySELECT'Select Query'SET @Selectquery='select '[email protected] +' from '+ @table_NameSELECT @Selectquery 
  -- For Delete QuerySELECT'Delete Query'SET @DeleteQuery='delete from '+ @table_NameSELECT @DeleteQuery 
end 


How to use this script,
Step 1 - Create the stored procedure using the above code or attached code.
Step 2 - Execute the stored procedure and pass your table name as a parameter.
Exec [email protected]_Name='mstCustomer' 

Should not pass the database object in the table name
Exec [email protected]_Name='[dbo].[mstCustomer]' 

Once you execute the Stored Procedure as mentioned above, you get all the SQL statements as shown here. You could easily use the generated SQL statements elsewhere. You get all basic SQL statements like Select, Insert, Update & Delete.


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.



European SQL 2016 Hosting - HostForLIFE.eu :: FOR JSON Clause With AUTO Mode In SQL Server 2016

clock July 24, 2018 07:26 by author Peter

In the release of SQL Server 2016 CTP 2 one of the features that was introduced is JSON clause. So, the first question that comes into everyone’s mind is What is JSON? JSON stands for JavaScript Object Notation. JSON is a lightweight format which is used for storing and interchanging the data. JSON uses standard JavaScript functions to convert the JSON data into native JavaScript objects. The main purpose of using FOR JSON is used to create new JSON objects. We can format the query results using FOR JSON clause in these ways,
With AUTO mode
With PATH mode
With ROOT option
Output with INCLUDE_NULL_VALUES option


In this blog, we will discuss the query formatting using FOR JSON clause with AUTO mode option.

Syntax for FOR JSON clause with AUTO option is like this:
FOR JSON AUTO

When AUTO option is used, the format of JSON is determined automatically on the basis of the number of columns present in the SELECT statement list. A FROM clause is necessary inquery with FOR JSON AUTO option.

When you join tables, columns present in the first table are used as properties of the root object in JSON array while columns present in the second table will be automatically formatted as a nested object within the root object.

Let’s execute the below query and see the JSON output.
SELECT sp.BusinessEntityID, 
   sp.TerritoryID, 
   st.CountryRegionCode, 
   st.[Group] TerrritoryGroup 
   FROM sales.salesperson sp 
   JOIN Sales.SalesTerritory st ON sp.TerritoryID = st.TerritoryID 
   WHERE sp.TerritoryID = 10 
FOR JSON AUTO 

After executing the above query, we get the output in this format.
[{ 
    "BusinessEntityID": 289, 
    "TerritoryID": 10, 
    "st": [{ 
        "CountryRegionCode": "GB", 
        "TerrritoryGroup": "Europe" 
    }] 
}]

Brackets [ ] represents JSON array in output.

Here, in the output, we can see that table Sales.SalesTerritory is automatically formatted as a nested object under parent object.

So we have generated a formatted query output using JSON clause. I will continue with other ways of formatted output using JSON clause in my next blogs.

European 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.



European SQL 2016 Hosting - HostForLIFE.eu :: Drop Indexes In Views In SQL Server

clock July 19, 2018 08:56 by author Peter

In this article, I am going to tell about the dropping of indexes in View in SQL Server. Views are virtual tables in which data from one or more tables gives the result set as our SQL table does with rows and columns. A View doesn’t store the data permanently in the database and at the time of execution, only its result set gets determined. When a View contains a large amount of rows and has complex logic in it then we can create an index on a View to improve the query performance. A View consists of a Unique Clustered Index and it is stored in the database as the clustered index does. Now, let’s run a few scenarios to check when Clustered index which is created on a View gets dropped automatically.

First, I will create a table on which I will run those scenarios.
CREATE TABLE[dbo]. [Customer] 
    ( 
        [CustomerID][int] IDENTITY(1, 1) NOT NULL, [CustomerName][varchar](50) NOT NULL, [CEO][varchar](40) NULL, [Phone][varchar](20) NOT NULL PRIMARY KEY CLUSTERED( 
            [CustomerID] ASC)) 
GO 


Now, I will create a View which will use this Customer table.
-- Create view 
Create VIEW vw_customer 
 WITH SCHEMABINDING  
AS  
 SELECT  CustomerID, CustomerName, CEO 
  from dbo.Customer 
  GO 


Here in the definition of View, I have used WITH SCHEMABINDING which is necessary for creating an index on a View. This option simulates that we cannot delete any of the base table used in the View and in order to make any changes, first, we need to drop or alter the View.

Also, all the table references in a View should have two part naming convention (schemaname.tablename) as we have in vw_Customer view (dbo.Customer).

Now, I will create an index on our View.


Scenario 1

Create index IX_CustomerID  
 ON vw_customer (CustomerID); 
 GO 


Error returned: Cannot create index on view 'vw_customer'. It does not have a unique clustered index. On Views, the first index must be a unique clustered index, so this will throw the error.


So, the first index on View should be UNIQUE CLUSTERD INDEX, else it will throw an error.

Scenario 2
Create unique clustered index IX_CustomerID  
 ON vw_customer (CustomerID) 
 GO 

Now, our Indexed View is created having a clustered index on it. Now, I want to alter my View and add one more column GETDATE() as CurrentDate  in the View definition and alter the View.

Now, alter the View after Scenario 2.

Scenario 3
ALTER  VIEW vw_customer 
 WITH SCHEMABINDING  
AS  
 SELECT  CustomerID, CustomerName , GETDATE() as CurrentDate 
  from dbo.Customer 
GO 

Now, as I have altered the View, I want to create another index on column CustomerName which will be a NonClustered index.

Scenario 4
Create index IX_CustomerName  
 ON vw_customer (CustomerName); 
GO 


Again, I get an error: Cannot create index on view 'vw_customer'. It does not have a unique clustered index.

As we have already created Unique Clustered Index on View, still it gives an error. The interesting thing is that after updating the View, the index that was created in Scenario 2 is dropped and the code will throw the same error as in Scenario 1.

As mentioned here,
"ALTER VIEW can be applied to indexed views; however, ALTER VIEW unconditionally drops all indexes on the view."
Hope you will like this post.

European 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.

 



European SQL 2016 Hosting - HostForLIFE.eu :: Using Window Ranking Function With UPDATE Statement

clock July 17, 2018 07:59 by author Peter

I was working on a scenario in which I needed to assign a unique value to a column. I was trying to update the column using window ranking function ROW_NUMBER() but I got an error that ‘Windowed function can only appear in SELECT or ORDER BY clause’:

Then I did some workaround and used the Windows function indirectly using a CTE (Common Table Expression) for this. I will show you step by step how I accomplished this using CTE.

Let’s first create a table with some test data,
    CREATE TABLE Test 
    ( 
        ID INT, 
        Value VARCHAR(10) NOT NULL 
    ) 
    GO 
     
    INSERT INTO Test (Value) VALUES('Sunday'),('Monday'),('Tuesday'),('Wednesday'),('Thursday'),('Friday'),('Saturday') 
    GO 

As we can see that in column ID NULL values get inserted as we didn’t specify any values for this column during INSERT statement. So, when I tried this UPDATE statement using ROW_NUMBER() with SET I got the following error,
    UPDATE Test 
    SET ID = ROW_NUMBER() OVER(ORDER BY Value) 
    GO 

Then I used CTE to update the ID values in table test in this manner,
    ;WITH CTE AS 
    ( 
        SELECT Value, ROW_NUMBER() OVER(ORDER BY Value) as RN 
        FROM Test 
    ) 
     
    UPDATE T 
    SET ID = RN 
    FROM CTE C JOIN Test T ON T.value = C.Value 


When I ran this SQL code I didn’t get any error and ID column values to get updated with unique values.

European 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.



European SQL 2016 Hosting - HostForLIFE.eu :: Multiple SQL Operation In Single Procedure

clock July 12, 2018 08:14 by author Peter

In this blog, I will show you how to write multiple SQL Statements in one procedure. Instead of writing separate procedures for Select, Insert, Delete and Update operations into the table, I am going to show you how to combine all operations into one single Stored Procedure.

This Table is called tblEmployee with the below structure:
We want to write a procedure for this table for Select, Insert, Delete and Update records.

Instead of writing separate a stored procedure for each operation we are going to write only one stored procedure to perform Select, Insert, Delete and Update records.

How To Achieve It?
Are you wondering how to accomplish this? It is simple -- just add a parameter to the stored procedure. Depending on this parameter we are going to execute the appropriate operations.

Here is the stored procedure:
Createprocedure [dbo].[USP_Employee] @empid asint=0, @empname asvarchar(50)=NULL, @age asint=0, @salary asint=0, @dob asvarchar(20)=NULL, @designation asvarchar(50)=NULL, @Reqtype asvarchar(10)=NULL AS  
BEGINIF @Reqtype='SELECT'   
BEGIN   
SELECT empid,   
       empname,   
       age,   
       salary,   
       dob,   
       designation   
FROM   tblemployee   
ENDIF @Reqtype='INSERT'   
BEGIN   
insertinto tblemployee VALUES(@empid,@empname,@age,@salary,@dob,@designation)   
ENDIF @Reqtype='DELETE'   
BEGIN   
deletefrom tblemployee WHERE [email protected]   
ENDIF @Reqtype='UPDATE'   
BEGIN   
UPDATE tblemployee   
SET    [email protected],   
       [email protected],   
       [email protected],   
       [email protected],   
       [email protected]   
WHERE  [email protected]   
ENDEND 

In the above example, based on the @Reqtype column the corresponding sql command will execute. For example, if the @Reqtype is select then select statement will execute. If the @Reqtype is inserted then Insert statement will execute.

In this blog, we have learned how to write multiple SQL operations into a single SQL procedure.

European 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 2016 Hosting, ASP.NET Core 2.2.1 Hosting, ASP.NET MVC 6 Hosting and SQL 2017 Hosting.


Tag cloud

Sign in