Saturday, February 28, 2009
You can add optional parameters in stored procedures by setting a default value for each parameter that you want to make optional. The default value is typically NULL, but it's not necessary.
Example:
CREATE PROCEDURE dbo.mySP
@firstParam VARCHAR(32) = NULL,
@secondParam INT = NULL
AS
BEGIN
SET NOCOUNT ON
SELECT *
FROM [TableName]
WHERE
Param1 = @firstParam
AND Param2 = @secondParam
END
GO
--
EXEC dbo.mySP @firstParam ='bar', @secondParam =4
EXEC dbo.mySP @firstParam ='bar'
EXEC dbo.mySP @secondParam =4
EXEC dbo.mySP 'bar',4
EXEC dbo.mySP 'bar'
EXEC dbo.mySP
GO
DROP PROCEDURE dbo.mySP
GO
HAS_DBACCESS returns information about whether the user has access to the specified database.
Example:
SELECT HAS_DBACCESS('Northwind');
returns
1 if the user has access to the database
0 if the user does not have access to the database
NULL if the database does not exist
Find all databases that the current user has access to
SELECT [Name] as DatabaseName
FROM master.dbo.sysdatabases
WHERE
ISNULL(HAS_DBACCESS ([Name]),0)=1
ORDER BY [Name]