how use parameters in where clause? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

how use parameters in where clause?

Author

Topic

allan8964
Posting Yak Master

249 Posts

Posted - 2011-12-14 : 15:36:29

Hi people,

I tried to use parameter in where clause like this:

declare @param1 varchar(10), @param2 varchar(10);
set @param1 = 'ABC';
set @param2 = 'CX03';
where Scope between @param1 and @param2

it works ok, then I failed on trying

declare @param varchar(50);
set @param = ' Scope between ABC and CX03'
where @param

The error is "An expression of non-boolean type specified in a context where a condition is expected" Plz advise. Thanks in advance.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-12-14 : 15:39:24

Why do you need to do this via a parameter? What is wrong with WHERE SCOPE between @param1 and @param2?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-12-14 : 16:44:38

What you have done is basicaly:

SELECT *
FROM TableName
WHERE Scope between ABC and CX03

What is the datatype of ABC? I assume it's a string. And how do you denote a string in sql? With single quotes. So, in order to do what you are trying to do you would need to adjust your string so that it generates something like:

SELECT *
FROM TableName
WHERE Scope between 'ABC' and 'CX03'

Even that won't work, because SQL will not evaluate a variable in a WHERE clause as an expression, hence the error you are receiving.

But the real question is, WHAT are you trying to accomplish? Perhaps using the sp_executesql stored procedure and passing the parameters would be a better choice.?.?

There is an article right off the SqlTeam home page that talks about using the sp_executesql stored procedure:
http://www.sqlteam.com/article/using-dynamic-sql-in-stored-procedures

allan8964
Posting Yak Master

249 Posts

Posted - 2011-12-14 : 23:14:39

Thanks Lamprey. I tried to bring some params to WHERE clause from a stored procedure input. Here is the code:

Create sp_GetValue
@param1 varchar(10),
@param2 varchar(10)
AS
SET NOCOUNT ON;

Select * from table1
where Type = @param1

---IF @param2 = 'AAA'
AND Code = '111'
---If @param2 = 'BBB'
AND Code = '222'
...
where clause needs 2 conditions and the 2nd condition depends on the string @param2 input. I am not sure how to do that ... possible or not.
Thanks again.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-12-14 : 23:59:17

Create sp_GetValue
@param1 varchar(10),
@param2 varchar(10)
AS
SET NOCOUNT ON;

Select * from table1
where Type = @param1 and ((@param2 = 'AAA and code = '111) or (@param2 = 'BBB' and code = '222'))

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

allan8964
Posting Yak Master

249 Posts

Posted - 2011-12-15 : 22:30:30

Thanks. This works but I have more than 10 arguments like this. Can I use case when ... for this scenario?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-12-16 : 00:35:44

Yes you can use a case expression in the where clause.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources