Use a wildcard parameter value in a SP? - 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

Use a wildcard parameter value in a SP?

Author

Topic

esloat
Starting Member

8 Posts

Posted - 2010-04-07 : 13:55:44

I have a SP with one input parameter. The paramter is specified as Float(10).

I would like to execute the SP by passing either an exact value (@parameter = 1111) or all values (@parameter > 0). There are no negative values in the parameter field.

Can the form of specifying a SP parameter include logical qualifiers if the SELECT statement in the SP is formed as:

select *
from [Table]
where parameter = @parameter

I know I can use wildcard values in string-valued parameters because I can write a WHERE clause using a LIKE qualifier:

select *
from [Table]
where stringparamter LIKE @stringparameter

But how can I accomplish this if the parameter being passed is numeric and I want to either use an exact value or a range of values?

Do I use some type of a Dynamic SQL statement?

Ideas, guidance, thoughts are greatly appreciated.

Thanks

Ed Sloat

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-07 : 13:58:15

seems like this

select * 
from [Table] 
where stringparamter LIKE CAST(@stringparameter AS varchar(20)) + '%'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-07 : 14:29:39

You can't use a wildcard with a float, they only work on strings. If you want to specify a numeric range, then you could use 2 parameters. When the 2nd param is null, then an exact match on the first, otherwise use both to indicate the beginning and end of the range. Like this:


CREATE PROCEDURE uspProc
@parameterStart FLOAT(10),
@parameterEnd FLOAT(10) = NULL
AS
SELECT * 
FROM TABLE
WHERE (@parameterEnd IS NULL AND parameter = @parameterStart)
	OR (parameter BETWEEN @parameterStart AND @parameterEnd )

Calling this sp with one param will produce an exact match. With 2 params, a range match.

There are 10 types of people in the world, those that understand binary, and those that don't.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources