Dynamic Stored Procedure in Sql Server 2005

Question

Is it possible to write a stored procedure with dynamic parameters in sql server 2005

Answer 1

No, both the count and parameter types are fixed as part of the stored procedure declaration.

Answer 2

Technically no, but there's a workaround: temp tables. Create a temp table, then set up your stored procedure to read its configuration from the temp table.

Here's an example of how to call it:

CREATE TABLE #inputParams (ParamName VARCHAR(20), ParamValue VARCHAR(20))
INSERT INTO #inputParams (ParamName, ParamValue) VALUES ('First Name', 'John')
INSERT INTO #inputParams (ParamName, ParamValue) VALUES ('Last Name', 'Doe')
EXEC dbo.USP_MyStoredProcedure

and then inside your stored procedure, you could retrieve whatever parameters you needed by checking for the existence of that temp table, and querying the parameters from it like this:

CREATE PROCEDURE dbo.USP_MyStoredProcedure AS
DECLARE @FirstName VARCHAR(20)
SET @FirstName = SELECT ParamValue FROM #inputParams WHERE ParamName = 'First Name'
GO

That way, your stored procedure's number of parameters never changes, which makes it easier for some dev applications to call it over time, but this is reeeeeally dangerous. From the outside of the stored proc, you never have any idea what version of the stored proc you're calling, and whether or not it's going to do anything with the parameters you're sending in.

What you might think about instead is abstracting away your stored proc inside another stored proc as a wrapper.

Answer 3

If there's a discrete set of parameters you could pass them into the sproc as XML. Then you can shred the XML into variables inside the body of the sproc e.g.

CREATE PROCEDURE MyDynamicStoredProc  
@ParameterXML XML
AS

SET NOCOUNT ON

DECLARE @Parameter1 NVARCHAR(50)
DECLARE @Parameter2 INT

SET @Parameter1 = @ParameterXML.value('/Root/Parameters/StringParameter/@Value','nvarchar(50)')
SET @Parameter2 = @ParameterXML.value('/Root/Parameters/IntegerParameter/@Value','int')
...

NB: My XQuery-fu is weak, don't rely on this example!