sql server programming: sp_executesql -- sql server programming

sp_executesql

Elizabeth
3/21/2005 11:17:03 PM

sql server programming:

Hi there,
I am trying to execute sp_executesql while producing the database name
dynamically.
The code look something like that:

CREATE PROCEDURE [sp_dest_comp]
@dest_comp varchar(20),
@product_code varchar(4) Output
AS

Declare @stmt nvarchar(120)
Declare @params nvarchar(100)

Set
@stmt = N'Select Top 1 * From '
+ N'@dest_comp'
+ N'.dbo.products '
+ N'Where product_code = '
+ N'@product_code'

Set
@params = N'@dest_comp varchar(20), '
+ N'@product_code varchar(4)'

Execute sp_executesql
@stmt,
@params = @params,
@dest_comp = @dest_comp,
@product_code = @product_code
GO

I get an error saying:
Line 1: Incorrect syntax near '.'.

What's wrong?

Many thanks in advance
--

RE: sp_executesql

John Bell
3/22/2005 12:17:05 AM

Hi

You are missing the database name in your 4 part name and you can not use a
variable to define the server name try:

ALTER PROCEDURE [sp_dest_comp]
@dest_comp varchar(20),
@product_code varchar(4) Output
AS

Declare @stmt nvarchar(120)
Declare @params nvarchar(100)

Set
@stmt = N'Select Top 1 * From ['
+ @dest_comp
+ N'].Northwind.dbo.products '
+ N'Where ProductID = '
+ N'@product_code'

Set @params = N'@dest_comp varchar(20), '
+ N'@product_code varchar(4)'

Execute sp_executesql
@stmt,
@params = @params,
@dest_comp = @dest_comp,
@product_code = @product_code
GO

John

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server programming group.