Logic within Stored Proedures?????? 1

[pwel]

Can I dynamically build a WHERE clause within a store procedure, depending the values of certain variables passed into it?

The ideal scenario would be a series of if...else statements to determine the condition of the WHERE clause.

 

[Tim1]

Hiya,

Yes, you can, but only if you use dynamic SQL. eg.

DECLARE @sql VARCHAR(255)

IF something = something_else
SELECT @sql = 'WHERE x = y'
ELSE
SELECT @sql = 'WHERE y = z'

EXEC ('UPDATE table ' +
'SET field = field ' +
'field = field ' +
@sql)

HTH

Tim

 

[MalcolmW]

You could use a CASE statement, which has the advantage of being processed more efficiently. Malcolm Wynden
Authorized Crystal Engineer

http://www.wynden.net

malcolm@wynden.net

 

[pwel]

Thanks Tim,

That is exactly what I wanted to do.........

Paul W.

 

[pwel]

I am having problems using an (int) value in the WHERE string, as the WHERE string is defined as a varchar, I keep getting an error when SQL Server tries to convert it to an int.

Is there command to parse that part of the string as an int??

 

[Tim1]

Hiya,

Yes, you need to use the convert function:

eg:

@sql = @sql + CONVERT(INT, variable)

Tim