February 10, 2012 at 3:11 am
Hi all. First of all, i am relatively unexperienced with tsql, so please do not judge too hard if my question is dumb
I am currently workin on a nasty report which calculates all paid leave days per employee within a period. The code is working but only with hard coded table names.
Problem is, our company exists of many clients, and their data is stored in separate tables identified by client name. the table structure is all the same. The source is a NAV 2009 Database.
So i thought of dynamically passing the client name to sql server, instead of using a hard coded query for every client. Manager can then choose in report service which client to view.
So far, i have created a stored procedure with nested views to prepare the final recordset. As i have learned, i cannot use exec() with nested queries, because it is running in a separate batch (if it was only one query, it works with exec of course).
Summary:
i'd like to replace the hard coded table names with the clientparam from the report.
@Niederlassung is the client parameter from report service
@Jahr is the current period
Client Table start with: [dbo].[88 Aufzugswerke M_Schmitt+Sohn$...] and should be like [dbo].[' + @Niederlassung +'$...] to work.
code:
/****** Object: StoredProcedure [dbo].[Rep_Urlaubstage] Script Date: 02/10/2012 09:40:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Rep_Urlaubstage] (
-- Add the parameters for the stored procedure here
@Niederlassung AS Varchar(30),
@Jahr AS varchar(4)
)
AS
BEGIN
SET NOCOUNT ON;
set DATEFORMAT ymd;
DECLARE @Startdatum As smalldatetime
DECLARE @Enddatum As smalldatetime
SET @Startdatum = '' + @Jahr + '-01-01 00:00:00';
SET @Enddatum = '' + @Jahr + '-12-31 00:00:00';
DECLARE @SQLcmd1 AS Varchar(4000)
DECLARE @FAKTOR AS FLOAT;
-- temp. Resultset (CTE, common table expresseion) vorbereiten
WITH view1_CTE AS -- gebuchte und ungebuchte Urlaubstage per Mitarbeiter mit Lohnart 0600 = URLAUB
(
SELECT DISTINCT AE.[Period Date]
,AE.[Correction Date]
,AE.[Creation Date]
,AE.[Employee No_]
,AE.[Pay Type No_]
,AE.[Cause of Absence Code]
,AE.[Description Absence Code]
,'Faktor' = CASE
WHEN AE.[Cause of Absence Code] = 'URLAUB' THEN '1'
ELSE '0.5'
END
,AE.[From Date]
,AE.[To Date]
,datediff(dd, AE.[From Date], AE.[To Date])+1 AS Tage --wird nicht benötigt
FROM [dbo].[88 Aufzugswerke M_Schmitt+Sohn$Absence Entry] AS AE
inner join [88 Aufzugswerke M_Schmitt+Sohn$Employee] AS E on E.[No_]=AE.[Employee No_]
WHERE AE.[Pay Type No_] = 0600
AND AE.[Period Date] >= @Startdatum AND AE.[Period Date] <= @Enddatum
),
view2_CTE AS -- Feiertage und Wochenende von gebuchten Urlaubstagen abziehen
(
SELECT [From Date]
, [To Date]
, [Faktor]
, [Employee No_]
, CAST(dbo.fkt_datum_HolenAnzahlArbeitstage([From Date], [To Date]) * CONVERT(float, [Faktor]) AS float) as arbeitstage
, (select count(*) from [88 Aufzugswerke M_Schmitt+Sohn$Statut_ Holiday Calendar Line]
where [Statut_ Holiday Calendar Code] = E.[National Holiday Calendar Code]
and [Date]>=[From Date]
and Date<=[To Date]) AS Feiertage
FROM view1_CTE
inner join [88 Aufzugswerke M_Schmitt+Sohn$Employee] AS E on E.[No_]=[Employee No_]
),
view3_CTE AS -- Gebuchte genommene Urlaubstage berechnen
(
SELECT [Employee No_]
, SUM(CAST(arbeitstage AS float) - CAST(Feiertage AS float)) AS [Verbraucht]
FROM view2_CTE
GROUP BY [Employee No_]
),
view4_CTE AS --ungebuchte Urlaubstage Liste
(
SELECT [Employee No_]
,[From Date]
,[To Date]
,CAST(dbo.fkt_datum_HolenAnzahlArbeitstage([From Date], [To Date]) AS float) as [ungebuchte_arbeitstage]
, (select count(*) from [88 Aufzugswerke M_Schmitt+Sohn$Statut_ Holiday Calendar Line] --Feiertage
where [Statut_ Holiday Calendar Code] = E.[National Holiday Calendar Code]
and [Date]>=[From Date]
and Date<=[To Date]) AS Feiertage
FROM [dbo].[88 Aufzugswerke M_Schmitt+Sohn$Employee Absence]
inner join [88 Aufzugswerke M_Schmitt+Sohn$Employee] AS E on E.[No_]=[Employee No_]
WHERE [Cause of Absence Code] = 'URLAUB' --kein Code nur Varchar Beschr. in Tabelle
),
view5_CTE AS --ungebuchte Urlaubstage per Mitarbeiter -Feiertage
(
SELECT [Employee No_], SUM([ungebuchte_arbeitstage] - [Feiertage]) AS [Ungebuchte Urlaubstage]
FROM view4_CTE
GROUP BY [Employee No_]
),
view6_CTE AS --korrekturliste gebuchte Einträge ("Von Datum" > 1753!)
(
SELECT [Employee No_]
,[From Date]
,[To Date]
,CAST(dbo.fkt_datum_HolenAnzahlArbeitstage([From Date], [To Date]) AS float) as [gebuchte_korrekturtage]
, (select count(*) from [88 Aufzugswerke M_Schmitt+Sohn$Statut_ Holiday Calendar Line] --Feiertage
where [Statut_ Holiday Calendar Code] = E.[National Holiday Calendar Code]
and [Date]>=[From Date]
and Date<=[To Date]) AS Feiertage
FROM [dbo].[88 Aufzugswerke M_Schmitt+Sohn$Absence Corr_ Reg_ Jnl_ Line]
inner join [88 Aufzugswerke M_Schmitt+Sohn$Employee] AS E on E.[No_]=[Employee No_]
WHERE [Cause of Absence Code] = 'URLAUB' AND
[From Date] > '1753-01-01 00:00:00'
),
view7_CTE AS -- korrekturliste per Mitarbeiter - Feiertage
(
SELECT [Employee No_],
SUM([gebuchte_korrekturtage] - [Feiertage]) AS [nachzubuchende Urlaubstage]
FROM view6_CTE
GROUP BY [Employee No_]
),
view8_CTE AS --korrekturliste abzuziehende Einträge ("Von Datum" leer!) z.b. Krankheit während Urlaub
(
SELECT [Employee No_], count(*) AS [Korrekturbuchung]
FROM [dbo].[88 Aufzugswerke M_Schmitt+Sohn$Absence Corr_ Reg_ Jnl_ Line]
WHERE [Cause of Absence Code] = 'URLAUB'
AND [From Date] = '1753-01-01 00:00:00'
GROUP BY [Employee No_]
)
--SELECT * FROM view3_CTE
-- Recordest für Report aufbereiten, Urlaubsanspruch per Mitarbeiter
SELECT jnl.[Employee No_] AS [Mitarbeiternr_]
, res.[Name], CONVERT(float, LEFT(SUM(jnl.[Days]), 4)) AS [Urlaubsanspruch]
, v.[Verbraucht]
, u.[Ungebuchte Urlaubstage]
, n.[nachzubuchende Urlaubstage]
, k.[Korrekturbuchung]
FROM [dbo].[88 Aufzugswerke M_Schmitt+Sohn$Recurring Reg_ Journal Line] AS jnl
INNER JOIN [dbo].[Resource] AS res ON jnl.[Employee No_] = res.[No_]
LEFT JOIN view3_CTE as v ON res.[No_] = v.[Employee No_] --gebuchte Urlaubstage
LEFT JOIN view5_CTE as u ON res.[No_] = u.[Employee No_] --ungebuchte Urlaubstage
LEFT JOIN view7_CTE as n ON res.[No_] = n.[Employee No_] --nachzubuchende Urlaubstage (Korrekturliste)
LEFT JOIN view8_CTE as k ON res.[No_] = k.[Employee No_] --Korrekturbuchungen (Gutschrift)
WHERE [Pay Type No_] = 0500
GROUP BY jnl.[Employee No_]
,res.[Name],v.[Verbraucht]
,u.[Ungebuchte Urlaubstage]
,n.[nachzubuchende Urlaubstage]
,k.[Korrekturbuchung]
--EXEC (@SQLString)
END
thanks for your help, and sorry for long text!
Regards
Joerg
February 10, 2012 at 4:32 am
I see dynamic SQL as the only way to go.
Turn the whole T-SQL statement to a nvarchar(max) variable and execute it with sp_executesql.
Example:
DECLARE @tableName sysname
SELECT @tableName = 'master.dbo.spt_values'
DECLARE @sql nvarchar(max)
SET @sql = 'SELECT TOP(10) * FROM ' + @tableName
EXEC sp_executesql @sql
-- Gianluca Sartori
February 10, 2012 at 4:36 am
I've not got time to go over it and make sure all of the syntax is correct, but this is the general idea: -
ALTER PROCEDURE [dbo].[Rep_Urlaubstage] (
-- Add the parameters for the stored procedure here
@Niederlassung AS Varchar(30),
@Jahr AS varchar(4)
)
AS
BEGIN
SET NOCOUNT ON;
set DATEFORMAT ymd;
DECLARE @OStartdatum As smalldatetime
DECLARE @OEnddatum As smalldatetime
SET @OStartdatum = '' + @Jahr + '-01-01 00:00:00';
SET @OEnddatum = '' + @Jahr + '-12-31 00:00:00';
DECLARE @SQLcmd1 AS NVARCHAR(MAX)
DECLARE @FAKTOR AS FLOAT;
SET @SQLcmd1 = @SQLcmd1 + 'WITH view1_CTE AS -- gebuchte und ungebuchte Urlaubstage per Mitarbeiter mit Lohnart 0600 = URLAUB
(
SELECT DISTINCT AE.[Period Date]
,AE.[Correction Date]
,AE.[Creation Date]
,AE.[Employee No_]
,AE.[Pay Type No_]
,AE.[Cause of Absence Code]
,AE.[Description Absence Code]
,''Faktor'' = CASE
WHEN AE.[Cause of Absence Code] = ''URLAUB'' THEN ''1''
ELSE ''0.5''
END
,AE.[From Date]
,AE.[To Date]
,datediff(dd, AE.[From Date], AE.[To Date])+1 AS Tage --wird nicht benötigt
FROM [dbo].[' + @Niederlassung + '$Absence Entry] AS AE
inner join [' + @Niederlassung + '$Employee] AS E on E.[No_]=AE.[Employee No_]
WHERE AE.[Pay Type No_] = 0600
AND AE.[Period Date] >= @Startdatum AND AE.[Period Date] <= @Enddatum
),'
SET @SQLcmd1 = @SQLcmd1 + 'view2_CTE AS -- Feiertage und Wochenende von gebuchten Urlaubstagen abziehen
(
SELECT [From Date]
, [To Date]
, [Faktor]
, [Employee No_]
, CAST(dbo.fkt_datum_HolenAnzahlArbeitstage([From Date], [To Date]) * CONVERT(float, [Faktor]) AS float) as arbeitstage
, (select count(*) from [' + @Niederlassung + '$Statut_ Holiday Calendar Line]
where [Statut_ Holiday Calendar Code] = E.[National Holiday Calendar Code]
and [Date]>=[From Date]
and Date<=[To Date]) AS Feiertage
FROM view1_CTE
inner join [' + @Niederlassung + '$Employee] AS E on E.[No_]=[Employee No_]
),'
SET @SQLcmd1 = @SQLcmd1 + 'view3_CTE AS -- Gebuchte genommene Urlaubstage berechnen
(
SELECT [Employee No_]
, SUM(CAST(arbeitstage AS float) - CAST(Feiertage AS float)) AS [Verbraucht]
FROM view2_CTE
GROUP BY [Employee No_]
),'
SET @SQLcmd1 = @SQLcmd1 + 'view4_CTE AS --ungebuchte Urlaubstage Liste
(
SELECT [Employee No_]
,[From Date]
,[To Date]
,CAST(dbo.fkt_datum_HolenAnzahlArbeitstage([From Date], [To Date]) AS float) as [ungebuchte_arbeitstage]
, (select count(*) from [' + @Niederlassung + '$Statut_ Holiday Calendar Line] --Feiertage
where [Statut_ Holiday Calendar Code] = E.[National Holiday Calendar Code]
and [Date]>=[From Date]
and Date<=[To Date]) AS Feiertage
FROM [dbo].[' + @Niederlassung + '$Employee Absence]
inner join [' + @Niederlassung + '$Employee] AS E on E.[No_]=[Employee No_]
WHERE [Cause of Absence Code] = ''URLAUB'' --kein Code nur Varchar Beschr. in Tabelle
),'
SET @SQLcmd1 = @SQLcmd1 + 'view5_CTE AS --ungebuchte Urlaubstage per Mitarbeiter -Feiertage
(
SELECT [Employee No_], SUM([ungebuchte_arbeitstage] - [Feiertage]) AS [Ungebuchte Urlaubstage]
FROM view4_CTE
GROUP BY [Employee No_]
),'
SET @SQLcmd1 = @SQLcmd1 + 'view6_CTE AS --korrekturliste gebuchte Einträge ("Von Datum" > 1753!)
(
SELECT [Employee No_]
,[From Date]
,[To Date]
,CAST(dbo.fkt_datum_HolenAnzahlArbeitstage([From Date], [To Date]) AS float) as [gebuchte_korrekturtage]
, (select count(*) from [' + @Niederlassung + '$Statut_ Holiday Calendar Line] --Feiertage
where [Statut_ Holiday Calendar Code] = E.[National Holiday Calendar Code]
and [Date]>=[From Date]
and Date<=[To Date]) AS Feiertage
FROM [dbo].[' + @Niederlassung + '$Absence Corr_ Reg_ Jnl_ Line]
inner join [' + @Niederlassung + '$Employee] AS E on E.[No_]=[Employee No_]
WHERE [Cause of Absence Code] = ''URLAUB'' AND
[From Date] > ''1753-01-01 00:00:00''
),'
SET @SQLcmd1 = @SQLcmd1 + 'view7_CTE AS -- korrekturliste per Mitarbeiter - Feiertage
(
SELECT [Employee No_],
SUM([gebuchte_korrekturtage] - [Feiertage]) AS [nachzubuchende Urlaubstage]
FROM view6_CTE
GROUP BY [Employee No_]
),'
SET @SQLcmd1 = @SQLcmd1 + 'view8_CTE AS --korrekturliste abzuziehende Einträge ("Von Datum" leer!) z.b. Krankheit während Urlaub
(
SELECT [Employee No_], count(*) AS [Korrekturbuchung]
FROM [dbo].[' + @Niederlassung + '$Absence Corr_ Reg_ Jnl_ Line]
WHERE [Cause of Absence Code] = ''URLAUB''
AND [From Date] = ''1753-01-01 00:00:00''
GROUP BY [Employee No_]
)'
--SELECT * FROM view3_CTE
-- Recordest für Report aufbereiten, Urlaubsanspruch per Mitarbeiter
SET @SQLcmd1 = @SQLcmd1 + 'SELECT jnl.[Employee No_] AS [Mitarbeiternr_]
, res.[Name], CONVERT(float, LEFT(SUM(jnl.[Days]), 4)) AS [Urlaubsanspruch]
, v.[Verbraucht]
, u.[Ungebuchte Urlaubstage]
, n.[nachzubuchende Urlaubstage]
, k.[Korrekturbuchung]
FROM [dbo].[' + @Niederlassung + '$Recurring Reg_ Journal Line] AS jnl
INNER JOIN [dbo].[Resource] AS res ON jnl.[Employee No_] = res.[No_]
LEFT JOIN view3_CTE as v ON res.[No_] = v.[Employee No_] --gebuchte Urlaubstage
LEFT JOIN view5_CTE as u ON res.[No_] = u.[Employee No_] --ungebuchte Urlaubstage
LEFT JOIN view7_CTE as n ON res.[No_] = n.[Employee No_] --nachzubuchende Urlaubstage (Korrekturliste)
LEFT JOIN view8_CTE as k ON res.[No_] = k.[Employee No_] --Korrekturbuchungen (Gutschrift)
WHERE [Pay Type No_] = 0500
GROUP BY jnl.[Employee No_]
,res.[Name],v.[Verbraucht]
,u.[Ungebuchte Urlaubstage]
,n.[nachzubuchende Urlaubstage]
,k.[Korrekturbuchung]'
EXECUTE sp_executesql @SQLcmd1, N'@Startdatum SMALLDATETIME, @Enddatum SMALLDATETIME', @Startdatum = @OStartdatum, @Enddatum = @OEnddatum
END
February 13, 2012 at 3:25 am
Thank you very much, works like a charme for me.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy