Using Like Operator, IN Operator and Order By In Dynamic SQL

 

Using LIKE Operator

/* Variable Declaration */
DECLARE @EmpName AS NVARCHAR(50)
DECLARE @SQLQuery AS NVARCHAR(500)

/* Build and Execute a Transact-SQL String with a single parameter 
value Using sp_executesql Command */
SET @EmpName = 'John' 
SET @SQLQuery = 'SELECT * FROM tblEmployees 
WHERE EmployeeName LIKE '''+ '%' + @EmpName + '%' + '''' 
EXECUTE sp_executesql @SQLQuery

Using IN Operator

/* Variable Declaration */
DECLARE @EmpID AS NVARCHAR(50)
DECLARE @SQLQuery AS NVARCHAR(500)

/* Build and Execute a Transact-SQL String with a single 
parameter value Using sp_executesql Command */
SET @EmpID = '1001,1003' 
SET @SQLQuery = 'SELECT * FROM tblEmployees 
WHERE EmployeeID IN(' + @EmpID + ')'
EXECUTE sp_executesql @SQLQuery

Using Order By Clause

 

/* Variable Declaration */
DECLARE @OrderBy AS NVARCHAR(50)
DECLARE @SQLQuery AS NVARCHAR(500)

/* Build and Execute a Transact-SQL String with a single parameter 
value Using sp_executesql Command */
SET @OrderBy = 'Department' 
SET @SQLQuery = 'SELECT * FROM tblEmployees Order By ' + @OrderBy

EXECUTE sp_executesql @SQLQuery

 

 

 

Filter Like Flipkart,amazon,snapdeal using Sql Server Stored Procedure

 Let us take a simple example - Employee Table with common fields such as EmployeeID, Name, Department, Designation, JoiningDate, Salary and Description. You can use the following Transact-SQL CREATE TABLE statement to create a Employee Table within your database.

 

CREATE TABLE tblEmployees
(
    EmployeeID       SMALLINT IDENTITY(1001,1) NOT NULL,
    EmployeeName     NVARCHAR(100) NOT NULL,
    Department       NVARCHAR(50) NOT NULL,
    Designation      NVARCHAR(50) NOT NULL,
    JoiningDate      DATETIME NOT NULL,
    Salary           DECIMAL(10,2) NOT NULL,
    [Description]    NVARCHAR(1000) NULL 
)

The following INSERT statements insert some sample records into the tblEmployee table: 

INSERT INTO tblEmployees
(EmployeeName, Department, Designation, 
 JoiningDate, Salary, [Description]) 
VALUES    
('John Smith', 'IT Research', 'Research Analyst', 
 '02/08/2005', 23000.00, 'Analyst since 2005')

INSERT INTO tblEmployees
(EmployeeName, Department, Designation, 
 JoiningDate, Salary, [Description]) 
VALUES    
('John Micheal', 'IT Operations', 'Manager', 
 '07/15/2007', 15000.00, NULL)

INSERT INTO tblEmployees
(EmployeeName, Department, Designation, 
 JoiningDate, Salary, [Description]) 
VALUES    
('Will Smith', 'IT Support', 'Manager', 
 '05/20/2006', 13000.00, 'Joined last year as IT Support Manager')

Create Procedure 

Create Procedure sp_EmployeeSelect
    /* Input Parameters */
    @EmployeeName NVarchar(100),
    @Department NVarchar(50),
    @Designation NVarchar(50),
    @StartDate DateTime,
    @EndDate DateTime,
    @Salary    Decimal(10,2)
        
AS
    Set NoCount ON
    /* Variable Declaration */
    Declare @SQLQuery AS NVarchar(4000)
    Declare @ParamDefinition AS NVarchar(2000) 
    /* Build the Transact-SQL String with the input parameters */ 
    Set @SQLQuery = 'Select * From tblEmployees where (1=1) ' 
    /* check for the condition and build the WHERE clause accordingly */
    If @EmployeeName Is Not Null 
         Set @SQLQuery = @SQLQuery + ' And (EmployeeName = @EmployeeName)'

    If @Department Is Not Null
         Set @SQLQuery = @SQLQuery + ' And (Department = @Department)' 
  
    If @Designation Is Not Null
         Set @SQLQuery = @SQLQuery + ' And (Designation = @Designation)'
  
    If @Salary Is Not Null
         Set @SQLQuery = @SQLQuery + ' And (Salary >= @Salary)'

    If (@StartDate Is Not Null) AND (@EndDate Is Not Null)
         Set @SQLQuery = @SQLQuery + ' And (JoiningDate 
         BETWEEN @StartDate AND @EndDate)'
    /* Specify Parameter Format for all input parameters included 
     in the stmt */
    Set @ParamDefinition =      ' @EmployeeName NVarchar(100),
                @Department NVarchar(50),
                @Designation NVarchar(50),
                @StartDate DateTime,
                @EndDate DateTime,
                @Salary    Decimal(10,2)'
    /* Execute the Transact-SQL String with all parameter value's 
       Using sp_executesql Command */
    Execute sp_Executesql     @SQLQuery, 
                @ParamDefinition, 
                @EmployeeName, 
                @Department, 
                @Designation, 
                @StartDate, 
                @EndDate,
                @Salary
                
    If @@ERROR <> 0 GoTo ErrorHandler
    Set NoCount OFF
    Return(0)
  
ErrorHandler:
    Return(@@ERROR)
GO 

 

Split String In sqlserver

 CREATE FUNCTION [dbo].[SplitString]
(   
      @Input NVARCHAR(MAX),
      @Character CHAR(1)
)
RETURNS @Output TABLE (
      Item NVARCHAR(1000)
)
AS
BEGIN
      DECLARE @StartIndex INT, @EndIndex INT
 
      SET @StartIndex = 1
      IF SUBSTRING(@Input, LEN(@Input) - 1, LEN(@Input)) <> @Character
      BEGIN
            SET @Input = @Input + @Character
      END
 
      WHILE CHARINDEX(@Character, @Input) > 0
      BEGIN
            SET @EndIndex = CHARINDEX(@Character, @Input)
            
            INSERT INTO @Output(Item)
            SELECT SUBSTRING(@Input, @StartIndex, @EndIndex - 1)
            
            SET @Input = SUBSTRING(@Input, @EndIndex + 1, LEN(@Input))
      END
 
      RETURN
END

SQL Server Evaluation Period has expired

 you can follow  below url

https://spgeeks.devoworx.com/evaluation-period-expired-for-sql-server-2012-how-to-extend-it/

 

if not success

Attach SQL Server serial number:

Development Edition: PTTFM-X467G-P7RH2-3Q6CG-4DMYB
Enterprise Edition: JD8Y6-HQG69-P9H84-XDTPG-34MBB

 

and you can choose maintenance tab and then choose edition upgrade

then put above serial number.

 

Drop all tables, stored procedure, views and triggers

 

1.Remove all Tables

-- drop all user defined tables
EXEC sp_MSforeachtable @command1 = "DROP TABLE ?" 

2.Remove all User-defined Stored Procedures

 -- drop all user defined stored procedures
Declare @procName varchar(500) 
Declare cur Cursor For Select [name] From sys.objects where type = 'p' 
Open cur 
Fetch Next From cur Into @procName 
While @@fetch_status = 0 
Begin 
 Exec('drop procedure ' + @procName) 
 Fetch Next From cur Into @procName 
End
Close cur 
Deallocate cur 

3.Remove all Views

 -- drop all user defined views
Declare @viewName varchar(500) 
Declare cur Cursor For Select [name] From sys.objects where type = 'v' 
Open cur 
Fetch Next From cur Into @viewName 
While @@fetch_status = 0 
Begin 
 Exec('drop view ' + @viewName) 
 Fetch Next From cur Into @viewName 
End
Close cur 

3.Remove all Triggers

-- drop all user defined triggers
Declare @trgName varchar(500) 
Declare cur Cursor For Select [name] From sys.objects where type = 'tr' 
Open cur 
Fetch Next From cur Into @trgName 
While @@fetch_status = 0 
Begin 
 Exec('drop trigger ' + @trgName) 
 Fetch Next From cur Into @trgName 
End
Close cur 
Deallocate cur 

 

 

 

 

Easiest way to remove .aspx from url in asp.net

1.Install Nuget package

Install-Package Microsoft.AspNet.FriendlyUrls

2.Files installed by friendly urls in asp .net

 

 

3.Add Global.asax file

 

void Application_Start(object sender, EventArgs e)

{

   RouteConfig.RegisterRoutes(System.Web.Routing.RouteTable.Routes);

}

 

 

USING SCRIPT GET TABLE STRUCTURE IN SQL SERVER


Create Procedure GenerateScript (              
@tableName varchar(100))              
as              
If exists (Select * from Information_Schema.COLUMNS where Table_Name= @tableName)              
Begin               
declare @sql varchar(8000)               
declare @table varchar(100)               
declare @cols table (datatype varchar(50))             
insert into @cols values('bit')             
insert into @cols values('binary')             
insert into @cols values('bigint')             
insert into @cols values('int')             
insert into @cols values('float')             
insert into @cols values('datetime')             
insert into @cols values('text')             
insert into @cols values('image')             
insert into @cols values('uniqueidentifier')             
insert into @cols values('smalldatetime')             
insert into @cols values('tinyint')             
insert into @cols values('smallint')             
insert into @cols values('sql_variant')             
              
set @sql=''               
Select @sql=@sql+                
case when charindex('(',@sql,1)<=0 then '(' else '' end +Column_Name + ' ' +Data_Type +                
case when Data_Type in (Select datatype from @cols) then '' else  '(' end+   
case when data_type in ('real','money','decimal','numeric')  then cast(isnull(numeric_precision,'') as varchar)+','+   
case when data_type in ('real','money','decimal','numeric') then cast(isnull(Numeric_Scale,'') as varchar) end   
when data_type in ('char','nvarchar','varchar','nchar') then cast(isnull(Character_Maximum_Length,'') as varchar)       else '' end+   
case when Data_Type in (Select datatype from @cols)then '' else  ')' end+   
case when Is_Nullable='No' then ' Not null,' else ' null,' end              
from Information_Schema.COLUMNS where Table_Name=@tableName               
    
     
select  @table=  'Create table ' + table_Name from Information_Schema.COLUMNS where table_Name=@tableName              
select @sql=@table + substring(@sql,1,len(@sql)-1) +' )'              
select @sql  as DDL            
     
End              
   
Else           
   
   
Select 'The table '+@tableName + ' does not exist'