Post reply

Dynamic SQL Queries

  • November 19, 2003 at 1:48 am

    #83558

    Can anyone help me please. I am trying to use a variable for the db name I am trying to query. This is because I have various databases that are copied every month and renamed so the name includes month and year in the name. I then want to do comparions between this months an d last month data etc.

    The basics of what I am trying to do goes like this:

    declare @DBNAME varchar(128)

    set @DBNAME = 'Jan2003Contracts'

    select * from + @DBNAME

    The @dbname would be made up from date and string etc but I can't get the basics to work!

    I must be doing something stupid! Please help

    Thanks

    Karl

  • November 19, 2003 at 1:52 am

    #482568

    You would need to use dynamic SQL to do this.

    declare @sqlstring nvarchar(1000)

    set @sqlstring = N'select * from ' + @DBName

    exec sp_executesql @sqlstring

    You might need to set SELECT permission on the tables in the new database.

    Jeremy

  • November 19, 2003 at 1:54 am

    #482569

    Try using sp_executeSQL. See BOL for further details

    ..and as always http://www.algonet.se/~sommar/dynamic_sql.html suggested for further reading

    Frank

    http://www.insidesql.de

    http://www.familienzirkus.de

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • November 19, 2003 at 1:55 am

    #482570

    Hey Jeremy, you beat me by almost 2 minutes

    Frank

    http://www.insidesql.de

    http://www.familienzirkus.de

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • November 19, 2003 at 1:58 am

    #482571

    Thanks for the info, I will have a try and let you know how I get on

    Thanks

    Karl

  • November 19, 2003 at 2:13 am

    #482572

    Frank,

    Just quick of the blocks this time.

    Jeremy

  • November 20, 2003 at 2:22 am

    #482801

    quote:

    set @sqlstring = N'select * from ' + @DBName

    Silly question, but what's the significance of the "N" before the select (N'Select)?

  • November 20, 2003 at 2:31 am

    #482803

    N' turns it into unicode.

    Frank

    http://www.insidesql.de

    http://www.familienzirkus.de

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • November 20, 2003 at 2:33 am

    #482804

    Is that a recommended practice?

  • November 20, 2003 at 2:59 am

    #482808

    I don't know whether it's a recommended practice or not, but sp_executesql requires a Unicode constant or variable. If a constant is specified, it has to be prefixed with N, hence why the N prefix is used in this example.


    Cheers,
    mia

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply