Polymorphism in Stored Procedures

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

SQL Server 2000 Forums

Transact-SQL (2000)

Polymorphism in Stored Procedures

Author

Topic

adlo
Posting Yak Master

108 Posts

Posted - 2004-08-30 : 15:11:44

How does one implement polymorphism in stored procedures?

What I currently do is receive parameters e.g.

@value1 varchar,
@value2 varchar,
@value3 varchar

AS

IF value3='' --nothing
BEGIN
-- The whole select statetment

END
else
BEGIN
-- The whole select statetment
END

This method gets rather bulky when there are many different parameter combinations and when the sql statements get large.
Is there a way to limit the "if" only to the part of the query that varies instead of having to redo the select query for each "if".

DustinMichaels
Constraint Violating Yak Guru

464 Posts

Posted - 2004-08-30 : 15:12:28

What do you mean by polymorphism? T-SQL is not object oriented based.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-08-30 : 15:18:21

why couldn't your select include a where clause like:

(@value3='' and blah blah)
or
(@value2='' and blah blah)

etc...

Corey

X002548
Not Just a Number

15586 Posts

Posted - 2004-08-30 : 16:18:54

I think you might want CASE

Do you have Books online?

SELECT CASE WHEN Col1 = 'A' THEN 'xxx' WHEN Col1 = 'B' Then 'yyy' END
FROM myTable99
WHERE yada yada yada

Brett

8-)

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2004-08-30 : 18:19:52

Are you trying to execute different SELECT statements based upon the input parameters of your sproc?

I'm only aware of the method you are using, or creating dynamic sql to do what you want. Your method is actually faster, since the sproc doesn't need to be reparsed every time it is executed. The dynamic SQL method requires reparsing each run.

-ec

adlo
Posting Yak Master

108 Posts

Posted - 2004-08-31 : 09:42:18

I want to say the follwing without repeating the whole select statement.

IF @value3='' --nothing
BEGIN
-- The whole select statetment

END
else
BEGIN
-- The whole select statetment
WHERE value3=@value3
END

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-08-31 : 09:48:04

I was pretty close!!

--whole select statement
Where (@value3='' or value3=@value3)

Corey

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-08-31 : 09:51:33

is maybe this what you need?
where there is @value = '' in your case that paramter you don give in your sproc.


CREATE PROCEDURE spYourProcedure
@value1 varchar(50) null,
@value2 varchar(50) null,
@value3 varchar(50) null
AS

SELECT 	*
FROM 	MyTable
WHERE 	(value1 = @value1 or @value1 is null) and 
	(value2 = @value2 or @value2 is null) and 
	(value3 = @value3 or @value3 is null)
GO

Go with the flow & have fun! Else fight the flow :)

adlo
Posting Yak Master

108 Posts

Posted - 2004-08-31 : 10:31:36

Sorry I didn't state the situation clearly.

Here is a better example

CREATE PROCEDURE spMyProcedure
@Member_ID int null,
AS

IF @Value1 is null -- if Value1 is not spesified get ALL member records
BEGIN
SELECT * FROM Member
END

ELSE -- if Value1 is spesified get spesific member record
BEGIN
SELECT * FROM Member
WHERE Name =@Value1
END

E.g. I'm trying to implement optional fields. If optional field exists then add sql where clause.

mr_mist
Grunnio

1870 Posts

Posted - 2004-08-31 : 10:38:58

You don't need two statements for that, you can just put

WHERE (NAME = @VALUE1 or @VALUE1 is null)

-------
Moo. :)

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-08-31 : 10:45:45

i really think ehat i gave you should work for you. as i understand your situation...

Go with the flow & have fun! Else fight the flow :)

adlo
Posting Yak Master

108 Posts

Posted - 2004-08-31 : 10:59:53

Thanks,I see know.