Sql Server Tips

Sql server

1.Parameterise TOP

DECLARE @top10 INT

SET @top10 = NULL

SELECT TOP ( CASE
WHEN @top10 IS NOT NULL THEN @top10
ELSE (SELECT COUNT(* )
FROM ["Table Name"])
END ) *
FROM ["Table Name"]

2.Converting static sql to dynamic sql


CREATE PROCEDURE [dbo].prcGetPersonDetails
@prmCityName VARCHAR(12) = NULL
AS
IF @prmCityName = ''
SET @prmCityName = NULL

BEGIN
SELECT [AddressID],
[City],
[PostalCode],
[rowguid],
[ModifiedDate]
FROM [AdventureWorks].[Person].[Address]
WHERE 1 = 1
AND ((@prmCityName IS NULL)
OR (@prmCityName IS NOT NULL
AND [AdventureWorks].[Person].[Address].[City] = @prmCityName))
END