Guest User

Dynamic SQL Example

a guest
Oct 28th, 2011
87
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. /*
  2. This example of dynamic sql takes in a table name, and
  3. field names to be used in the where clause.
  4. In the field name put your column you wish to query
  5. then in the Param1 / 2 parameters put the expression
  6. for instance
  7.  
  8. @Field1 = ID
  9. @Param1 ='= 1'
  10. OR
  11. @Param1 ='>1' etc
  12.  
  13. This is obviously a very simple procedure and it can be expanded massively.
  14.  
  15. Note when passing text you need to properly escape the ' characters
  16. */
  17. CREATE PROCEDURE spMyProcName
  18. (
  19.     @Table VARCHAR(50),
  20.     @Field1 VARCHAR(50),
  21.     @Field2 VARCHAR(50),
  22.     @Param1 VARCHAR(50),
  23.     @Param2 VARCHAR(50)
  24. )
  25. AS
  26. BEGIN
  27.  
  28.     DECLARE @queryString NVARCHAR(MAX)
  29.     DECLARE @selectStatement NVARCHAR(MAX)
  30.     DECLARE @whereClause NVARCHAR(MAX)
  31.    
  32.     SET @selectStatement = 'SELECT * FROM '+@Table+' WHERE '
  33.    
  34.     SET @whereClause = RTRIM(@Field1) +' '+ RTRIM(LTRIM(@Param1))+' AND '+RTRIM(LTRIM(@Field2)) +' '+ RTRIM(LTRIM(@Param2))
  35.    
  36.     SET @queryString = @selectStatement + @whereClause
  37.    
  38.     EXECUTE sp_executesql @queryString
  39. END
  40.  
RAW Paste Data