SQL Query for an asp page

[Hinden]

Hi all,

First off let me apologise if this is the wrong forum for this post, but I couldn't see an SQL forum and as its for an ASP page...

I'm quite new to T-SQL and I'm writing a dynamic stored procedure. Bascially is the following code possible?

CREATE PROCEDURE sp_Anything

@BookingKey Int,
@functionFlag Int

AS

DECLARE

@theKey VARCHAR(20)

SET @theKey = "columnName"

SELECT blah blah
FROM blah blah

WHERE @theKey = @BookingKey

So basically I dont want to fix the column name in the Where clause. I'm aiming to put in an IF clause to seach for a user inputted variable (@functionFlag) which will affect which column is called in the WHERE clause. Can it be done?

Thanks in advance,

[phpnovice]

I'm not familiar with T-SQL. However, the may be a SELECT-CASE construct which can help you there.

[CardboardHammer]

Yes, you can use EXECUTE on an nvarchar string. However, be VERY careful if you use user supplied character data as part of the string you EXECUTE, as it is the one thing that makes stored procedures vulnerable to SQL injection.

FYI, in your example code, there is no need at all to use EXECUTE.

EDIT: In most any case, there would be less performance penalty in rewriting the SELECT/FROM portion in every IF block than in using EXECUTE. If the SELECT / FROM portion is huge or might need alteration later, you can create a view that encapsulates the SELECT / FROM and then do all the SELECTs FROM the view instead. If the underlying SELECT / FROM needs to be changed, you just change the view, and the rest takes care of itself (an exception being, if you delete fields from the view, you'll need to delete any IF blocks containing a query that references a removed field.)

[Hinden]

Thanks for the replies, as ever this forum has helped a lot!

I have used the Execute for the time being, but will look at the other approaches if I get the time!

[CardboardHammer]

You're welcome :cool: