August 27, 2009 at 4:40 am
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
August 27, 2009 at 5:02 am
Hi Fred,
did something go wrong with your post ?
The code segments look the same.
August 27, 2009 at 5:19 am
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
August 27, 2009 at 5:42 am
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
August 27, 2009 at 6:00 am
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/
August 27, 2009 at 6:42 am
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
August 27, 2009 at 8:11 am
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
August 27, 2009 at 8:31 am
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.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply