Database

Member 11919722 wrote:

i think its the simplest one

Member 11919722 wrote:

select dt_bill_date from sc_bill where sc_bill.st_bill_no=sc_bill_details.st_bill_no and dt_bill_date>='1-7-2015'

ALTER PROCEDURE [dbo].[SP_SearchByUserNeed]
(	
@ColumnName varchar(20),
@Value Varchar(100)
)
AS
BEGIN
BEGIN try
declare @qry nvarchar(max);
set @qry = 'Select * From Product where @ColumnName like ' + '''' +  @Value + '%' + '''';
exec(@qry);
END try
BEGIN catch
print('Error in [SP_SearchByProductName]')
END catch
END

DECLARE @strSQL NVARCHAR(1000); 
SET @strSQL = N'Select * From Product where ' + @ColumnName + ' like @Value+''%'';';
EXECUTE sp_executesql @strSQL, @Value

ALTER PROCEDURE dbo.SP_SearchByUserNeed
(
    @ColumnName varchar(20),
    @Value varchar(100)
)
AS
BEGIN
DECLARE @RealColumnName sysname;
DECLARE @Statement nvarchar(max);
    
    -- Validate the column name:
    SELECT
        @RealColumnName = name
    FROM
        sys.columns
    WHERE
        object_id = OBJECT_ID('Product')
    And
        name = @ColumnName
    ;
    
    If @RealColumnName Is Null
    BEGIN
        RAISERROR('Unknown column: "%s"', 16, 1, @ColumnName);
        Return;
    END;
    
    SET @Statement = N'SELECT * FROM Product WHERE ' + QuoteName(@RealColumnName) + N' Like @Value + ''%''';
    EXEC sp_executesql @Statement, N'@Value varchar(100)', @Value;
END