passing operators to a sproc

Question

passing operators to a sproc

yogiberr

Hall of Fame

Points: 3679
More actions
November 18, 2004 at 10:18 am

#361618

hiya,
I'm using dotnet.I can pass an operator into my sproc as follows:
New SqlParameter("@operator", SqlDbType.Text)
Now, I want to use this operator in my sproc

<simplifiedSql>
SELECT *
FROM myTable
WHERE price = 10
<\simplifiedSql>

So, in theory, If I pass the “=” operator to my sproc, then this should work.
<sql>

@operator text
SELECT *
FROM myTable
WHERE price @operator 10
<\sql>

Obviously, this doesn’t work.Do I have to cast the “text” value to a different datatype within the sproc?
Maybe I should also have cast it to a different datatype in dotnet, as I was passing it in?

I think I am on the right tracks.Can anyone advise?

Cheers,
Yogi

Viewing 8 posts - 1 through 7 (of 7 total)

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

Steve Jones - SSC Editor SSC Guru Points: 735712 More actions · Answer 1

What does your sproc look like? Why are you passing in the query? I'd expect the sproc takes a parameter for the price and you only pass in the 10.

yogiberr Hall of Fame Points: 3679 More actions · Answer 2

hi Steve,

I simplified my sproc.It will actually take 2 params:

1) the operator, ie "=" ">" or "<"

2) a date

Then, if i can get it to work, I can have a "datePicker" that will allow me to to select dates that are either:

1) on

2) before

3) after

...a given date.

This could equally apply to prices I suppose.

Is it possible to use operators this way in sql2k sprocs?

cheers,

yogi

Rax SSC Enthusiast Points: 144 More actions · Answer 3

You can do it but in that case you have to make your query dynamic within the procedure...something like

declare @sql nvarchar(4000)

set @sql = 'SELECT * FROM myTable WHERE price '+rtrim(@operator)+' 10'

exec sp_executesql @sql

you can see different types of usage of sp_executesql in BOL...

(Note: User excuting this procedure MUST have SELECT permission granted on the table used in query...this sucks...:angry

yogiberr Hall of Fame Points: 3679 More actions · Answer 4

cheers Rax.

I'm out of the office now, but I'll try first thing tomorrow.

yogi

yogiberr Hall of Fame Points: 3679 More actions · Answer 5

yawn...morning

I can now get the spoc to work with a hard-coded value of 10.

I am trying to pass in a param to replace the hard-coded value, But at the moment, it retuns nothing.Please note that I have also tried via QA, and still it returns nothing.

@operator varchar

declare @sql nvarchar(4000)

set @sql = 'SELECT * FROM tblOperator

WHERE currValue '+rtrim(@operator)+' 10'

<\workingHardCodedValue>

@operator varchar

@valueToCompare VARCHAR

declare @sql nvarchar(4000)

set @sql = 'SELECT * FROM tblOperator

WHERE currValue '+rtrim(@operator)+' ' +@valueToCompare

exec sp_executesql @sql

<\nonWorkingParamValue>

I’m nearly there. Can anyone see where I’m going wrong?

Ta,

yogi

hoo-t SSChampion Points: 12713 More actions · Answer 6

The only thing I see is that you don't have lengths defined for your parms.

Try this....

create procedure dynam_oper

(@operator char(2),

@value VARCHAR(3))

as

declare @sql nvarchar(4000)

set @sql = 'SELECT * FROM pubs.dbo.sales

WHERE qty '+rtrim(@operator)+' ' + @value

print @sql

exec sp_executesql @sql

/*

exec dynam_oper '=', '5'

*/

yogiberr Hall of Fame Points: 3679 More actions · Answer 7

sweet as a nut.

Thanks to all.It's strange, but I initially didn't have a length specified for my @operator param, yet that part of it worked.

livin and learnin.

yogi