Trying to build a generic Stored Procedure

Question

Post reply

Trying to build a generic Stored Procedure

FredS-1001785

Mr or Mrs. 500

Points: 564
More actions
August 27, 2009 at 4:40 am

#209928

I have a simple Stored Procedure that returns a record set to my app. I would like to pass a parameter to the SP that defines an area so that I can link the IH_master table to several different link tables instead of duplicating code in numerous SPs.

@Area = 'COI_Shell'

select I.*, L.ShowOnWeb from IH_master as I
inner join IH_lnk_COIShell as L on I.IH_Key = L.IH_Key
where L.coi_shell = @COI_Shell

@Area = 'Supplier'

select I.*, L.ShowOnWeb from IH_master as I
inner join IH_lnk_COIShell as L on I.IH_Key = L.IH_Key
where L.coi_shell = @COI_Shell

I can do this in other languages, but I am new to t-sql.

Thanks.

Fred

Viewing 8 posts - 1 through 7 (of 7 total)

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

Dave Ballantyne SSC-Dedicated Points: 33556 More actions · Answer 1

Dave Ballantyne

SSC-Dedicated

Points: 33556

August 27, 2009 at 5:02 am

#1045425

Hi Fred,

did something go wrong with your post ?

The code segments look the same.

Clear Sky SQL
My Blog[/url]

Lowell SSC Guru Points: 323482 More actions · Answer 2

i'm not sure, but ot looks like you want to parameterize the value for @COI_Shell , right?

Is this what you are looking for?(I guessed on the datatype)



CREATE PROCEDURE MyProcedure(@COI_Shell int)

AS

BEGIN

SET NOCOUNT ON

select I.*, L.ShowOnWeb from IH_master as I

    inner join IH_lnk_COIShell as L

 on I.IH_Key = L.IH_Key

  where L.coi_shell =@COI_Shell

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!

FredS-1001785 Mr or Mrs. 500 Points: 564 More actions · Answer 3

Sorry. Let's try this again. My code examples were incomplete.

I have a simple Stored Procedure that returns a record set to my app. I would like to pass a parameter to the SP that defines an area so that I can link the IH_master table to several different link tables instead of duplicating code in numerous SPs.

I would pass @Area and @Area_key to the Stored Procedure and it would return the same fields, but based on differing criteria.

@Area = 'COI_Shell'

select I.*, L.ShowOnWeb from IH_master as I

inner join IH_lnk_COIShell as L

on I.IH_Key = L.IH_Key

where L.coi_shell = @Area_Key

@Area = 'Supplier'

select I.*, L.ShowOnWeb from IH_master as I

inner join IH_lnk_Supplier as L

on I.IH_Key = L.IH_Key

where L.p_key = @Area_Key

I can do this in other languages, but I am new to t-sql.

Thanks.

Fred

Dave Ballantyne SSC-Dedicated Points: 33556 More actions · Answer 4

FredS (8/27/2009) but based on differing criteria.

Thats the key here, sql is not designed to easily (perhaps more importantly efficiently) do that.

You could try dynamic SQL http://www.sommarskog.se/dynamic_sql.html

or a catch all query http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

Clear Sky SQL
My Blog[/url]

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 5

FredS (8/27/2009)

I would pass @Area and @Area_key to the Stored Procedure and it would return the same fields, but based on differing criteria.

Not a good idea. There's a number of downsides to doing this in SQL, parameter sniffing being one of them.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

FredS-1001785 Mr or Mrs. 500 Points: 564 More actions · Answer 6

I was able to make it work, but I have no idea if it is stable or a good idea.

ALTER PROCEDURE [dbo].[IH_GetRecords]



	@Area char(15)

	,@AreaKey Int

AS

BEGIN

	-- SET NOCOUNT ON added to prevent extra result sets from

	-- interfering with SELECT statements.

	SET NOCOUNT ON;

if @Area = 'COI_Shell'

	begin

		select I.*, L.ShowOnWeb from [psc_comp].[dbo].[IH_master] as I

			inner join [psc_comp].dbo.IH_lnk_COIShell as L

			on I.IH_Key = L.IH_Key

			where L.coi_shell = @AreaKey

		return

	end

if @Area = 'Suppliers'

	begin

		select I.*, L.ShowOnWeb from [psc_comp].[dbo].[IH_master] as I

			inner join [psc_comp].dbo.IH_lnk_Supplier as L

			on I.IH_Key = L.IH_Key

			where L.p_key = @AreaKey

		return

	end

select 'Invalid Area (' + rtrim(@Area) + ') requested' as OurDocName

END

Fred

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 7

FredS (8/27/2009)

I was able to make it work, but I have no idea if it is stable or a good idea.

ALTER PROCEDURE [dbo].[IH_GetRecords]
@Area char(15) ,@AreaKey Int AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; if @Area = 'COI_Shell' begin select I.*, L.ShowOnWeb from [psc_comp].[dbo].[IH_master] as I inner join [psc_comp].dbo.IH_lnk_COIShell as L on I.IH_Key = L.IH_Key where L.coi_shell = @AreaKey return end if @Area = 'Suppliers' begin select I.*, L.ShowOnWeb from [psc_comp].[dbo].[IH_master] as I inner join [psc_comp].dbo.IH_lnk_Supplier as L on I.IH_Key = L.IH_Key where L.p_key = @AreaKey return end select 'Invalid Area (' + rtrim(@Area) + ') requested' as OurDocName
END

Fred

I wouldn't do it this way. Each of the queries in the procedure above should be moved to their own stored procedure. you could then call the necessary procedure based on the value of the parameters passed to the main procedure. The way you have it right now your performance may suffer.