Dynamic stored procedure help needed

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

SQL Server Development (2000)

Dynamic stored procedure help needed

Author

Topic

james_b
Starting Member

9 Posts

Posted - 2004-07-27 : 05:43:25

I'm trying to write a stored procedure that accepts a varying amount of parameters

In pseudo-code, it will look something like:

CREATE PROCEDURE sp_GetSubscriberData
(
@RegionId_1 int,
@RegionId_2 int,

-- optional regionid's, up to maximum of 10

@Selected_Field_1 varchar(50),
@Selected_Field_2 varchar(50),

-- optional selected fields, up to maximum of 10

@SortColumn varchar(50)
)

AS

EXEC('SELECT ' + @Selected_Field_1 + ', ' + @Selected_Field_2 + ' FROM SubscriberData WHERE RegionId = ' + @RegionId_1 + ' OR RegionId = ' + @RegionId_2 + 'ORDER BY ' + @SortColumn )

GO

Obviously the problem is that the actual select statement needs to be modified on the fly, depending on how many regionid parameters and selected_field parameters have actually been passed to the procedure. Can anyone point me in the right direction for solving this?

Thanks for any help :)

James

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2004-07-27 : 06:02:11

Look at array / CSV parsing for the regionid's
Return all 10 columns, project the columns at the client end.
Look up the use of the CASE expression in the ORDER BY clause.

DavidM

"Always pre-heat the oven"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources