Results 1 to 11 of 11
  1. #1
    Join Date
    May 2003
    Location
    Atlanta, GA
    Posts
    187

    Unanswered: Help with Dynamic Table Names

    Hi all,
    I hope what I'm trying to do is possible. Please help.
    I am trying to make a dynamic stored procedure to select from a table that I pass into sp.
    What happens is I have tables for each company by Company ID:
    Ex: tbl342AccessorialData
    tbl398AccessorialData

    I need to be able to select from a table by passing in the id.

    Something like this.

    CREATE PROCEDURE sp_test
    (
    @tablenum varchar(50)
    )
    AS
    BEGIN

    DECLARE @tablename varchar(50)
    SET @tablename = 'tbl' + @tablenum + 'AccessorialData'

    SELECT * FROM @tablename

    END
    GO


    Any idea on how to make it work?

    Thanks in advance.

  2. #2
    Join Date
    Sep 2003
    Location
    Dallas, Texas
    Posts
    311

    Re: Help with Dynamic Table Names

    Try this,

    CREATE PROCEDURE sp_test
    (
    @tablenum varchar(50)
    )
    AS
    BEGIN

    DECLARE @tablename varchar(50), @sqlstmt varchar(1000)
    SET @tablename = 'tbl' + @tablenum + 'AccessorialData'
    set @sqlstmt = 'SELECT * FROM ' + @tablename
    exec(@sqlstmt)

    END
    GO




    Thanks in advance. [/SIZE][/QUOTE]

  3. #3
    Join Date
    Sep 2003
    Posts
    212
    use this:

    exec('SELECT * FROM '+@tablename )

  4. #4
    Join Date
    May 2003
    Location
    Atlanta, GA
    Posts
    187
    Cool guys this will work, but running into one problem. In the WHERE clause, I have strings to compare. How would I put a single quate in a single quate to search the string in the statement?
    This is the SP.

    CREATE PROCEDURE sp_InboundByService
    (
    @startdate datetime,
    @enddate datetime,
    @Parent int,
    @Root int
    )
    AS
    BEGIN
    SET NOCOUNT ON

    DECLARE @INOUT varchar(10)
    SET @INOUT = 'inbound'

    DECLARE @OrgLvl varchar(15)
    SET @OrgLvl = 'Shipper Number'

    DECLARE @ReportData varchar(50)
    SET @ReportData = 'tbl' + STR(@Root) + 'ReportData'

    DECLARE @SQLStr varchar(1000)
    SET @SQLStr = 'SELECT tblLegendServiceLevel.ServiceLevel, ' +
    'tblLegendServiceLevel.ServiceLevelCode, ' +
    'COUNT(' + @ReportData + '.ShipGrossCharge) AS NumOfPackages, ' +
    'SUM(' + @ReportData + '.Weight) AS TotalWeight, ' +
    'SUM(' + @ReportData + '.AccessorialTotal) AS TotalAccess, ' +
    'SUM(' + @ReportData + '.Incentive) AS TotalIncentive, ' +
    'SUM(' + @ReportData + '.NetCharge) AS TotalNetCharge ' +
    'FROM ' + @ReportData + ' ' +
    'INNER JOIN tblLegendLading ON ' + @ReportData + '.LadingCode = tblLegendLading.LadingCode ' +
    'INNER JOIN tblLegendServiceLevel ON ' + @ReportData + '.ServiceStandard = tblLegendServiceLevel.ServiceStandard ' +
    'INNER JOIN tblShipperNumberLookUp AS LookUp ON ' + @ReportData + '.ShipperNum = LookUp.ShipperNumber ' +
    'INNER JOIN tblOrg_Unit ON LookUp.OU_ID = tblOrg_Unit.OU_ID ' +
    'INNER JOIN tblOrg_Unit_Hier ON tblOrg_Unit.OU_ID = tblOrg_Unit_Hier.child ' +
    'INNER JOIN tblOrg_lvls ON tblOrg_Unit_Hier.child_level = tblOrg_lvls.OrgLvl ' +
    'WHERE (' + @ReportData + '.InvoiceDate BETWEEN ' + @startdate + ' AND ' + @enddate + ') AND ' +
    '(tblLegendLading.LadingType = ' + @INOUT + ') AND' +
    '(tblOrg_Unit_Hier.parent = ' + @Parent + ') AND ' +
    '(tblOrg_lvls.Root = ' + @Root + ') AND ' +
    '(tblOrg_lvls.[Name] = ' + @OrgLvl + ') ' +
    'GROUP BY tblLegendServiceLevel.ServiceLevel,
    tblLegendServiceLevel.ServiceLevelCode ' +
    'ORDER BY tblLegendServiceLevel.ServiceLevel, tblLegendServiceLevel.ServiceLevelCode'

    EXEC (@SQLStr)


    END
    GO

  5. #5
    Join Date
    Sep 2003
    Location
    Dallas, Texas
    Posts
    311
    Try this,

    set @sqlstmt = 'select * from ' + @databasename + '..sysobjects where name = ' + '''table1'''

  6. #6
    Join Date
    May 2003
    Location
    Atlanta, GA
    Posts
    187
    I am having problem with, if you look at the sp, the @startdate and the @enddate are dates that get passed into the sp. Also @INOUT and the @OrgLvl are strings that need to be in single quotes. In sql string I got to put them into single quotes to make it work I assume. The where clouse is :
    'WHERE (' + @ReportData + '.InvoiceDate BETWEEN ' + @startdate + ' AND ' + @enddate + ') AND ' +
    '(tblLegendLading.LadingType = ' + @INOUT + ') AND' +
    '(tblOrg_Unit_Hier.parent = ' + @Parent + ') AND ' +
    '(tblOrg_lvls.Root = ' + @Root + ') AND ' +
    '(tblOrg_lvls.[Name] = ' + @OrgLvl + ') ' +

    How would I do that?

  7. #7
    Join Date
    Sep 2003
    Posts
    212
    Originally posted by bpolunin
    I am having problem with, if you look at the sp, the @startdate and the @enddate are dates that get passed into the sp. Also @INOUT and the @OrgLvl are strings that need to be in single quotes. In sql string I got to put them into single quotes to make it work I assume. The where clouse is :
    'WHERE (' + @ReportData + '.InvoiceDate BETWEEN ' + @startdate + ' AND ' + @enddate + ') AND ' +
    '(tblLegendLading.LadingType = ' + @INOUT + ') AND' +
    '(tblOrg_Unit_Hier.parent = ' + @Parent + ') AND ' +
    '(tblOrg_lvls.Root = ' + @Root + ') AND ' +
    '(tblOrg_lvls.[Name] = ' + @OrgLvl + ') ' +

    How would I do that?
    the same way as joejcheng mentioned b4

  8. #8
    Join Date
    May 2003
    Location
    Atlanta, GA
    Posts
    187
    When I put Double quote single quote @startdate single quote double quote I get INVALID COLUMN NAME @startdate. Its not a column, it's what get passed into sp.
    Or could I say something like in vb CHR(39) for the single quote.
    ????????????
    Last edited by bpolunin; 11-05-03 at 13:18.

  9. #9
    Join Date
    Nov 2003
    Posts
    2
    it's not double qoute, single quote, but rather 3 single quotes.

    Two single quotes together will represent one single quote.

    For example:

    set @sqlstmt = @sqlstmt + ' WHERE [date] BETWEEN ''' + @startdate + ''' and ''' + @enddate + '''

    all single quotes

  10. #10
    Join Date
    May 2003
    Location
    Atlanta, GA
    Posts
    187
    That works, but now I get this error.
    Server: Msg 241, Level 16, State 1, Procedure sp_InboundByService, Line 22
    Syntax error converting datetime from character string.

    Is it because I have @startdate in single quotes? But you need to out the date field in single quotes. Why do I get this error?

  11. #11
    Join Date
    May 2003
    Location
    Atlanta, GA
    Posts
    187
    OK the previous error is fixed, althogh I don't know why it didn't work before. But now I get error:
    Server: Msg 245, Level 16, State 1, Procedure sp_InboundByService, Line 22
    Syntax error converting the varchar value 'SELECT tblLegendServiceLevel.ServiceLevel, tblLegendServiceLevel.ServiceLevelCode, COUNT (tbl1ReportData.ShipGrossCharge) AS NumOfPackages, SUM(tbl1ReportData.Weight) AS TotalWeight, SUM(tbl1ReportData.AccessorialTotal) AS TotalAccess, SUM(tbl1ReportData.Incentive) AS TotalIncentive, SUM(tbl1ReportData.NetCharge) AS TotalNetCharge FROM tbl1ReportData ...

    I noticed if I hard code the @Parent and the @Root to 1's it works.
    My SP looks like this now. Please help.

    CREATE PROCEDURE sp_InboundByService
    (
    @startdate varchar(15),
    @enddate varchar(15),
    @Parent int,
    @Root int
    )
    AS
    BEGIN
    --SET NOCOUNT ON

    DECLARE @INOUT varchar(10)
    SET @INOUT = 'inbound'

    DECLARE @OrgLvl varchar(15)
    SET @OrgLvl = 'Shipper Number'

    DECLARE @ReportData varchar(50)
    SET @ReportData = 'tbl' + CAST(@Root AS varchar) + 'ReportData'

    DECLARE @SQLStr varchar(4000)
    SET @SQLStr = 'SELECT tblLegendServiceLevel.ServiceLevel, ' +
    'tblLegendServiceLevel.ServiceLevelCode, ' +
    'COUNT (' + @ReportData + '.ShipGrossCharge) AS NumOfPackages, ' +
    'SUM(' + @ReportData + '.Weight) AS TotalWeight, ' +
    'SUM(' + @ReportData + '.AccessorialTotal) AS TotalAccess, ' +
    'SUM(' + @ReportData + '.Incentive) AS TotalIncentive, ' +
    'SUM(' + @ReportData + '.NetCharge) AS TotalNetCharge ' +
    'FROM ' + @ReportData +
    ' INNER JOIN tblLegendLading ON ' + @ReportData + '.LadingCode = tblLegendLading.LadingCode ' +
    'INNER JOIN tblLegendServiceLevel ON ' + @ReportData + '.ServiceStandard = tblLegendServiceLevel.ServiceStandard ' +
    'INNER JOIN tblShipperNumberLookUp AS LookUp ON ' + @ReportData + '.ShipperNum = LookUp.ShipperNumber ' +
    'INNER JOIN tblOrg_Unit ON LookUp.OU_ID = tblOrg_Unit.OU_ID ' +
    'INNER JOIN tblOrg_Unit_Hier ON tblOrg_Unit.OU_ID = tblOrg_Unit_Hier.child ' +
    'INNER JOIN tblOrg_lvls ON tblOrg_Unit_Hier.child_level = tblOrg_lvls.OrgLvl ' +
    'WHERE (' + @ReportData + '.InvoiceDate BETWEEN ''' + @startdate + ''' AND ''' + @enddate + ''') AND ' +
    '(tblLegendLading.LadingType = ''' + @INOUT + ''') AND ' +
    '(tblOrg_Unit_Hier.parent = ' + @Parent + ') AND ' +
    '(tblOrg_lvls.Root = ' + @Root + ') AND ' +
    '(tblOrg_lvls.[Name] = ''' + @OrgLvl + ''') ' +
    'GROUP BY tblLegendServiceLevel.ServiceLevel, tblLegendServiceLevel.ServiceLevelCode ' +
    'ORDER BY tblLegendServiceLevel.ServiceLevel, tblLegendServiceLevel.ServiceLevelCode'
    END

    EXEC (@SQLStr)
    GO


    Is there a way to see what @SQLStr is?
    Last edited by bpolunin; 11-05-03 at 15:30.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •