help with passing variables into stored proc

[Jacqui1811]

I have an issue with the sql for a report and wondered if anbody could help.
I want to pass an entire where clause to a stored procedure to make filtering easy for the report.
So I will have @filter = " where name = 'myname' ",
exec sp_get name @filter

the stored proc reads.
sql = 'select * from table ' + filter + ' order by name'
sp_executesql @sql

the stored proc doesn't seem to like the fact that I have quote marks around 'myname' in @filter,I have tried with two single quotes as well by the way.
any hints ?
Thanks.
Jacqui.

 

[markros]

1. Are you sure you want to pass the where clause as a string? This opens you to SQL injection attacks.

You can also use

'where name = ' + Quotename(@Name)

PluralSight Learning Library

 

[simian336]

That does not even need to by dynamic...

select *
from table
where name = @filter
order by name

Simi

 

[Jacqui1811]

It is a little more comples than it would appear.
It does need to be dynamic as I might be passing other values to be in the where clause so it might be.
where name = 'myname' and title = 'mr' and area= 'home'
or it might be just where name = 'myname'.
Or there might not even be a where clause.
If you can think of a better way of implementing that I would be very happy to hear of it
Appreciate all your time
Thanks.
Jacqui

 

[markros]

Take a look at this excellent blog (though a bit long)

http://www.sommarskog.se/dyn-search-2008.html

PluralSight Learning Library

 

[simian336]

Here would be 2 other ways...

declare @test varchar(100)
set @test = 'where code = ''B'' '
select @test

declare @test2 varchar(100)
set @test2 = 'where code = ' + char(39) +'B' +char(39)
select @test2

I personally like char(39) method better. It is clearer.

Simi

 

[Jacqui1811]

Thanks markros I am looking over that link .
Regards
Jacqui.

 

[Jacqui1811]

Thanks Simian will have a try at both of those.
I will let both of you know what I have ended up implementing.
Thanks again you two.
Jacqui.