A reliable way to verify T-SQL stored procedures
You can choose different ways. First of all SQL SERVER 2008 supports dependencies which exist in DB inclusive dependencies of STORED PROCEDURE (see http://msdn.microsoft.com/en-us/library/bb677214%28v=SQL.100%29.aspx, http://msdn.microsoft.com/en-us/library/ms345449.aspx and http://msdn.microsoft.com/en-us/library/cc879246.aspx). You can use sys.sql_expression_dependencies and sys.dm_sql_referenced_entities to see and verify there.
But the most simple way to do verification of all STORED PROCEDURE is following:
- export all STORED PROCEDURE
- drop old existing STORED PROCEDURE
- import just exported STORED PROCEDURE.
If you upgrade DB the existing Stored Procedure will be not verified, but if you create a new one, the procedure will be verified. So after exporting and exporting of all Stored Procedure you receive all existing error reported.
You can also see and export the code of a Stored Procedure with a code like following
SELECT definition
FROM sys.sql_modules
WHERE object_id = (OBJECT_ID(N'spMyStoredProcedure'))
UPDATED: To see objects (like tables and views) referenced by Stored Procedure spMyStoredProcedure you can use following:
SELECT OBJECT_NAME(referencing_id) AS referencing_entity_name
,referenced_server_name AS server_name
,referenced_database_name AS database_name
,referenced_schema_name AS schema_name
, referenced_entity_name
FROM sys.sql_expression_dependencies
WHERE referencing_id = OBJECT_ID(N'spMyStoredProcedure');
UPDATED 2: In the comment to my answer Martin Smith suggested to use sys.sp_refreshsqlmodule
instead of recreating a Stored Procedure. So with the code
SELECT 'EXEC sys.sp_refreshsqlmodule ''' + OBJECT_SCHEMA_NAME(object_id) +
'.' + name + '''' FROM sys.objects WHERE type in (N'P', N'PC')
one receive a script, which can be used for verifying of Stored Procedure dependencies. The output will look like following (example with AdventureWorks2008):
EXEC sys.sp_refreshsqlmodule 'dbo.uspGetManagerEmployees'
EXEC sys.sp_refreshsqlmodule 'dbo.uspGetWhereUsedProductID'
EXEC sys.sp_refreshsqlmodule 'dbo.uspPrintError'
EXEC sys.sp_refreshsqlmodule 'HumanResources.uspUpdateEmployeeHireInfo'
EXEC sys.sp_refreshsqlmodule 'dbo.uspLogError'
EXEC sys.sp_refreshsqlmodule 'HumanResources.uspUpdateEmployeeLogin'
EXEC sys.sp_refreshsqlmodule 'HumanResources.uspUpdateEmployeePersonalInfo'
EXEC sys.sp_refreshsqlmodule 'dbo.uspSearchCandidateResumes'
EXEC sys.sp_refreshsqlmodule 'dbo.uspGetBillOfMaterials'
EXEC sys.sp_refreshsqlmodule 'dbo.uspGetEmployeeManagers'
What is the best way to test a stored procedure?
A colleague swears by the TSQLUnit testing framework. May be worth a look for your needs.
The right way to do stored procedure parameter validation
I don't think that there is a single "right" way to do this.
My own preference would be similar to your second example, but with a separate validation step for each parameter and more explicit error messages.
As you say, it's a bit cumbersome and ugly, but the intent of the code is obvious to anyone reading it, and it gets the job done.
IF (ISNULL(@fooInt, 0) = 0)
BEGIN
RAISERROR('Invalid parameter: @fooInt cannot be NULL or zero', 18, 0)
RETURN
END
IF (ISNULL(@fooString, '') = '')
BEGIN
RAISERROR('Invalid parameter: @fooString cannot be NULL or empty', 18, 0)
RETURN
END
Easiest way to validate user in stored procedure?
Without more information the best I can offer for the moment is:
CREATE STORED PROCEDURE CheckPassword
@username VARCHAR(20),
@password varchar(20)
AS
BEGIN
SET NOCOUNT ON
IF EXISTS(SELECT * FROM usertable WHERE username = @username AND password = @password)
SELECT 'true' AS UserExists
ELSE
SELECT 'false' AS UserExists
END
Query amended based on your response - this will return the string 'true' or 'false' you could replace them with bit values 1 and 0 respectively if you prefer.
Stored procedure best practice? Should they check if foreign keys exist before inserting?
If the tables are under your control, there is no reason to perform an extra check. Just assume they are set up correctly, and let SQL handle any error. Constantly checking that you have indeed done what you intended to do is overly defensive programming that adds unnecessary complexity to your code.
For example, you wouldn't write code like this:
i = 1;
if (i != 1)
{
print "Error: i is not 1!";
}
And I view this situation as similar.
If the tables are not under your control it may be useful to handle the error gracefully. For example, if this procedure can run on an arbitrary set of tables created by the user, or if it will be distributed to external users who are required to set up the tables in their own database, you may want to add some custom error handling. The purpose of this would be to give the user a clearer description of what went wrong.
Check if stored procedure is running
You might query sys.dm_exec_requests
which will provide sesion_ID, waittime and futher rows of interest and CROSS APPLY sys.dm_exec_sql_text
filtering your query with the SQL for your procedure.
Select * from
(
SELECT * FROM sys.dm_exec_requests
where sql_handle is not null
) a
CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) t
where t.text like 'CREATE PROCEDURE dbo.sp_sleeping_beauty%'
How to check if a stored procedure exists before creating it
You can run procedural code anywhere you are able to run a query.
Just copy everything after AS
:
BEGIN
DECLARE @myvar INT
SELECT *
FROM mytable
WHERE @myvar ...
END
This code does exactly same things a stored proc would do, but is not stored on the database side.
That's much like what is called anonymous procedure in PL/SQL
.
Update:
Your question title is a little bit confusing.
If you only need to create a procedure if it not exists, then your code is just fine.
Here's what SSMS
outputs in the create script:
IF EXISTS ( SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'myproc')
AND type IN ( N'P', N'PC' ) )
DROP …
CREATE …
Update:
Example of how to do it when including the schema:
IF EXISTS ( SELECT *
FROM sysobjects
WHERE id = object_id(N'[dbo].[MyProc]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1 )
BEGIN
DROP PROCEDURE [dbo].[MyProc]
END
In the example above, dbo is the schema.
Update:
In SQL Server 2016+, you can just do
CREATE OR ALTER PROCEDURE dbo.MyProc
Related Topics
SQL Query to Join Two Tables Based Off Closest Timestamp
How to Make Comment Reply Query in MySQL
How to Find SQL Language Specification
Create SQL Server Table Based on a User Defined Type
Detect Duplicate Items in Recursive Cte
"Ambiguous Column Name" Error on One Particular Server
T-SQL Get Number of Working Days Between 2 Dates
Generate a Sequential Number (Per Group) When Adding a Row to an Access Table
Always Show Decimal Places in SQL
How to Execute a Stored Procedure Over a Set Without Using a Cursor
How to Change the Name of the Athena Results Stored in S3
Xml Query() Works, Value() Requires Singleton Found Xdt:Untypedatomic
Recursive Query for Bill of Materials
Add a Column That Represents a Concatenation of Two Other Varchar Columns
Convert SQL Server Date to Mm-Yyyy
Range Wildcard Pattern Matching Behaviour with Case-Sensitive Collations
Getting a Dynamically-Generated Pivot-Table into a Temp Table