Dynamic SQL in stored procs
Posted on 2001-06-14
I'm writing a stored proc to return a resultset on a Sybase DB. The complication is that one of
the parameters into the proc is a value which indicates which field on a table a second parameter belongs
E.G. Say MyTable has a number of fields, 3 of which are identifiers for a record, called Code1,
Code2, Code3. My proc will receive a first parameter with a value of 'LOG1' say. This is the value of
the key. A second parameterrepresents which Code type it belongs to, for example 'Code2'. This would
then mean a WHERE condition can be added to the SELECT statement saying
WHERE MyTable.Code2 = @Parameter1
WHERE MyTable.Code2 = 'LOG1'
How would you implement something like this? Would you dynamically generate the SQL, or write a SELECT
for each possibility of 'Code' field and seperate them with an IF condition on @Parameter2. Or can you
embed an IF statement into a SELECT statement to say :
IF @parameter2 = 'Code1'
WHERE MyTable.Code1 = @parameter1
ELSEIF @parameter2 = 'Code2'
WHERE MyTable.Code2 = @parameter1
Help much appreciated...