stored procedure with case and actions

Intoduction :-

Hii Friends...

Today I explain how to use case and action in stored procedure . 


CREATE Proc [dbo].[Proc_ManageRatingAndReview]--exec [Proc_ManageRatingAndReview] 'GetAllBySeller','','','101647'                
(                           
@Action nvarchar(50)='',                           
@ID int=0,       
@ProductID int=0,       
@SellerMsrNo int=0                          
)                           
as                           
begin                           
                            
if(@Action='Delete')                           
begin                           
delete tblRatingAndReview Where RatingAndReviewID = @ID                           
end                           
                            
                                 
if(@Action='IsActive')                           
begin                           
update tblRatingAndReview set IsActive=case when IsActive=0 then 1 else 0 end                            
where RatingAndReviewID=@ID                           
end                           
                            
                            
if(@Action='GetAll')                           
begin                           
Select * from tblRatingAndReview                            
where RatingAndReviewID= case when @ID=0 then RatingAndReviewID  else @ID  end  order by ProductID desc                           
end                         
                            
                         
if(@Action='Get')                           
begin                           
Select * from tblRatingAndReview                           
where RatingAndReviewID= case when @ID=0 then RatingAndReviewID else @ID  end and  IsActive='true'  order by ProductID desc                               
end      
     
if(@Action='GetAllBySeller')                           
begin                           
Select distinct IV.ProductName,RAR.*,MM.FirstName+' '+MM.LastName as SellerName from tblRatingAndReview as RAR    
inner join tblInvoiceDetail as IV on RAR.ProductID=IV.ProductID    
inner join tblMlm_memberMaster as MM on RAR.SellerMsrNo= MM.MsrNo                        
where RAR.SellerMsrNo= case when @SellerMsrNo=0 then RAR.SellerMsrNo  else @SellerMsrNo  end  order by RAR.ProductID desc                           
end    
                          
                       
                       
if(@Action='GetByProductID')                           
begin                           
Select RatingAndReviewID, ReviewTitle, YourReview, CONVERT(decimal(5,0),YourRating)as YourRating,IPAddress, ProductID, UserName, UserEmail, AddDate, LastUpdate, IsActive from tblRatingAndReview                           
where ProductID= case when @ProductID=0 then ProductID else @ProductID  end       
and SellerMsrNo= case when @SellerMsrNo=0 then SellerMsrNo else @SellerMsrNo  end       
and  IsActive='true' order by ProductID,AddDate desc                               
end                         
                   
                   
if(@Action='GetRating')                       
begin                       
                   
declare @Rating decimal(5,0)                   
select @Rating=ROUND(AVG(YourRating), 0) FROM tblRatingAndReview where ProductID=                   
 case when @ID=0 then ProductID                   
 else @ID                   
 end and  IsActive='true'                   
                   
if @Rating>0                   
begin                    
 select @Rating as Rating                   
end                   
else                   
begin                   
 select 4 as Rating                   
end                   
                   
                   
end                               
end

Read More

Some tricks for stored procedures

Intoduction :-

Hii Friends...


Today I give some tricks that help in stored procedures when we are fixed. Like when we not found any stored procedure , or we alter any stored procedure then what we do.

 Trick 1 :-    when procedure not found then we write this query only . With the help of this query you can find any stored procedure that you want.


select * from sys.objects where name like '%yourSearchText'   

In this you only change the Stored procedure name that you want  and you find it.


Trick 2 :-    when procedure goes alter then recovery process is given blow .


SELECT deqs.last_execution_time AS [Time], dest.text AS [Query]
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
ORDER BY deqs.last_execution_time DESC    


with the help of this query you can recover your sp.

Read More

How to make ck editor in Asp.net

Intoduction :-

Hii Friends...


Today I explain how to make  Ck editor in asp.net. I think this will be interesting and time consuming for CMS type data entry where CKEditor is quite often used. As this process can be used easily and it supports easy integration between different projects, programmers will be helped a lot.

Step : 1   The Code goes here :->

 Firstly add this script on designing page as below given.

<%@ Register Assembly="CKEditor.NET" Namespace="CKEditor.NET" TagPrefix="CKEditor" %>

Step : 2  Design Code here :->

<div>
   <CKEditor:CKEditorControl ID="CKEditor1" BasePath="/ckeditor/" runat="server">
   </CKEditor:CKEditorControl>
</div>

 Step : 3  CS Code here :->

protected void Button1_Click(object sender, EventArgs e)
        {
            string str = CKEditor1.Text;
            string str1 = Server.HtmlEncode(str);
            string str2 = Server.HtmlDecode(str);
            lblText.Text = str2;
        } 

  DEMO:-

 

Read More

How to get a table Script

Intoduction :- 

Hii Friends...

Today I explain how to get a table Script when we not get this manually then we use this code. In this code we only change a table name that you want and execute it . You will get that table Script . 

Step : 1   The Code goes here :->

DECLARE @table_name SYSNAME
SELECT @table_name = 'dbo.Tbl_User_LoginDetail'

DECLARE
      @object_name SYSNAME
    , @object_id INT

SELECT
      @object_name = '[' + s.name + '].[' + o.name + ']'
    , @object_id = o.[object_id]
FROM sys.objects o WITH (NOWAIT)
JOIN sys.schemas s WITH (NOWAIT) ON o.[schema_id] = s.[schema_id]
WHERE s.name + '.' + o.name = @table_name
    AND o.[type] = 'U'
    AND o.is_ms_shipped = 0

DECLARE @SQL NVARCHAR(MAX) = ''

;WITH index_column AS
(
    SELECT
          ic.[object_id]
        , ic.index_id
        , ic.is_descending_key
        , ic.is_included_column
        , c.name
    FROM sys.index_columns ic WITH (NOWAIT)
    JOIN sys.columns c WITH (NOWAIT) ON ic.[object_id] = c.[object_id] AND ic.column_id = c.column_id
    WHERE ic.[object_id] = @object_id
)
SELECT @SQL = 'CREATE TABLE ' + @object_name + CHAR(13) + '(' + CHAR(13) + STUFF((
    SELECT CHAR(9) + ', [' + c.name + '] ' +
        CASE WHEN c.is_computed = 1
            THEN 'AS ' + cc.[definition]
            ELSE UPPER(tp.name) +
                CASE WHEN tp.name IN ('varchar', 'char', 'varbinary', 'binary', 'text')
                       THEN '(' + CASE WHEN c.max_length = -1 THEN 'MAX' ELSE CAST(c.max_length AS VARCHAR(5)) END + ')'
                     WHEN tp.name IN ('nvarchar', 'nchar', 'ntext')
                       THEN '(' + CASE WHEN c.max_length = -1 THEN 'MAX' ELSE CAST(c.max_length / 2 AS VARCHAR(5)) END + ')'
                     WHEN tp.name IN ('datetime2', 'time2', 'datetimeoffset')
                       THEN '(' + CAST(c.scale AS VARCHAR(5)) + ')'
                     WHEN tp.name = 'decimal'
                       THEN '(' + CAST(c.[precision] AS VARCHAR(5)) + ',' + CAST(c.scale AS VARCHAR(5)) + ')'
                    ELSE ''
                END +
                CASE WHEN c.collation_name IS NOT NULL THEN ' COLLATE ' + c.collation_name ELSE '' END +
                CASE WHEN c.is_nullable = 1 THEN ' NULL' ELSE ' NOT NULL' END +
                CASE WHEN dc.[definition] IS NOT NULL THEN ' DEFAULT' + dc.[definition] ELSE '' END +
                CASE WHEN ic.is_identity = 1 THEN ' IDENTITY(' + CAST(ISNULL(ic.seed_value, '0') AS CHAR(1)) + ',' + CAST(ISNULL(ic.increment_value, '1') AS CHAR(1)) + ')' ELSE '' END
        END + CHAR(13)
    FROM sys.columns c WITH (NOWAIT)
    JOIN sys.types tp WITH (NOWAIT) ON c.user_type_id = tp.user_type_id
    LEFT JOIN sys.computed_columns cc WITH (NOWAIT) ON c.[object_id] = cc.[object_id] AND c.column_id = cc.column_id
    LEFT JOIN sys.default_constraints dc WITH (NOWAIT) ON c.default_object_id != 0 AND c.[object_id] = dc.parent_object_id AND c.column_id = dc.parent_column_id
    LEFT JOIN sys.identity_columns ic WITH (NOWAIT) ON c.is_identity = 1 AND c.[object_id] = ic.[object_id] AND c.column_id = ic.column_id
    WHERE c.[object_id] = @object_id
    ORDER BY c.column_id
    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, CHAR(9) + ' ')
    + ISNULL((SELECT CHAR(9) + ', CONSTRAINT [' + k.name + '] PRIMARY KEY (' +
                    (SELECT STUFF((
                         SELECT ', [' + c.name + '] ' + CASE WHEN ic.is_descending_key = 1 THEN 'DESC' ELSE 'ASC' END
                         FROM sys.index_columns ic WITH (NOWAIT)
                         JOIN sys.columns c WITH (NOWAIT) ON c.[object_id] = ic.[object_id] AND c.column_id = ic.column_id
                         WHERE ic.is_included_column = 0
                             AND ic.[object_id] = k.parent_object_id
                             AND ic.index_id = k.unique_index_id   
                         FOR XML PATH(N''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, ''))
            + ')' + CHAR(13)
            FROM sys.key_constraints k WITH (NOWAIT)
            WHERE k.parent_object_id = @object_id
                AND k.[type] = 'PK'), '') + ')'  + CHAR(13)

PRINT @SQL

Read More