Reaching another database using dynamic SQL

[InvisibleDuncan]

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:

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:

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

However, the Execute bit gives me this error:

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...

[digitalShaman]

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

execute sp_executesql @sql

[TysonLPrice]

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

execute sp_executesql @sql

Not true...

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

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

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.

[digitalShaman]

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...

[InvisibleDuncan]

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...

[techgnome]

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