code_junkie

Dynamic Stored Procedure in Sql Server 2005

Nov 13th, 2011
130
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 0.74 KB | None | 0 0
  1. CREATE TABLE #inputParams (ParamName VARCHAR(20), ParamValue VARCHAR(20))
  2. INSERT INTO #inputParams (ParamName, ParamValue) VALUES ('First Name', 'John')
  3. INSERT INTO #inputParams (ParamName, ParamValue) VALUES ('Last Name', 'Doe')
  4. EXEC dbo.USP_MyStoredProcedure
  5.  
  6. CREATE PROCEDURE dbo.USP_MyStoredProcedure AS
  7. DECLARE @FirstName VARCHAR(20)
  8. SET @FirstName = SELECT ParamValue FROM #inputParams WHERE ParamName = 'First Name'
  9. GO
  10.  
  11. CREATE PROCEDURE MyDynamicStoredProc
  12. @ParameterXML XML
  13. AS
  14.  
  15. SET NOCOUNT ON
  16.  
  17. DECLARE @Parameter1 NVARCHAR(50)
  18. DECLARE @Parameter2 INT
  19.  
  20. SET @Parameter1 = @ParameterXML.value('/Root/Parameters/StringParameter/@Value','nvarchar(50)')
  21. SET @Parameter2 = @ParameterXML.value('/Root/Parameters/IntegerParameter/@Value','int')
  22. ...
Add Comment
Please, Sign In to add comment