Modifying stored procedure SQL from front end code 1

[Beren1h]

Hello all,

I am currently working on a project in Access that runs several slight variations on a query that has it's SQL statement altered through code before it is run. Something like changing:

SELECT * FROM Table WHERE X > 1

to

SELECT * FROM Table WHERE X < 1 AND X >-2

There is a chance that I may get to run this off SQL Server and I was wondering if I could do the same thing to a stored procedure. I'm thinking I could get the performance gain from the stored procedure, but not have to write many stored procedures that do almost the same thing.

Any advice?

 

[tlbroadbent]

You'll have to use dynamic SQL to do this in a stored procedure. That will negate any performance benefits you might have received from using a stored procedure. When changing the basic query structure, I'd recommend keeping the logic in the client. Just my 2-bits. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions.

 

[Beren1h]

Fudge...

I was afraid of that...

Thanks...

 

[Durkin]

If you're just changing the criteria and not the tables you are returning data from it is often possible to write a sql statement that returns different values based on parameters. I wrote a stored proc recently that in some cases I wished to run a number of checks on a range of investor numbers (IN_No) or all the investor numbers. Alternatively I wanted to run the checks for a range of Planners (IN_PL_ID) or all of the planners. This is the part of the where clause which I used to return the records I wanted (@IsInv is a bit parameter which signified whether the range was Investors or planners, @nFrom and @nTo are the range - they both have a default of 0) .

		AND ((IN_No >=
			CASE 
			WHEN ((ISNULL(@nFrom, 0) <> 0)) THEN @nFrom
			ELSE IN_No
			END
		AND IN_No <=
			CASE 
			WHEN ((ISNULL(@nTo, 0) <> 0)) THEN @nTo
			ELSE IN_No
			END
		AND (@IsInv = 1))
		OR (IN_PL_ID <=
			CASE 
			WHEN (ISNULL(@nFrom, 0) <> 0) THEN @nFrom
			ELSE IN_PL_ID
			END
		AND IN_PL_ID >=
			CASE 
			WHEN (ISNULL(@nTo, 0) <> 0) THEN @nTo
			ELSE IN_PL_ID
			END
		AND (@IsInv = 0)))

Hope this helps.
Durkin