passing where clause to stored procedure.

Question

Post reply

passing where clause to stored procedure.

barua.99

SSC Enthusiast

Points: 130
More actions
July 25, 2012 at 12:26 am

#259790

sir, i try like below
create procedure abc
@sql varchar(100)
as
begin
select * from [accomodation] where @sql
end
it gives error
An expression of non-boolean type specified in a context where a condition is expected, near 'end'.
plz help me.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Karthiart Ten Centuries Points: 1070 More actions · Answer 1

For which column you need to check that variable?

Like, select * from [accomodation] where ? = @sql

Also specify, what is the input for that variable @sql.

Regards,
Karthik.
SQL Developer.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Seriously, don't do this. That's the best thing I can recommend. This kind of design is incredibly problematic.

If you absolutely must, you need to use dynamic SQL and protect against SQL Injection.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

barua.99 SSC Enthusiast Points: 130 More actions · Answer 3

i solve it

http://brentedwards.net/2009/01/05/dynamic-where-clause-with-sql-server-stored-procedure/

is fantastic.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

barua.99 (7/25/2012)
i solve it
http://brentedwards.net/2009/01/05/dynamic-where-clause-with-sql-server-stored-procedure/
is fantastic.

Sure, as long as good performance is not a requirement. Those catch-all style queries are horridly slow in many cases.

http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass