Common Requirements in SQL

-- Using Optinal Parameters In SQL Store Procedures:

declare @param as int=null
SELECT * FROM TableA where Field1 = ISNULL(@param,Field1) 

This will select only the records matching if  @param has got value, else all the records from the table.

Another Example:

CREATE PROCEDURE TestProc

(

@Param1 varchar(50) = NULL,

@Param2 varchar(50) = NULL,

@Param3 varchar(50) = NULL

)

AS

SELECT

*





FROM

TestTable





WHERE

(@Param1 IS NULL OR col1 = @Param1) AND

(@Param2 IS NULL OR col2 = @Param2) AND

(@Param3 IS NULL OR col3 = @Param3)





Results in:



exec TestProc

exec TestProc I

exec TestProc I, Love

exec TestProc I, Love, SPROCs





--Using Optional Parameters With Between Operator:





ALTER PROCEDURE [qspMKTRPTProjectAvailabilityStatus]



@COMCompanyID INT,



@FromUnit INT = NULL,



@ToUnit INT = NULL





SELECT



COMProject.COMCompanyID



FROM



COMProject



WHERE



(COMProjectUnit.COMProjectUnitID BETWEEN @FromUnit AND @ToUnit OR (@FromUnit IS NULL AND @ToUnit IS NULL))









--* Search Stored Procedures with SQL in SQL Server



sp_stored_procedures '%qspTBD%'



or



SELECT DISTINCT SO.NAME



FROM SYSOBJECTS SO (NOLOCK)



WHERE SO.TYPE = 'P'



AND SO.NAME LIKE '%scheduletemplate%'



ORDER BY SO.Name







--* Drop Procedures





IF EXISTS (select * from sys.procedures where name like 'qspProcToDrop')



DROP PROCEDURE qspProcToDrop



GO





--* DROPPING RELATIONSHIPS IN TBDBOQSubContractRateDetails TABLE



IF EXISTS(select * from sys.objects where name like 'FK_TBDBOQSubContractRateDetails_TBDBOQScheduleDetails')





--* DROP A COLUMN AFTER CHECKING FOR EXISTENCE





if Exists(select * from sys.columns where Name = N'columnName'

and Object_ID = Object_ID(N'tableName'))







--* ADD COLUMNS TO TABLE



ALTER TABLE table_name

ADD column_name datatype



--* ADD COLUMNS TO TABLE WITH DEFAULT VALUE



ALTER TABLE table_name ADD column_name datatype DEFAULT 0


--* TO CHANGE THE DATATYPE OF AN EXISTING COLUMN



ALTER TABLE TBDBOQSubContractRateDetails ALTER COLUMN Rate DECIMAL(12,3)



--Iiterate through a result set by using Transact-SQL:





SELECT @MinID = MIN(Column) FROM @Table



SELECT @MaxID = MAX(Column) FROM @Table





WHILE @MinID<=@MaxID



BEGIN





-------------------------------------



------------------------------------



------------------------------------



SET @MinID = @MinID + 1





END