Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Dynamic Footers, without dynamic SQL Expand / Collapse
Author
Message
Posted Friday, October 19, 2012 4:19 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 9:12 AM
Points: 653, Visits: 3,838
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...
Post #1375056
Posted Saturday, October 20, 2012 12:30 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Sunday, August 17, 2014 3:10 PM
Points: 4,013, Visits: 6,098
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? -- Stephen Stills
Post #1375085
Posted Monday, October 22, 2012 2:21 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 9:12 AM
Points: 653, Visits: 3,838
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...

Post #1375722
Posted Monday, October 22, 2012 4:00 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 12:06 AM
Points: 994, Visits: 3,008
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

Post #1375765
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse