June 30, 2015 at 3:04 pm
Hello,
I have this stored procedure which i'm trying to execute, but get an error:
spFT @term='%FT%', @position>=0, @value_per_month >=0
I get the following error:
Incorrect syntax near '>'.
However, when I change the value to equal to, it works
spFT @term='%FT%', @position =0, @value_per_month =0
So trying to find error as to why >= doesn't work.
FYI:
@term nvarchar (250),
@position int,
@value_per_month int
Thank You
June 30, 2015 at 3:29 pm
You can only use = to assign values to variables. If you need to filter on >=0, you need to put that logic in the body of your stored procedure.
June 30, 2015 at 4:20 pm
Thank You Adam,
Do you mean like:
create procedure spFT
@term nvarchar (250),
@position int,
@value_per_month int
as
Begin
select [term],[position],[value_per_month]
from [dbo].[XXXX]
where [term]=@term and ([position]=@position) >=0
and [value_per_month]=@value_per_month >=0
End
June 30, 2015 at 11:34 pm
These are puzzling:
where [term]=@term and ([position]=@position) >=0
and [value_per_month]=@value_per_month >=0
Could you please explain what you're trying to accomplish? I just don't follow.
[position] is a column in your table, and @position would be a variable/parameter.
so say you have something like
DECLARE @position INT = 10;
SELECT ...
FROM ...
WHERE [position] = @position;
would evaluate to
SELECT ...
FROM ...
WHERE [position] = 10;
what do you mean about @position>=0 ? Is that supposed to be part of an IF statement?
July 1, 2015 at 1:45 am
Adam Angelini (6/30/2015)
You can only use = to assign values to variables. If you need to filter on >=0, you need to put that logic in the body of your stored procedure.
what adam mean is that when you assign the value to the parameters of stored procedure you can only use '='.
exec SomeSp @param = '%FT%', @Quantity = 0
now, how you want to use it in a query inside a SP, well it depends what you have in mind.
following is very confusing to understand what actually you are looking for. please explain a bit more in detail about below
where [term]=@term and ([position]=@position) >=0
and [value_per_month]=@value_per_month >=0
it even more helpful if you share some sample data and your desired output which will help a lot to understand the actual problem.
hope it helps.
July 5, 2015 at 5:42 pm
Hi Teri,
Let me try to clarify...
On a typical select query I would have:
select [term],[position],[value_per_month]
from [dbo].[XXXX]
where [term] like ='%something%' and [position] >0 and [value_per_month] >0
so what I wanted to do i put this simple query into a stored procedure, where I can use the stored procedure as filters, per se.
Thus, when Adam mentioned, You can only use = to assign values to variables. If you need to filter on >=0, you need to put that logic in the body of your stored procedure, so how else can i re-write stored procedure where
create procedure spFT
@term nvarchar (250),
@position int,
@value_per_month int
as
Begin
select [term],[position],[value_per_month]
from [dbo].[XXXX]
where [term]=@term and ([position]=@position)>0
and [value_per_month]=@value_per_month >0
End
How else can i re-write stored procedure that when i execute it, the bold below, i can simply modify and change its value.
spSomeSP @term='%some%thing%', @position>0
July 5, 2015 at 9:20 pm
What are you trying to accomplish with the >= comparisons here:
where [term]=@term and ([position]=@position)>0
and [value_per_month]=@value_per_month >0
?
if you're trying to validate that the values passed to @position and @value_per_month are both greater than zero, you would have to do something like this:
CREATE PROC spFT
@term nvarchar (250),
@position int,
@value_per_month int
AS
BEGIN
IF @value_per_month>0 AND @position>0
BEGIN
select [term],[position],[value_per_month]
from [dbo].[XXXX]
where [term]=@term
and [value_per_month]=@value_per_month
and [position] = @position
END
ELSE
PRINT 'oops!' -- trap errors here.
END
If you're trying to change the comparison operators in your stored procedure so that you can use any of {=,>=,<=} you would have to either create another branch or use dynamic SQL.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply