Implimenting Optional arguments in SQL Server

CREATE PROCEDURE uspGetProductList

@Colour varchar = NULL,

@ProductCategoryID int = NULL

AS

BEGIN

SET NOCOUNT ON;

SELECT

ProductID,

ProductName,

ProductCode,

ProductDescription,

Colour,

ProductCategoryID

FROM Product

WHERE (@ProductCategoryID IS NULL OR ProductCategoryID = @ProductCategoryID)

AND (@Colour IS NULL OR Colour = @Colour)

-- if any one argument get no value, the variable's value will be NULL. The NULL value

-- always make the condition true

END

To list out all the procedures which does not have EXECUTE permissions for a particular user.

This T-Sql code can help you to find out all the procedures which does not have EXECUTE permission for a specific user. You can make a slight modification to the below query to generate SQL for granting EXECUTE permission

DECLARE @UserName varchar(50)

-- Set the database user name here

SET @UserName = 'Dev'

SELECT

O.NAME AS 'ProcedureName'

FROM sys.objects as O

LEFT JOIN sys.database_permissions AS DP

ON O.Object_id = DP.major_id

LEFT JOIN sys.database_principals AS PR

ON DP.grantee_principal_id = PR.principal_id

WHERE (DP.state_desc <> 'grant' OR DP.state_desc IS NULL)

AND O.type = 'p'

AND (PR.NAME = @UserName OR PR.NAME IS NULL)