How to convert varchar to = , > and < operators

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

SQL Server 2005 Forums

Transact-SQL (2005)

How to convert varchar to = , > and < operators

Author

Topic

subhankar02dey
Starting Member

4 Posts

Posted - 2010-09-29 : 23:13:01

I have a procedure like

create procedure emp_fill(@operator1 varchar, @operator2 varchar, @emp_salary int, @emp_comm int)
as
begin
select * from employees where salary [>|=|<] @emp_salary and commission_pct [>|=|<] @emp_comm;
end;

Now the problem is I am sending @operator1 and @operator2 parameter in this procedure either '>' or '=' or '<'
But I cannot write select * from employees where salary @operator1 @emp_salary and commission_pct @operator2 @emp_comm;

What I have to do to convert @operator1 and @operator2 which are varchar type, to change them as [>|=|<] operators and use them in the procedure.

pls help

lazycoder
Starting Member

12 Posts

Posted - 2010-09-30 : 04:49:02

You need to cast or convert those varchars to data type of those columns you compare. Eg.: cast(@emp_salary as int)

-----------------
http://it.expertmonster.com/

Ifor
Aged Yak Warrior

700 Posts

Posted - 2010-09-30 : 05:01:04

You would do better to pass a range for salary and commision.
ie If looking for salaries less than 50000, pass a range of 0.00 to 49999.99


SET ANSI_NULLS, QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE dbo.emp_fill
(
	@emp_salary_from money
	,@emp_salary_to money
	,@emp_comm_from int
	,@emp_comm_to int
)
AS

SET NOCOUNT ON

SELECT * -- put in column list here
FROM dbo.employees
WHERE salary BETWEEN @emp_salary_from AND @emp_salary_to
	AND commission_pct BETWEEN @emp_comm_from AND @emp_comm_to
GO

subhankar02dey
Starting Member

4 Posts

Posted - 2010-09-30 : 13:37:38

I cant a range for salary and commision.
Because this procedure is being called from a front end page where there is a dropdown which sends either > or < or = And there is textbox which sends the salary to the database procedure.
I cant change the design

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-10-01 : 06:44:10

You need to use a dynamic sql
www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail

Ifor
Aged Yak Warrior

700 Posts

Posted - 2010-10-01 : 07:10:35

quote:
Originally posted by subhankar02dey

I cant a range for salary and commision.
Because this procedure is being called from a front end page where there is a dropdown which sends either > or < or = And there is textbox which sends the salary to the database procedure.
I cant change the design

Umm....
Then your options are:

1. do something inefficient. eg


SET ANSI_NULLS, QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE dbo.emp_fill
(
	@operator1 char(1)
	,@operator2 char(1)
	,@emp_salary int
	,@emp_comm int
)
AS

SET NOCOUNT ON

SELECT * -- put in column list here
FROM dbo.employees
WHERE salary BETWEEN
	CASE
		WHEN @operator1 = '<' THEN 0
		WHEN @operator1 = '=' THEN @emp_salary
		WHEN @operator1 = '>' THEN @emp_salary + 1
		ELSE -1
	END
	AND
	CASE
		WHEN @operator1 = '<' THEN @emp_salary - 1
		WHEN @operator1 = '=' THEN @emp_salary
		WHEN @operator1 = '>' THEN 2147483647
		ELSE -1
	END
	AND commission_pct BETWEEN
	CASE
		WHEN @operator2 = '<' THEN 0
		WHEN @operator2 = '=' THEN @emp_comm
		WHEN @operator2 = '>' THEN @emp_comm + 1
		ELSE -1
	END
	AND
	CASE
		WHEN @operator2 = '<' THEN @emp_comm - 1
		WHEN @operator2 = '=' THEN @emp_comm
		WHEN @operator2 = '>' THEN 2147483647
		ELSE -1
	END
GO

2. use dynamic SQL with the nasty security implications. eg


SET ANSI_NULLS, QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE dbo.emp_fill
(
	@operator1 char(1)
	,@operator2 char(1)
	,@emp_salary int
	,@emp_comm int
)
AS

SET NOCOUNT ON

DECLARE @SQL nvarchar(4000)
SET @SQL = ''

SELECT @SQL = @SQL +
	'SELECT * FROM employees WHERE salary '
	+ @operator1
	+ ' @emp_salary AND commission_pct '
	+ @operator2
	+ ' @emp_comm'
WHERE @operator1 IN ('<','=','>')
	AND @operator2 IN ('<','=','>')

EXEC sp_executesql @SQL, N'@emp_salary int, @emp_comm int', @emp_salary, @emp_comm
GO

3. hardcode all the combinations. eg


SET ANSI_NULLS, QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE dbo.emp_fill1(@emp_salary int, @emp_comm int)
AS SELECT * FROM employees WHERE salary < @emp_salary AND commission_pct < @emp_comm
GO
CREATE PROCEDURE dbo.emp_fill2(@emp_salary int, @emp_comm int)
AS SELECT * FROM employees WHERE salary < @emp_salary AND commission_pct = @emp_comm
GO
CREATE PROCEDURE dbo.emp_fill3(@emp_salary int, @emp_comm int)
AS SELECT * FROM employees WHERE salary < @emp_salary AND commission_pct > @emp_comm
GO
CREATE PROCEDURE dbo.emp_fill4(@emp_salary int, @emp_comm int)
AS SELECT * FROM employees WHERE salary = @emp_salary AND commission_pct < @emp_comm
GO
CREATE PROCEDURE dbo.emp_fill5(@emp_salary int, @emp_comm int)
AS SELECT * FROM employees WHERE salary = @emp_salary AND commission_pct = @emp_comm
GO
CREATE PROCEDURE dbo.emp_fill6(@emp_salary int, @emp_comm int)
AS SELECT * FROM employees WHERE salary = @emp_salary AND commission_pct > @emp_comm
GO
CREATE PROCEDURE dbo.emp_fill7(@emp_salary int, @emp_comm int)
AS SELECT * FROM employees WHERE salary > @emp_salary AND commission_pct < @emp_comm
GO
CREATE PROCEDURE dbo.emp_fill8(@emp_salary int, @emp_comm int)
AS SELECT * FROM employees WHERE salary > @emp_salary AND commission_pct = @emp_comm
GO
CREATE PROCEDURE dbo.emp_fill9(@emp_salary int, @emp_comm int)
AS SELECT * FROM employees WHERE salary > @emp_salary AND commission_pct > @emp_comm
GO
CREATE PROCEDURE dbo.emp_fill
(
	@operator1 char(1)
	,@operator2 char(1)
	,@emp_salary int
	,@emp_comm int
)
AS

SET NOCOUNT ON

IF @operator1 = '<' AND @operator2 = '<' EXEC dbo.emp_fill1 @emp_salary, @emp_comm
ELSE IF @operator1 = '<' AND @operator2 = '=' EXEC dbo.emp_fill2 @emp_salary, @emp_comm
ELSE IF @operator1 = '<' AND @operator2 = '>' EXEC dbo.emp_fill3 @emp_salary, @emp_comm
ELSE IF @operator1 = '=' AND @operator2 = '<' EXEC dbo.emp_fill4 @emp_salary, @emp_comm
ELSE IF @operator1 = '=' AND @operator2 = '=' EXEC dbo.emp_fill5 @emp_salary, @emp_comm
ELSE IF @operator1 = '=' AND @operator2 = '>' EXEC dbo.emp_fill6 @emp_salary, @emp_comm
ELSE IF @operator1 = '>' AND @operator2 = '<' EXEC dbo.emp_fill7 @emp_salary, @emp_comm
ELSE IF @operator1 = '>' AND @operator2 = '=' EXEC dbo.emp_fill8 @emp_salary, @emp_comm
ELSE IF @operator1 = '>' AND @operator2 = '>' EXEC dbo.emp_fill9 @emp_salary, @emp_comm
GO

Personally, I would ensure the web page was altered!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources