Can a Stored Procedure do addition, subtraction, greater than and less than?

Question

Post reply

Can a Stored Procedure do addition, subtraction, greater than and less than?

philosophaie

SSC Eights!

Points: 963
More actions
January 27, 2014 at 1:01 pm

#396170

Can a Stored Procedure do addition, subtraction, greater than and less than? If so could you please give some examples.

Viewing 4 posts - 1 through 3 (of 3 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

it depends on what you mean;

you can of course return results that have operations agaisnt them.

if you mean can you pass a command and have it interpreted, then yes, with dynamic SQL, but it's a poor practice.

some crappy examples:

greater than or less than is not an operation, but a comparison, but you can mold a proc to rueturn desired results based on the comparison

GO

CREATE PROCEDURE EXAMPLE(@param1 int,@param2 int)

AS

BEGIN --PROC

SELECT @param1 + @param2

SELECT @param1 - @param2

SELECT @param1 * @param2

SELECT @param1 / @param2 --note! INTEGER division

SELECT (@param1 * 1.0) / @param2 --

SELECT CASE

WHEN @param1 = @param2

Then CONVERT(VARCHAR,@param1) + ' Equals' + CONVERT(VARCHAR,@param2)

WHEN @param1 > @param2

Then CONVERT(VARCHAR,@param1) + ' is greater than' + CONVERT(VARCHAR,@param2)

WHEN @param1 < @param2

Then CONVERT(VARCHAR,@param1) + ' is less than' + CONVERT(VARCHAR,@param2)

ELSE 'Something must be null'

END

END --PROC

GO

EXECUTE EXAMPLE 14,4

GO

CREATE PROCEDURE EXAMPLE2(@param1 int,@param2 int,@operation VARCHAR(10))

AS

BEGIN

DECLARE @cmd varchar(4000) = 'SELECT ' + CONVERT(VARCHAR,@param1) + ' ' + @operation + ' ' + CONVERT(VARCHAR,@param2)

EXECUTE(@cmd)

END

GO

EXECUTE EXAMPLE2 14,4 ,'*'

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

philosophaie SSC Eights! Points: 963 More actions · Answer 2

Could conditional do something like this:

CREATE PROCEDURE [IFTHEN] AS

DECLARE @max-2 INT, @min-2 INT;

@min-2=MIN(idt)

,@max=MAX(idt)

FROM test;

BEGIN --PROC

SELECT CASE ((@max-@min)>50000)

THEN

---Execute more SQL commands---

ELSE

---Execute some more SQL commands

END;

PROC--END

GO

Lowell SSC Guru Points: 323518 More actions · Answer 3

yes it can, if you follow the correct construct;

in SQl there's no THEN:

CREATE PROCEDURE [IFTHEN] AS

DECLARE @max-2 INT, @min-2 INT;

SELECT

@min-2=MIN(idt)

,@max=MAX(idt)

FROM test;

IF ((@max-@min)>50000)

BEGIN

PRINT 'Doing stuff.'

---Execute more SQL commands---

END; --IF

ELSE

BEGIN

PRINT 'Doing other stuff.'

---Execute some more SQL commands

END; --IF

END; --PROC

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!