Pastebin launched a little side project called HostCabi.net, check it out ;-)Don't like ads? PRO users don't see any ads ;-)
Guest

Dynamic SQL Example

By: a guest on Oct 28th, 2011  |  syntax: T-SQL  |  size: 0.98 KB  |  hits: 34  |  expires: Never
download  |  raw  |  embed  |  report abuse  |  print
Text below is selected. Please press Ctrl+C to copy to your clipboard. (⌘+C on Mac)
  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.