get table and SP dependencies
EXEC sp_depends N'[dbo].[RecItemDates]'
[dbo].[RecItemDates] -- > table name
loop sql table rows
DECLARE @LoopCounter INT = 210, @MaxEmployeeId INT = 401,
@EmployeeName int = 0;
WHILE(@LoopCounter <= @MaxEmployeeId)
BEGIN
SELECT @EmployeeName = [EventDateFacilityTimeslotID]
FROM RecEventDateFacilityTimeslot WHERE [EventDateFacilityTimeslotID] = @LoopCounter
update RecEventDateFacilityTimeslot set [EventScheduleGroupID] = @EmployeeName
WHERE [EventDateFacilityTimeslotID] = @EmployeeName
PRINT @EmployeeName
SET @LoopCounter = @LoopCounter + 1
END
second example
declare @table table(
facilityID int
)
insert into @table
select facilityid from RecFacility
declare @count int = 0;
select @count = count(*) from RecFacility
print @count ;
declare @id int = 0;
While (@count > 0)
Begin
Select Top 1 @Id = facilityid From @table
print @id;
select DefaultBumpInMinutes,DefaultBumpOutMinutes from RecFacility where FacilityID = @id
update RecFacility set DefaultBumpInMinutes = (SELECT ROUND(((90 -15) * RAND() + 15), 0)) , DefaultBumpOutMinutes = (SELECT ROUND(((90 -15) * RAND() + 15), 0)) where FacilityID = @id;
set @count = @count-1;
delete from @table where facilityid = @id;
End
-------------
select statement with if EXISTS condition
IF EXISTS (SELECT * FROM Products WHERE id = ?)
BEGIN
END
========================
database normalization
create database efficiently .
---------------------------------------------
SQL functions
CREATE FUNCTION dbo.AddFunc(@First int,@Second int)
RETURNS int
AS
-- Returns the stock level for the product.
BEGIN
RETURN @First+@Second;
END;
GO
SELECT dbo.AddFunc(10,20); // 30
IS NULL RETURN DEFAULT VALUE
select EventId,AttachmentName,IsNull(fileName_, 'Unknown Name') as FileName_ from RecEventsAttachments
Alter table add colom inside if condition
IF NOT EXISTS(SELECT * FROM sys.columns WHERE Name = 'IsSystemGenarated' AND OBJECT_ID =
OBJECT_ID('SubAccounts'))
BEGIN
alter table SubAccounts
add IsSystemGenarated bit
END;
Create table if not
IF NOT EXISTS ( Select * From sysObjects Where Name ='RecEventTicketTemplate')
BEGIN
create table RecEventTicketTemplate
(
Id int identity(1, 1) primary key,
TemplateName varchar(100),
TemplateContent nvarchar(max),
IsEnable bit,
InsertedTime datetime,
InsertedUser varchar(100)
);
END
IF check value is exsist
IF NOT EXISTS (SELECT * FROM RecEventTicketingPlaceHolders WHERE PlaceHolderName = 'A' and PlaceHolderDescription = 'B')
BEGIN
print 'yes'
END
ELSE
BEGIN
PRINT 'NO'
END
