update column with incremental value using sql

update column with incremental value using sql

DECLARE @NewID INT;

SET @NewID = 0;

UPDATE myTable
SET @NewID = ID = @NewID + 1

Case in where clause with parameters in sql

Case in where clause with parameters in SQL:

Suppose you have a table named "testTable" having column Col1, Col2, Col3, Col4.

If you want to match @Parameter1 with Column Col1 and @Parameter2 with Column Col2. Either of two parameters may or may not be '' (Blank). For this situation you have make procedure with if..else. In Order to avoid if.. else in Store procedure, you can use Case in where as given below:

By If...Else:
If (@Parameter1 ='' and @Parameter2 ='')
Begin
Select Col1, Col2, Col3, Col4 from testTable
End
If (@Parameter1 <>'' and @Parameter2 ='')
Begin
Select Col1, Col2, Col3, Col4 from testTable where Col1=@Parameter1
End
If (@Parameter1 ='' and @Parameter2 <>'')
Begin
Select Col1, Col2, Col3, Col4 from testTable where Col2=@Parameter2
End
If (@Parameter1 <>'' and @Parameter2 <>'')
Begin
Select Col1, Col2, Col3, Col4 from testTable where Col1=@Parameter1 and Col2=@Parameter2
End

By Case in Where Clause:

Create Procedure [dbo].[Proc_GetData]
(
@Parameter1 varchar(50) ='',
@Parameter2 varchar(50) =''
)
as
Select Col1, Col2, Col3, Col4
from testTable
where
case when @Parameter1 ='' and @Parameter2 ='' Then 1
        when @Parameter1 <>'' and @Parameter2 ='' and  Col1=@Parameter1 Then 1
        when @Parameter1 ='' and @Parameter2 <>'' and Col2=@Parameter2  Then 1
        when @Parameter1 <>'' and @Parameter2 <>'' and  Col1=@Parameter1                     and Col2=@Parameter2  Then 1
else 0
end=1

Shrink Log file in SQL

Shrink Log file in SQL

Run the below command in sql:

ALTER DATABASE DatabaseName SET RECOVERY full  
GO
ALTER DATABASE DatabaseName SET RECOVERY SIMPLE WITH NO_WAIT;
GO
USE DatabaseName 
GO
DBCC SHRINKFILE('DatabaseName _log', 0, TRUNCATEONLY)