Dynamic Stored Procedures

Have you every felt the need to write a stored procedure that would be modified at runtime?

Well then let me show you how you can achieve this in SQL Server 2005 or upper version.

First of all let me tell you about the scenario where i had to write such a stored procedure:
Recently i was dealing with a large database (having more than 5 million records), and i had to provide the client with an option to search and filter data from a table with various search parameters in an asp.net web page.
Now few of the parameters when combined together in the where clause of the search query worked fine without much delay (mostly integer columns), but some the text columns took really long time especially when combined together in the where clause. For instance:
Query: select * from table 1 where
personame ='' or
cityid= '' or
state='' .....

In such a scenario i had to write an dynamic stored procedure which would accept parameters and form query according to the number of  paramters passed.

Here is the stored procedure that i had wrote for fetching some patients information from an insurance database:

ALTER procedure [dbo].[sp_GetEligibility] 
(@Insured nvarchar(50)=null,@GroupKey nvarchar(50)=null,MAX_RESULTS nvarchar(50)=null )
 
AS 
    BEGIN

    if @MAX_RESULTS is null
    begin
    set @MAX_RESULTS=250
    end

    DECLARE @SQL nvarchar(MAX)
    DECLARE @SQLWhere nvarchar(MAX)

    set @SQLWhere = ''
    set @Insured = LTRIM(RTRIM(@Insured))        
    set @GroupKey = LTRIM(RTRIM(@GroupKey))

    if (@Insured is not null And @Insured <> '')
    begin
    --set @SQLWhere = @SQLWhere + ' and (I.ssn LIKE ''%' + @Insured + '%'' ) '
    set @SQLWhere = @SQLWhere + ' and (I.ssn LIKE ''%'' + @Insured + ''%'' ) '
    end

   
    if (@GroupKey is not null And @GroupKey = '')  
    begin 
    set @GroupKey = null
    end
    Else if @GroupKey <> '0'
    begin
    --set @SQLWhere = @SQLWhere + ' and (E.fk_group = '+ @GroupKey + ')'
    set @SQLWhere = @SQLWhere + ' and (E.fk_group =  @GroupKey )'
    end

   

    IF not (@Insured is null and @GroupKey is null)  

    BEGIN 
 
     Select @SQL ='Select TOP(' + @MAX_RESULTS + ') * from (' +
      'select TOP(' + @MAX_RESULTS + ') I.first_name,' +
      'R.Relationship,' +
      'I.last_name, ' +
      'I.SSN,' +
      'E.subscriber_id,' + 
      'I.first_name + '' ''+ I.last_name as Names,' +
      'e.eff_date,' +  
      'e.term_date,' +
      'G.group_name,' +
      'B.benefitPlan_name, ' +     
      'convert(varchar,D.eff_date ,101) as dependEffDate,' +
      'convert(varchar,d.Term_date ,101)  as dependTermdate,' +        
      'I.pk_insured,' +  
      '(SELECT top 1 ' +
      '(' + 
      '         SELECT distinct(C.benefit_name + '', '')' +  
      '         FROM  EligibilityBenefitCodes B ' +  
      '         left join BenefitPlanCodes C on c.pk_benefitplancodes = B.fk_benefitplancodes' + 
      '         left join BenefitPlan BP on BP.pk_benefitplan= C.fk_benefitplan ' +
      '         WHERE  C.pk_benefitplancodes = B.fk_benefitplancodes' +  
      '         and  B.fk_eligibility = E.pk_eligibility  FOR XML PATH('''')' +    
      ' )' +
      ')AS TypeofCoverage ' +   
      'from Eligibility E With(NOLOCK) ' + 
      'Inner Join Dependents D With(NOLOCK) ON e.fk_insured=D.fk_insuredprimary ' +  
      'Inner Join Insured I With(NOLOCK) ON I.pk_insured=D.fk_insuredDependent and E.fk_insured = I.pk_insured ' +  
      'Inner Join BenefitPlan B With(NOLOCK) ON E.fk_benefitplan=B.pk_benefitplan ' +  
      'Inner Join Relation R With(NOLOCK) ON I.fk_relation=R.pk_relation ' +  
      'Inner Join [Group] G With(NOLOCK) ON E.fk_group = G.pk_group ' +  
      'where e.eff_date <= getDate()  and E.IsArchived=0 ' +        
       @SQLWhere +                       
      ' UNION  ' +
     'select TOP(' + @MAX_RESULTS + ') I.first_name,R.Relationship,I.last_name, I.SSN,  E.subscriber_id, ' + 
     'I.first_name + '' ''+ I.last_name as Names, e.eff_date, ' +  
     'e.term_date, G.group_name, B.benefitPlan_name, ' +  
     'convert(varchar,D.eff_date ,101) as dependEffDate, ' +
     'convert(varchar,d.Term_date ,101)  as dependTermdate, ' +  
     'I.pk_insured  , ' +  
     '(SELECT top 1' + 
     ' (  ' +
     '  SELECT distinct(C.benefit_name + '', '')' +  
     'FROM  EligibilityBenefitCodes B ' +  
     'left join BenefitPlanCodes C on c.pk_benefitplancodes = B.fk_benefitplancodes ' + 
     'left join BenefitPlan BP on BP.pk_benefitplan= C.fk_benefitplan ' + 
     'WHERE  C.pk_benefitplancodes = B.fk_benefitplancodes  and  B.fk_eligibility = E.pk_eligibility ' + 
     ' FOR XML PATH('''') ' +    
     '  )) AS BenefitCode ' +   
     'from Eligibility E With(NOLOCK) ' +   
     'Inner Join Dependents D With(NOLOCK) ON e.fk_insured=D.fk_insuredprimary ' + 
     'Inner Join Insured I With(NOLOCK) ON I.pk_insured=D.fk_insuredDependent ' +  
     'Inner Join BenefitPlan B With(NOLOCK) ON E.fk_benefitplan=B.pk_benefitplan ' +  
     'Inner Join Relation R With(NOLOCK) ON I.fk_relation=R.pk_relation ' +  
     'Inner Join [Group] G With(NOLOCK) ON E.fk_group = G.pk_group ' +  
     'where  e.eff_date <= getDate()  and E.IsArchived=0 ' +
     @SQLWhere +
    ')A  ' +
    '  order by A.Relationship,A.first_name,A.last_name desc' 
 
    --EXEC(@SQL)
    --For preventing SQL Injection use following method
    EXEC sp_executesql @SQL, N'@Insured nvarchar(50),@GroupKey nvarchar(50)',@Insured,@GroupKey

  END

END

As you can see in this example, the stored procedure generates the sql query as string and at the end the parameters are passed to the generated final query.
This is pretty much similar to the if else conditions that we used to write in code behind to generate a query.

Encrypt Sensitive Data in SQL Server 2005

SQL Server 2005 provides easier functions for encrypting and decrypting user sensitive information such as credit card numbers or bank account details, so as to deter any hacking attempts.

Data encryption in SQL Server 2005 can be done either by using password mechanism or by making use of keys and certificates. These methods are as follows:

1.Encryption by PassPhrase
This is a simple method in which we use the SQL method EncryptByPassPhrase('password','original_value') with our insert,update,select queries.

For an example suppose we have a table named user_info with the column named credit_card_no (varchar) in which we have to stored the encrypted credit card no then the query would be as follows:

Update user_info set credit_card_no=EncryptByPassPhrase('password',@credit_card_no);

where password is the key used to generate the encrypted value.
The same key is used to decrypt the credit_card_no as follows:

Select DecryptByPassPhrase('password',credit_card_no) as decrypted_no from user_info.

Note:The password has to be protected and remembered by the programmer. Hence can be vulnerable.


2.Encryption by Keys

The limitation of encryption by passphrase method is that we have to supply the password each time the data has to be accessed. But if we encrypt our symmetric key with a certificate then we won't have to pass the passphrase each time. To create a key or its certificate, we must first create or open the master key for the database.
The following command creates a master key:

create master key encryption by password='password';

Now we can create a certificate and then a symmetric key that is attached to that certificate. The following SQL script creates the certificate 'DemoCert' and a key 'DemoKey' associated with that certificate:

create certificate DemoCert with subject='Demo Certificate;

create symmetric key DemoKey with algorithm=AES_256 encryption by certificate DemoCert;

Now that we possess a key we can do encryption using the EncryptByKey() method and considering the previous table user_info as follows:

open symmetric key DemoKey decryption by certificate DemoCert;
update user_info set credit_card_no=EncryptByKey(Key_GUID('DemoKey'),@credit_card_no);

Similarly we can decrypt it as follows:

open symmetric key DemoKey decryption by certificate DemoCert;
select cast(DecryptByKey(credit_card_no) as varchar(16)) as decrypted_no from user_info;

This is a lenghty method but is very secure as we do not have to pass the password for the process of encryption/decryption.


So this is one advance features of SQL Server 2005 that not many of us use. But it can surely come in handy when dealing with large user database that requires some security features!!

Using T-SQL to Simplify Coding

A sign of a good web developer is determined by his knowledge of SQL. Generally speaking if you know SQL well (be it SQL Server or MySQL) chances are that most of your coding will be reduced dramatically saving you lots of time and also increasing website performance.
Take for example if you wanted to change the status of a registered user from Active to De active or vice verse on the click of a single button and you are using a int field for it in the database( 0 and 1). Now as a novice what I used to do is run a SELECT query and get the user status field. Then compare the status in client side scripting language, and then again run a Update query to change the user status.
But as i studied more on SQL i found out that this could be done just by using a single query/stored procedure and only passing the id of the user to the query as follows(for SQL Server):

Create Procedure changeStatus
@ID integer
BEGIN
Declare @status integer
Select @status= status from UserTable where UserId=@ID
if @status=1
Update UserTable set status=0 where UserId=@ID
ELSE
Update UserTable set status=1 where UserId=@ID
END
END

So you see this has saved me a lot of time and coding. Also modification also becomes easy. Hence if you still think that SQL knowledge is not that important for Development; its time to wake up and brush up on YOUR SQL SKILLS!!!!!!