Dynamic Query
I need to write a stored proceed that has 15 parameters that returns a recordset. Any one of these parameters may contain values.
EX: @Lname = ''
@Phone = '1234567890'
@Fname = 'JANE'
@City = 'LA'
@State = ''
The main part of the proc is a dynamically created SELECT statement where the parameters are used in the WHERE clause. EX: @SQL = 'SELECT * FROM Table WHERE '. Only parameters with values must be included in the WHERE clause. And any parameter after the first one should have 'AND'. So the query should look like this:
@SQL = 'SELECT * FROM Table WHERE '
@SQL = @SQL + ' phone = ' + @phone
@SQL = @SQL + ' AND Fname = ' + @Fname
How can I figure out which is the first parameter that contains a value so not to include an AND condition and then add the AND for the rest of the parameters?
Thanks,
Ninel
|
