Stored procedures, dynamic SQL, and cursors solve different problems. Good T-SQL design starts with ordinary set-based queries and introduces these features only when they are warranted.
Stored procedures
A stored procedure wraps reusable database logic, can accept parameters, and can return results. Its permissions and deployment lifecycle should be managed like application code.
Parameterized dynamic SQL
When the statement structure varies at runtime, sp_executesql can execute a statement with strongly typed values. Never concatenate untrusted values into a WHERE clause.
T-SQL example
CREATE OR ALTER PROCEDURE dbo.FindOrders
@CustomerID int
AS
BEGIN
SET NOCOUNT ON;
DECLARE @sql nvarchar(max) =
N'SELECT OrderID, OrderDate FROM dbo.Orders WHERE CustomerID = @cid';
EXEC sys.sp_executesql @sql, N'@cid int', @cid = @CustomerID;
END;Identifiers and cursors
Parameter placeholders cannot substitute object names such as table or column identifiers. Validate against an allowlist and quote identifiers with QUOTENAME. Cursors process rows one at a time and can be useful for truly sequential operations; set-based UPDATE, INSERT, and window functions are usually preferable.
Injection prevention
Grant the database account minimal rights, validate dynamic object names, parameterize values, and do not treat simple quote escaping as a complete defense. Test failure paths as well as the successful query.
About this article: This AI-generated educational rewrite was prepared from an existing Open Education article, with new explanations and illustrative examples where helpful. Read the earlier article (post 75975). The new text may be freely copied, edited, and reused with or without attribution to this site. Third-party material, brand names, and linked resources retain their own rights. AI can make mistakes: verify syntax, version support, security practices, and outcomes against official documentation before use, especially in production.
