Reaching another database using dynamic SQL

I want to get some data from another database. The name of the other database can change depending on the context, so I figured I'd build the SQL dynamically and execute it. However, it's not doing what I expected. My code is this:

Code:

---

Select  @SQL    =  '
Select      @PolicyName    =  PolicyName
From        ' + @DatabaseName + '..uvwSchemeAdministratorDetails
Where      PolicyNumber    =  ''' + @PolicyNumber + ''''

Select @SQL;

Execute @SQL;

---

The Select statement shows me that the @SQL variable says this:

Code:

---

Select      @PolicyName    =  PolicyName
From        OtherDatabase..uvwSchemeAdministratorDetails
Where      PolicyNumber    =  'MyPolicy'

---

However, the Execute bit gives me this error:

Code:

---

Msg 911, Level 16, State 4, Line 55
Database '
                        Select      @PolicyName    =  PolicyName
                        From        OtherDatabase' does not exist. Make sure that the name is entered correctly.

---

Now, I was expecting to have issues populating my variable using this, but I did think it would at least connect to the database correctly. I'm sure I've made a massively obvious mistake in my syntax - any ideas what it might be?

Thanks...

Execute is for running stored procedures, it won't let you run a Select statement. you would need to run

Code:

---

execute sp_executesql @sql

---

Quote:

---

Originally Posted by digitalShaman

Execute is for running stored procedures, it won't let you run a Select statement. you would need to run

Code:

---

execute sp_executesql @sql

---

---

Not true...

This works on SQL Server 2005 running at 2000 compatibility. I can't speak to later versions.

Code:

---

declare @sql varchar(max)

set @sql = 'select ''See it works!'''

execute (@sql)

---

Edit:

I want to add what I posted isn't the best way to do that and I agree about using sp_executesql. Here is a nice link on it followed by a small synopsis in it.

http://blogs.msdn.com/b/turgays/arch...xecutesql.aspx

Quote:

---

1.sp_executesql allows for statements to be parameterized
◦Therefore It’s more secure than EXEC in terms of SQL injection
2.sp_executesql can leverage cached query plans.
◦The TSQL string is built only one time, after that every time same query is called with sp_executesql, SQL Server retrieves the query plan from cache and reuses it
3.Temp tables created in EXEC can not use temp table caching mechanism

---

I just have an anal need to chirp in when someone states something like "Execute is for running stored procedures, it won't let you run a Select statement" categorically and and isn't true.

you are right, it also works in 2012. i tried the code the OP posted and got the same error while execute sp_executesql @sql worked so got mislead by this. the error message would have made sense when it was looking for db.sp and treated 'select...' as database specifier...

Hi guys,

Thanks for pointing me back towards sp_ExecuteSQL. I had tried that initially but was getting strange errors that made no sense to me. However, my issue actually turned out to be that I had declare my @SQL variable as a VarChar. Declaring it as nVarChar solved the problem.

Cheers...

an advantage of sp_executesp ... you can pass parameters into/out of it as well...

later, when I get a chance, I'll see if I can post a sample of what I mean.

-tg