Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- /*
- This example of dynamic sql takes in a table name, and
- field names to be used in the where clause.
- In the field name put your column you wish to query
- then in the Param1 / 2 parameters put the expression
- for instance
- @Field1 = ID
- @Param1 ='= 1'
- OR
- @Param1 ='>1' etc
- This is obviously a very simple procedure and it can be expanded massively.
- Note when passing text you need to properly escape the ' characters
- */
- CREATE PROCEDURE spMyProcName
- (
- @Table VARCHAR(50),
- @Field1 VARCHAR(50),
- @Field2 VARCHAR(50),
- @Param1 VARCHAR(50),
- @Param2 VARCHAR(50)
- )
- AS
- BEGIN
- DECLARE @queryString NVARCHAR(MAX)
- DECLARE @selectStatement NVARCHAR(MAX)
- DECLARE @whereClause NVARCHAR(MAX)
- SET @selectStatement = 'SELECT * FROM '+@Table+' WHERE '
- SET @whereClause = RTRIM(@Field1) +' '+ RTRIM(LTRIM(@Param1))+' AND '+RTRIM(LTRIM(@Field2)) +' '+ RTRIM(LTRIM(@Param2))
- SET @queryString = @selectStatement + @whereClause
- EXECUTE sp_executesql @queryString
- END
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement