Stored Procedure parameters
Here’s some more SQL that writes SQL. One way of debugging a stored procedure is to chop off the CREATE PROCEDURE at the top and replace it with DECLARE and SET statements for the variables, then step through the stored procedure.
The following SQL gives an easy way of extracting the stored procedure parameters and creating variables based on the parameters, including the data type. Just copy-paste the output into a query window.
The variable initialization is output as template parameters so you can press Ctrl+Shift+M and easily populate the variables using the Template Parameter window.
The first version is a simple query, the second is a UDF that you can keep in the master database.
Query version:
DECLARE @StoredProcName VARCHAR(100)
SET @StoredProcName = '(Stored Proc Name)'
SELECT 'DECLARE ' + c.name +
' ' + t.name +
CASE WHEN t.name LIKE '%char%'
THEN '(' + CONVERT(varchar, c.max_length) + ')'
ELSE '' END
FROM sys.parameters c
INNER JOIN sys.types t ON c.user_type_id = t.user_type_id
WHERE c.object_id = OBJECT_ID(@StoredProcName)
UNION ALL
SELECT ' '
UNION ALL
SELECT 'SET ' + c.name +
' = <' + c.name +
',' + t.name +
CASE WHEN t.name LIKE '%char%'
THEN '(' + CONVERT(varchar, c.max_length) + ')'
ELSE '' END +
',>'
FROM sys.parameters c
INNER JOIN sys.types t ON c.user_type_id = t.user_type_id
WHERE c.object_id = OBJECT_ID(@StoredProcName)
Example
Output from SET @StoredProcName = 'HumanResources.uspUpdateEmployeePersonalInfo' in the AdventureWorks db:
DECLARE @EmployeeID int
DECLARE @NationalIDNumber nvarchar(30)
DECLARE @BirthDate datetime
DECLARE @MaritalStatus nchar(2)
DECLARE @Gender nchar(2)
SET @EmployeeID = <@EmployeeID,int,>
SET @NationalIDNumber = <@NationalIDNumber,nvarchar(30),>
SET @BirthDate = <@BirthDate,datetime,>
SET @MaritalStatus = <@MaritalStatus,nchar(2),>
SET @Gender = <@Gender,nchar(2),>
User-defined function version:
CREATE FUNCTION dbo.uFn_StoredProcVariables(@StoredProcName SYSNAME)
RETURNS NVARCHAR(MAX) AS
BEGIN
DECLARE @Declares NVARCHAR(MAX)
DECLARE @Sets NVARCHAR(MAX)
SET @Declares =
(SELECT 'DECLARE ' + c.name +
' ' + t.name +
CASE WHEN t.name LIKE '%char%'
THEN '('+CONVERT(varchar, c.max_length)+')'
ELSE '' END + CHAR(10)
FROM sys.parameters c
INNER JOIN sys.types t
ON c.user_type_id = t.user_type_id
WHERE c.object_id = OBJECT_ID(@StoredProcName)
FOR XML PATH(''))
SET @Sets =
(SELECT 'SET ' + c.name +
' = <' + c.name +
',' + t.name +
CASE WHEN t.name LIKE '%char%'
THEN '(' + CONVERT(varchar, c.max_length) + ')'
ELSE '' END + ',>' + CHAR(10)
FROM sys.parameters c
INNER JOIN sys.types t
ON c.user_type_id = t.user_type_id
WHERE c.object_id = OBJECT_ID(@StoredProcName)
FOR XML PATH(''))
RETURN @Declares
+ CHAR(10)
+ REPLACE(REPLACE(@Sets, '>', '>'), '<', '<')
END
Example
PRINT dbo.uFn_StoredProcVariables('HumanResources.uspUpdateEmployeePersonalInfo')