SHARE
TWEET

Dynamic SQL Example

a guest Oct 28th, 2011 50 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
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
Top