October 19, 2012 at 4:19 pm
Hello All,
I have a bit of a 'is it possible without dynamic SQL' question.
I have a table of Footers. I want to select from this footers table in from a stored procedure. The footer has a placeholder referencing a parameter. My assumption is the parameter will always be in the stored procedure.
The setup
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Footers]') AND type in (N'U'))
DROP TABLE [dbo].[Footers]
CREATE TABLE [dbo].[Footers](
[FooterID] [int] NULL,
[Footer] [varchar](160) NULL,
[DynamicFlag] [char](1) NULL,
[DynamicParamter] [varchar](30) NULL
) ON [PRIMARY]
INSERT INTO [dbo].[Footers]([FooterID],[Footer],[DynamicFlag],[DynamicParamter]) VALUES ( 1,'This material is for @test1 only.','Y','@Test1')
INSERT INTO [dbo].[Footers]([FooterID],[Footer],[DynamicFlag],[DynamicParamter]) VALUES ( 2,'This material is not for @Test2.','Y','@Test2')
SELECT * FROM dbo.Footers
So my procedure would have the following parameters
DECLARE @test1 varchar(30)
DECLARE @Test2 varchar(30)
SET @test1 = 'MyNumberOneClient'
SET @Test2 = 'MyNumberTwoClient'
When I select from the table I want to return the parameter value embedded in the results.
SELECT Footer FROM dbo.Footers WHERE FooterID = 1
SELECT Footer FROM dbo.Footers WHERE FooterID = 2
So the above Selects would give me.
This material is for MyNumberOneClient only.
This material is not for MyNumberTwoClient.
Possible? Preferably I'd do the magic in a view or function rather than in the stored procedure.
Let me also add that I am very grateful to all the generous folks who are willing to put in there time here. I have learned so much...
October 20, 2012 at 12:30 am
A simple REPLACE() in your SELECT is all that is needed to substitute your parameter value for the constant text in your footer. In your example:
declare @test1 varchar(30) = 'MyNumberOneClient'
declare @Test2 varchar(30) = 'MyNumberTwoClient'
select REPLACE(footer,'@Test1',@Test1) as footer from Footers where FooterID = 1
select REPLACE(footer,'@Test2',@Test2) as footer from Footers where FooterID = 2
-- OR
select REPLACE(footer,'@Test1',@Test1) as footer from Footers where DynamicParamter = '@Test1'
select REPLACE(footer,'@Test2',@Test2) as footer from Footers where DynamicParamter = '@Test2'
In these situations, I usually try to make the character strings to be replaced more generic. See below.
Please note, the query below changes the values in your table.
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Footers]') AND type in (N'U'))
DROP TABLE [dbo].[Footers]
CREATE TABLE [dbo].[Footers](
[FooterID] [int] NULL,
[Footer] [varchar](160) NULL,
[DynamicFlag] [char](1) NULL,
[DynamicParamter] [varchar](30) NULL
) ON [PRIMARY]
INSERT INTO [dbo].[Footers]([FooterID],[Footer],[DynamicFlag],[DynamicParamter]) VALUES ( 1,'This material is for @client only.','Y','@Test1')
INSERT INTO [dbo].[Footers]([FooterID],[Footer],[DynamicFlag],[DynamicParamter]) VALUES ( 2,'This material is not for @client.','Y','@Test2')
SELECT * FROM dbo.Footers
declare @client varchar(30) = 'MyNumberOneClient'
declare @footerID int = 1
select REPLACE(footer,'@client',@client) from Footers where FooterID = @footerID
-- same query just different parameters
select@client = 'MyNumberTwoClient'
,@footerID = 2
select REPLACE(footer,'@client',@client) from Footers where FooterID = @footerID
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
October 22, 2012 at 2:21 pm
Ok, thanks, it sure seems like REPLACE would be the way to go.
Views don't seem possible.
This is invalid syntax because of the parameter.
CREATE VIEW myView
AS
select REPLACE(footer,'@client',@client) from Footers
I'll probably use a procedure
CREATE PROCEDURE myProc
@footerID int,
@client varchar(30)
AS
select REPLACE(footer,'@client',@client) from Footers where FooterID = @footerID
Then call it like
EXEC myProc 1,'MyNumberOneClient'
Downside here is my parameter list may grow. I may have @Country
I assume I could nest my replaces.
select REPLACE(REPLACE(footer,'@client',@client),'@Country',@Country) from Footers where FooterID = @footerID
Then I would need to ALTER my procedure as new parameters are created or I could create generic parameters sufficient to cover anticipated needs.
CREATE PROCEDURE myProc
@footerID int,
@param1 varchar(30),
@param2 varchar(30),
@param3 varchar(30),
@param4 varchar(30)
AS...
DECLARE @client varchar(30),
DECLARE @Country varchar(30)
etc
SET @client = @param1
SET @Country = @param2
--add new parameters as needed
SELECT ...--nested REPLACE for as many parameters as needed
EXEC myProc 1, 'MyNumberOneClient', NULL,NULL,NULL...
My goal here would be to not have to go back and modify the execution of the procedure as new parameters are added. I should be able to modify the procedure only.
Sorry for the aircode above and for thinking out loud...
October 22, 2012 at 4:00 pm
Hi
Rather than a procedure you could try a table valued function along the lines of
CREATE FUNCTION fnFooter (@p1 varchar(30) )
RETURNS TABLE AS RETURN
SELECT FooterID, REPLACE(Footer, '@client', @p1) Footer FROM Footers
SELECT Footer FROM dbo.fnFooter(@Test1) WHERE FooterID = 1
SELECT Footer FROM dbo.fnFooter(@Test2) WHERE FooterID = 2
You could also make it a bit more flexible by adding additional dynamicParameter columns to your Footer table to match the number of parameters you are allowing and create function like
CREATE FUNCTION fnFooters (
@p1 varchar(30),
@p2 varchar(30),
@p3 varchar(30),
@p4 varchar(30),
@p5 varchar(30)
)
RETURNS TABLE AS RETURN
SELECT FooterID,
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
footer,isnull(DynamicParameter1,''),isnull(@p1,''))
,isnull(DynamicParameter2,''),isnull(@p2,''))
,isnull(DynamicParameter3,''),isnull(@p3,''))
,isnull(DynamicParameter4,''),isnull(@p4,''))
,isnull(DynamicParameter5,''),isnull(@p5,''))Footer
FROM Footers
SELECT Footer FROM dbo.fnFooters(@Test1, null, null, null, null) WHERE FooterID = 1
SELECT Footer FROM dbo.fnFooters(@Test2, null, null, null, null) WHERE FooterID = 2
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply