Comparison Operator as a Parameter in Stored Proc

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

Comparison Operator as a Parameter in Stored Proc

Author

Topic

sindhu122
Starting Member

32 Posts

Posted - 2011-01-24 : 10:58:12

Hi,
I have to compare values entered in a web page. I am storing the Comparison Operators in a variables '@exp1' and '@exp2' and have to use these in the Stored Procedure to compare the values. I have to do comparison for two values/fields.
So I have two parameters.
The only way I see is writing if conditions for all the possibilities, but that will a very big stored procedure. So can someone help me in this, where I can use the parameter in my query.
@exp1 can be >,<.>=,<=,=,between
@exp2 can be >,<.>=,<=,=,between
So these are the two parameters I want to pass.

It is very urgent.
Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-24 : 11:03:06

you need to use a series of CASE WHEN statements in your WHERE condition or use dynamic sql for this requirement. I dont think passing operators to procedure is a good idea at all...Can you tell why you're doing like this?

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

sindhu122
Starting Member

32 Posts

Posted - 2011-01-24 : 11:45:20

I have to implement a search feature. In the search feature, you have two fields, amount and quantity.
So if you have to do searches like
amount >20
quantity <5
I am storing that operator in a parameter.
I did not find nay other way to do that, because I have to get the result from a SQL Table.
I tried using CASE WHEN, but did not succeed.
Can you please tell me how to do this or is there any other to implement this?

Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-24 : 11:50:41

you've to use something like

EXEC('SELECT FROM table WHERE ' + @firstcol + @exp1 + CAST(@firstvalue AS varchar(10))+ ' AND ' + @secondcol + @exp2 + CAST(@secondvalue AS varchar(10)))

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

sindhu122
Starting Member

32 Posts

Posted - 2011-01-24 : 11:56:29

How do I write if this is the case.
The value of amount in the table is in the column 'td_amount'
The operator is in the parameter '@exp'
and the Value which we enter to compare is in the parameter '@value'.
How do I use these in the query. Can you please give me the code.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-24 : 12:03:14

so the column is fixed?

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

sindhu122
Starting Member

32 Posts

Posted - 2011-01-24 : 12:06:28

Yes..the column 'td_amount' is fixed. we have to get a result by comparing that column with the value we give.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-24 : 12:09:47

then you can use like


....
WHERE (td_amount > @value AND @exp='>')
OR (td_amount < @value AND @exp='<')
OR (td_amount >= @value AND @exp='>=')
...

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

sindhu122
Starting Member

32 Posts

Posted - 2011-01-24 : 12:16:01

It worked.
Thank you sooooooooooo much.
I also have another question.
How can we pass the table name in the stored procedure as a parameter?
Out data is stored per month basis, like one table for each month.
So in the search page, It has to select the tables depending on the start and end dates.
Can we choose multiple tables at a time, if we select dates from Jan-Dec.?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-24 : 12:20:14

its not again a good approach. why should you store it in separate tables? isn't it just a matter of keeping in same table and having a date column. If at all table grows rapidly, you can apply partitioning on table based on datefield for speeding up queries.

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

sindhu122
Starting Member

32 Posts

Posted - 2011-01-24 : 12:31:46

I do not store data like that. I got the data in that pattern.
As SQL partitioning license costs a lot, my company decided to store data per month basis. Now I have to implement a search feature depening on this. Is there a way where can run a SQL Procedure on multiple tables depending on the Start Date and End Date?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-24 : 12:34:59

but its a real pain. after each table creation you've to change the procedure accordingly or else you might have to go for dynamic sql. If using latter you should be careful against SQL injection attacks.

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

sindhu122
Starting Member

32 Posts

Posted - 2011-01-24 : 12:45:46

Yes, but I am supposed to do only in the SQL Server without Dynamic SQL.
So is there no way to do that?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-24 : 12:49:32

there is one way. but code will having serious maintainenace issues . the query should be like


SELECT columns..
FROM tablejan
WHERE 1 BETWEEN MONTH(@startdate) AND   MONTH(@enddate)
 UNION ALL
SELECT columns..
FROM tablefeb
WHERE 2 BETWEEN MONTH(@startdate) AND   MONTH(@enddate)

.....

UNION ALL
SELECT columns..
FROM tabledec
WHERE 12 BETWEEN MONTH(@startdate) AND   MONTH(@enddate)

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

sindhu122
Starting Member

32 Posts

Posted - 2011-01-24 : 13:10:09

Alright. Thanks.
Will try and let you know.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-24 : 13:13:48

ok... good luck

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

sindhu122
Starting Member

32 Posts

Posted - 2011-01-26 : 09:51:54

Hi I am still working on passing the table name as a parameter. After passing that, I have to do a Union on them
Bu I am not knowing how to pass it as a parameter. My Stored Proc has joins and a lot of parameters. This is my Stored Proc:
USE [StoreKeeperTranDB]
GO
/****** Object: StoredProcedure [dbo].[SP_Investigations] Script Date: 01/21/2011 14:04:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- Batch submitted through debugger: SQLQuery2.sql|7|0|C:\Users\Sindhu\Documents\SQL Server Management Studio\Projects\SQLQuery2.sql

-- ==========================================================================================
-- Entity Name: SP_Investigations
-- Author: Sindhura

-- ==========================================================================================

ALTER PROCEDURE [dbo].[SP_Investigations]
@StoreName nvarchar(50),
@Criteria1 nvarchar(155),
@Criteria2 nvarchar(155),
@fromDate DateTime,
@toDate DateTime,
@reg nvarchar(5),
@cashier nvarchar(50),
@type nvarchar(50),
@evenodd int,
@exp nvarchar(10),
@value nvarchar(50),
@exp1 nvarchar(10),
@value1 nvarchar(50),
@value2 nvarchar(50),
@qty1 int,
@qty2 int,
@qty3 int,
@id uniqueidentifier,
@tablename varchar(50),
@month varchar(10)

AS
BEGIN

if(@StoreName='')
set @StoreName=null;

if(@fromDate='')
set @fromDate=null;

if(@toDate='')
set @toDate=null;

if(@Criteria1='')
set @Criteria1=null;

if(@Criteria2='')
set @Criteria2=null;

if(@reg='')
set @reg=null;

if(@cashier='')
set @cashier=null;

if(@type='')
set @type=null;

if(@exp='')
set @exp=null;

if(@value='')
set @value=null;

if(@exp='')
set @exp=null;

if(@value1='')
set @value1=null;

if(@value2='')
set @value2=null;

if(@qty1='')
set @qty1='0'

if(@qty2='')
set @qty2='0';

if(@qty3='')
set @qty3='0';

if(@exp1='')
set @exp1=null;

SET @tablename = 'TransactionData_' + @month + '_' + @id

BEGIN
if(@Criteria2 is NULL and @evenodd = 1)
BEGIN
select tt.tt_descr as type1,sd.td_store_name as StoreName,td.td_ref as ref1,td.td_device_data as data1,td.td_amount as amount,
td.td_user_name as name,td.td_receipt_id as id1,td.td_datetimestamp as date, RIGHT(td.td_device_name,1) as register,
td.td_item_description as descr,td.td_store_guid as StoreId,td.td_quantity as qnty from TransactionData_May_c82e104a_5011_49a0_9919_93b8b53bc2d0 td
join StoreDetails sd on sd.td_store_guid = td.td_store_guid
join TypeConfig tt on tt.tt_id = td.td_type
where sd.td_store_name like coalesce(@StoreName, sd.td_store_name) + '%'
and td.td_item_description like coalesce(@Criteria1,td.td_item_description) + '%'
and CONVERT (VARCHAR(10), td.td_datetimestamp,111) between coalesce(@fromDate,td.td_datetimestamp) and coalesce(@toDate,td.td_datetimestamp)
and tt.tt_descr like coalesce(@type, tt.tt_descr) + '%'
and RIGHT(td.td_device_name,1) like coalesce(@reg, td.td_device_name) + '%'
and td.td_user_name like coalesce(@cashier, td.td_user_name) + '%'
and ((td.td_amount > CONVERT(money,@value) AND @exp='>') OR (td.td_amount < CONVERT(money,@value) AND @exp='<')
OR (td.td_amount >= CONVERT(money,@value) AND @exp='>=') OR (td.td_amount <= CONVERT(money,@value) AND @exp='<=')
OR (td.td_amount = CONVERT(money,@value) AND @exp='=' OR td.td_amount between CONVERT(money,@value1) AND CONVERT(money,@value2) AND @exp='between' ))
and ((td.td_quantity > @qty1 AND @exp1='>') OR (td.td_quantity < @qty1 AND @exp1='<')
OR (td.td_quantity >= @qty1 AND @exp1='>=') OR (td.td_quantity <= @qty1 AND @exp1='<=')
OR (td.td_quantity = @qty1 AND @exp1='=') OR (td.td_quantity between @qty2 AND @qty3 AND @exp1='between'))
and @id = sd.td
Order By td_datetimestamp
END
END
END

I want to pass the table name 'TransactionData_May_c82e104a_5011_49a0_9919_93b8b53bc2d0' as a parameter. I have declared a parameter @tablename, and that is the format i want it to be.
So how can i modify my select statement above to pass it a sa parameter?
Please help me.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-01-26 : 11:08:25

I know you mentioned that you are not supposed to use dynamic SQL. However, if performance is any consideration at all, you will probably want to go that route. Here is an article that pertains to catch-all queries, which is very similar to what you are doing as far as how it is implemented.

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-26 : 11:15:12

why do you name tables like TransactionData_May_c82e104a_5011_49a0_9919_93b8b53bc2d0? what does this indicate? also for taking tablename from a parameter in query you need dynamic sql

make sure you read this

http://www.sommarskog.se/dynamic_sql.html

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

sindhu122
Starting Member

32 Posts

Posted - 2011-01-26 : 11:29:54

Hi visakh,
The table is generated like that when the data is downloaded using data services. Well, that is a format of a uniqueidentifier I suppose. I do no do that part, but just get those tables and have to implement this search feature on that data tables as I told you.

I am going through the Dynamic SQL link you sent me. I will try doing that way, but can you tell me how I can change the joins and all the other conditions which are there in my stored proc in that case?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-26 : 11:36:39

why do you need to change joins? for dynamic sql you dont need to change joins but just wrap the query in a string and join to variable to get tablename included dynamically.

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

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources