Build an SQL statement in a stored procedure 4

[johnaregan]

Hi
I want to be able to build a WHERE clause of an SQL statment depending on another variable in a stored procedure. This isnt T-SQL code but just to give you some idea:

select * from aTable where
col1 = 1 and
If aValue = '5' Then
col2 = 2 and
End If
If aValue = '6' Then
col3 = 3
End If

so that if aValue = 5 then the actual SQL run against the database will be
select * from aTable where
col1 = 1 and
col2 = 2

Thanks in advance

 

[russellbcopeland]

You could use a series of If statements. Try looking in the SQL Books Online Help for the If statement. Alternatley (If you don't like If statements) you could setup your aValue to be mathematically related to some other variable that you then use in the WHERE clause.

 

[bperry]

I think this might work? (untested)

Select * from aTable
where col1 = 1 and
(aValue = '5' and col2 = 2)
OR
(aValue = '6' and col3 = 3)

 

[VogonPoet]

If you are running this in a stored procedure you could do something like this:

Declare @Where as varchar(500)

If @Value=5 then
BEGIN
Set @Where = 'col2=2'
END
Else @Value=6
BEGIN
Set @Where = 'col3=6'
END

Set @Sql='Select * from aTable
where col1 = 1 and ' + @Where
exec(@Sql)

Michael

 

[bperry]

At least one issue to be aware of when runnning dynamic sql (i.e. via the exec command) is that the security context changes from the SP itself, to the user actually running the SP.

Suppose a SP does 'Select * From aTable'. A user running that SP will not need any access rights to aTable itself; he will just need execute permission on the SP.

Now suppose instead the SP does these 2 commands:
set @Sql='Select * from aTable'
exec(@Sql)

Now, the user needs not only execute permission on the SP, BUT ALSO read permission on the table itself.

It is a common (and well-accepted) practice, when setting up security for an application, to give users no permissions at all to the tables, and instead give them execute permission on the SPs. This insures that users can only get at the tables through the SPs provided for them. But if the SPs have exec code in them, then that approach will no longer be possible.

Just something to be aware of.

rgrds, etc
bp

 

[VogonPoet]

Very good point bperry. My environment is rather unique (I'm the only user of the SQL database). Therefore, I don't usually think about security issues.

Michael

 

[mgid]

bperry,
Do you have any suggestions on getting around the EXEC permissions problem.