Dynamic SQL Queries

Question

Post reply

Dynamic SQL Queries

kpspencer

Mr or Mrs. 500

Points: 586
More actions
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

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

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

Jeremy Kemp SSCertifiable Points: 6754 More actions · Answer 1

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

Frank Kalis SSC Guru Points: 111183 More actions · Answer 2

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]

Frank Kalis SSC Guru Points: 111183 More actions · Answer 3

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]

kpspencer Mr or Mrs. 500 Points: 586 More actions · Answer 4

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

Thanks

Karl

Jeremy Kemp SSCertifiable Points: 6754 More actions · Answer 5

Jeremy Kemp

SSCertifiable

Points: 6754

November 19, 2003 at 2:13 am

#482572

Frank,

Just quick of the blocks this time.

Jeremy

Tim-24860 Mr or Mrs. 500 Points: 578 More actions · Answer 6

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

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

Frank Kalis SSC Guru Points: 111183 More actions · Answer 7

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]

Tim-24860 Mr or Mrs. 500 Points: 578 More actions · Answer 8

Tim-24860

Mr or Mrs. 500

Points: 578

November 20, 2003 at 2:33 am

#482804

Is that a recommended practice?

mia Right there with Babe Points: 749 More actions · Answer 9

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.