Post reply

Dynamic Tablename within stored procedure HELP PLS

  • February 10, 2012 at 3:11 am

    #252706

    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

    #1445457

    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

    #1445458

    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

    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • February 13, 2012 at 3:25 am

    #1446141

    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

Cookies on SQLServerCentral

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