Visual Studio Tips: Copy/Paste multiple snippets in quicker way


In this quick post, I just want to share a very useful tip on Visual Studio which has been helping me numerous times since I learned it. While developing an application using visual studio, you may need to copy multiple words/lines/snippets which are not continuous on that form/page. With current facility of Windows(how far I know) we can only paste the content which is copied to the clipboard last time only. So, to copy and paste multiple snippets you have to copy a single snippet and then paste it on some other page/form and then again repeat the same process until you are done.

But with the feature I am talking about, you can copy up to 20 snippets to the clipboard and then can traverse through each snippet copied to the clipboard. This is called Clipboard Ring.

To copy the snippets just copy them with your own way like pressing CTRL + C or right clicking with your mouse etc.

Now, you can paste/traverse through copied snippets by pressing CTRL + Shift + V and CTRL+ Shift + Insert. or, Clicking Edit ->Cycle Clipboard Ring.

 

Thanks for reading !!!

Advertisements

SQL Server: Stored Procedure for update with optional parameters


Problem:

One more article in Problem-Solution approach to keep it short and simple.

When we are writing a stored procedure to update different fields of a table for one or more records, we may write as the below script.

CREATE PROCEDURE [dbo].[UpdtEmployee]
(
    @EmpId INT,
    @EmpName VARCHAR(100)=NULL,
    @Address VARCHAR(200)=NULL,
    @EmpPhotoPath VARCHAR(100)=NULL,
    @MobileNo VARCHAR(14)=NULL
)
AS
BEGIN
    UPDATE dbo.Employee
    SET EmpName=@EmpName,
        Address=@Address,
        EmpPhotoPath=@EmpPhotoPath,
        MobileNo=@MobileNo
    WHERE EmpId=@EmpId
END

The problem with the above stored procedure is when we want to update only those fields/columns of the record for which corresponding parameter is not having NULL values then it will not yield the anticipated result. In this scenario this stored procedure will update all the fields with the values from corresponding parameters. Let’s take an example so that the problem can be understood clearly.

Example: Let’s assume that we have inserted values to all the fields of a record. Now we want to modify only the mobile number of that employee. If we call this procedure then it will make values of all other fields to NULL as we have declared the default value for all the parameters as NULL.

Solution:

Solution to this problem is to avoid updating those fields which have corresponding parameters with NULL values or, we can update existing data corresponding to those fields. We will go for the later one. Now, the modified script may look like


CREATE PROCEDURE [dbo].[UpdtEmployee]
(
    @EmpId INT,
    @EmpName VARCHAR(100)=NULL,
    @Address VARCHAR(200)=NULL,
    @EmpPhotoPath VARCHAR(100)=NULL,
    @MobileNo VARCHAR(14)=NULL
)
AS
BEGIN
    UPDATE dbo.Employee
    SET EmpName=ISNULL(@EmpName,EmpName),
        Address=ISNULL(@Address,Address),
        EmpPhotoPath=ISNULL(@EmpPhotoPath,EmpPhotoPath),
        MobileNo=ISNULL(@MobileNo,MobileNo)
    WHERE EmpId=@EmpId
END

Hopefully this article may be useful to you.

Thanks !!!

MySQL: Creating a simple stored procedure


Let’s start creating stored procedure with a very simple one and then we will analyse it line by line.

DELIMITER $$

DROP PROCEDURE IF EXISTS MyFirstSP$$
CREATE PROCEDURE MyFirstSP()
BEGIN
 SELECT * FROM MyTable;
END$$

Analysis:

  • By default MySQL treats semicolon(;) as the statement terminator or end of statement. But as we are going to use it inside the procedure body, so we need another different delimiter to state the end of the stored procedure. DELIMITER $$ sets $$ as the statement terminator.
  •  The DROP PROCEDURE IF EXISTS <SPName> statement checks for a duplicate stored procedure with the same name and if there exists any then issue a DROP command. You can skip this line if you are sure that there is no other stored procedure exists with the same name in your selected database.
  • Statement CREATE PROCEDURE marks the start of the stored procedure definition. Here, MyFirstSP is the name of our stored procedure. The stored procedure name followed by a pair of parentheses. The use of these parentheses is to define parameters inside it. In this stored procedure we don’t need any parameters, but we have to put these parentheses as this is mandatory in MySQL unlike SQL Server.
  • The BEGIN Statement marks the start or begining of a block (here the block is the stored procedure itself).In stored procedures, every statements with multiple statements should be enclosed with a block defined by BEGIN and END, where END statement marks the end of the block;
  •  The statement inside the BEGIN .. END is a simple SELECT query, which fetches all the records from MyTable table.

CodeProject

SQL Server: An optimal way to create procedure with multiple optional parameters


Problem:

I took the Problem-Solution approach to write this post to keep it short and simple.

Here the problem I am talking about is the scenario when you have to write a stored procedure in SQL Server with multiple search options/parameters then you may use some IF…ELSE .. statements to do it. Let’s take an short example.

Example:


CREATE PROCEDURE [dbo].[getEmployeeList]
(
   @DeptId INT=NULL,
   @OfficeId INT=NULL
)
AS
BEGIN
    IF(@DeptId IS NOT NULL AND @OfficeId IS NOT NULL)
        BEGIN
            SELECT *
            FROM dbo.Employees
            WHERE DeptId=@DeptId AND OfficeId=@OfficeId
        END
    ELSE IF(@DeptId IS NOT NULL AND @OfficeId IS NULL)
        BEGIN
            SELECT *
            FROM dbo.Employees
            WHERE DeptId=@DeptId
        END
    ELSE IF(@DeptId IS NULL AND @OfficeId IS NOT NULL)
        BEGIN
            SELECT *
            FROM dbo.Employees
            WHERE OfficeId=@OfficeId
         END
    ELSE
        BEGIN
            SELECT *
            FROM dbo.Employees
        END
END

Here we can have 4 combinations of search options.

Now, if we want add some more parameters like SectionId,DesignationId,CityId etc. to this stored procedure then it will be a tough task to manage it and may end-up with a 100’s lines of script.

Solution:

I found a solution for this problem by creating multiple conditions for a single WHERE… tag. Now, if I implement it to the previous example then the stored procedure will be like the below script-


CREATE PROCEDURE [dbo].[getEmployeeList]
(
    @DeptId INT=NULL,
    @OfficeId INT=NULL
)
AS
BEGIN
   SELECT *
   FROM dbo.Employees
   WHERE (@DeptId IS NULL OR DeptId=@DeptId)
   AND (@OfficeId IS NULL OR OfficeId=@OfficeId)
END

Just add conditions like these two to the WHERE part, as many as optional parameters you have in your stored procedure.

I hope it will be helpful to those who are looking a solution for the above said problem. Please share your thoughts and ideas regarding this post.

Thank you.

SQL Server: encrypt password


This post is about encrypting a value before inserting it to your password  field in SQL Server. This is very much similar as PASSWORD(),SHA1(), etc. in MySQL. In SQL Server we can use the same through PWDENCRYPT() function.

Syntax for insert:


INSERT INTO Table1 (field1,field2) VALUES(value1, PWDENCRYPT(value2))

Example:


INSERT INTO dbo.UserLogin (Uid,Pwd) VALUES('User', PWDENCRYPT('Suvendu'))

Here, ‘Suvendu’ is the value for my Pwd field which I want to encrypt.

Syntax for comparing:


SELECT * FROM Table1 WHERE field1=value1 AND field2=PWDENCRYPT(value2)

Example:


SELECT * FROM dbo.UserLogin WHERE Uid='User' AND Pwd=PWDENCRYPT('Suvendu')

Reverse  is not possible, I mean decryption can not done for this encrypted value.

NOTE: It is a undocumented function. As far I know, PWDENCRYPT() is supported by SQL Server version upto SQL Server 2012 (beta) but, MSDN says it may not be available there in the future versions of SQL Server.

SQL Server: Function to get previous session year


When you need to get previous session year of a supplied session year then you can use the below function.

Step-1 :

Execute below script.


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Suvendu>
-- Create date: <13 Mar 2012>
-- Description: <returns prev sess yr of the supplied sess yr>
-- =============================================
CREATE FUNCTION [dbo].[fuGetPrevSessionYr]
(
 @SessYr NVARCHAR(7)
)
RETURNS NVARCHAR(7)
AS
BEGIN
 DECLARE @PrevSessYr NVARCHAR(7)
 SELECT @PrevSessYr=CAST((CAST(LEFT(@SessYr,4) AS INT)-1) AS VARCHAR(4))+'-'+RIGHT(LEFT(@SessYr,4),2)

 RETURN @PrevSessYr
END

Step-2:

Syntax for calling the function-

SELECT dbo.fuGetPrevSessionYr(<SessionYr>) [FROM TABLE1]

Where  <SessionYr> is the session year, whose previous session you want to show.

SQL Server: Function to get fiscal year or, session year


You can use this function to get the fiscal/financial year from a supplied date value. This may be useful when you have a datetime column in a table but you want to show it’s fiscal year. Another case when you may need this function is you have a datetime column in your table but you have to show the transaction made, specified fiscal year/session year wise or when you are preparing any statistical report.


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Suvendu>
-- Create date: <10 mar 2012>
-- Description: <returns the fiscal year of the supplied date>
-- =============================================
CREATE FUNCTION [dbo].[fuGetFiscalYear]
(
 @SuppliedDate DATETIME
)
RETURNS NVARCHAR(7)
AS
BEGIN
 DECLARE @SessYr NVARCHAR(7)
 SELECT @SessYr= CASE WHEN DATEPART(MONTH,@SuppliedDate)>3 THEN CAST(DATEPART(YEAR,@SuppliedDate) AS VARCHAR(4))+'-'+RIGHT(CAST(DATEPART(YEAR,@SuppliedDate)+1 AS VARCHAR(4)),2) ELSE CAST(DATEPART(YEAR,@SuppliedDate)-1 AS VARCHAR(4))+'-'+RIGHT(CAST(DATEPART(YEAR,@SuppliedDate) AS VARCHAR(4)),2) END
 RETURN @SessYr
END
GO

[Note: I have assumed the fiscal year as Indian fiscal year or, financial year. You can change it accordingly or, can use the below function]


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Suvendu>
-- Create date: <10 mar 2012>
-- Description: <returns the fiscal year of the supplied date>
-- =============================================
CREATE FUNCTION [dbo].[fuGetSessionYear]
(
 @SuppliedDate DATETIME,
 @StartMonth INT
)
RETURNS NVARCHAR(7)
AS
BEGIN
 DECLARE @SessYr NVARCHAR(7)
 SELECT @SessYr= CASE WHEN DATEPART(MONTH,@SuppliedDate)>@StartMonth THEN CAST(DATEPART(YEAR,@SuppliedDate) AS VARCHAR(4))+'-'+RIGHT(CAST(DATEPART(YEAR,@SuppliedDate)+1 AS VARCHAR(4)),2) ELSE CAST(DATEPART(YEAR,@SuppliedDate)-1 AS VARCHAR(4))+'-'+RIGHT(CAST(DATEPART(YEAR,@SuppliedDate) AS VARCHAR(4)),2) END
 RETURN @SessYr
END
GO