SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Dynamic Footers, without dynamic SQL


Dynamic Footers, without dynamic SQL

Author
Message
Chrissy321
Chrissy321
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3212 Visits: 4796
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...
The Dixie Flatline
The Dixie Flatline
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13082 Visits: 6903
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
Chrissy321
Chrissy321
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3212 Visits: 4796
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...
mickyT
mickyT
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2870 Visits: 3318
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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search