parametrized query errors

Question

Post reply

parametrized query errors

Roust_m

SSCoach

Points: 17410
More actions
October 23, 2008 at 5:46 pm

#192524

Hi,
I am trying to build the following query:
declare @s-2 nvarchar(500)
declare @Dbname nvarchar(50)
declare @dbs table (Dbname sysname)
insert into @dbs select name from master.dbo.sysdatabases (nolock) where [name] LIKE 'MyDB__'
set @Dbname = (select min(Dbname) from @dbs)
while @Dbname is not null
begin
SELECT @s-2= 'select CompanyName from [@Dbname].dbo.company (nolock)'
EXEC sp_executesql @s-2, N'@Dbname nvarchar(50)', @Dbname=@Dbname
set @Dbname = (select min(Dbname) from @dbs where Dbname > @Dbname)
end
but it returns this error:
Msg 208, Level 16, State 1, Line 1
Invalid object name '@Dbname.dbo.company'.
Any ideas?
Thanks.

Viewing 6 posts - 1 through 5 (of 5 total)

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

Nikhil Shikarkhane Default port Points: 1498 More actions · Answer 1

@tablename is a local table variable.

When you are executing the dynamic sql, its not able to access it ( out of scope )

Use instead temporary ( #tablename ) or global temporary ( ##tablename )tables.

PLease refer Books Online, to check on the scope of the respective temp tables.

Roust_m SSCoach Points: 17410 More actions · Answer 2

This code comes from BOL. @IntVariable is a local variable. So what is different in my code?

DECLARE @IntVariable INT;

DECLARE @SQLString NVARCHAR(500);

DECLARE @ParmDefinition NVARCHAR(500);

/* Build the SQL string one time. */

SET @SQLString =

N'SELECT * FROM AdventureWorks.Sales.Store WHERE SalesPersonID = @SalesID';

/* Specify the parameter format one time. */

SET @ParmDefinition = N'@SalesID int';

/* Execute the string with the first parameter value. */

SET @IntVariable = 275;

EXECUTE sp_executesql @SQLString, @ParmDefinition,

@SalesID = @IntVariable;

/* Execute the same string with the second parameter value. */

SET @IntVariable = 276;

EXECUTE sp_executesql @SQLString, @ParmDefinition,

@SalesID = @IntVariable;

jsteinbeck-618119 Default port Points: 1408 More actions · Answer 3

I've been using sp_executesql and found that if I try to pass in the table name it never works... I think it's comparable to early binding or somthing...

I put in some code you can play with....

The other thing I noticed is you didn't put SET @s-2 = N'', you just put SET @s-2 = ''...

Also I didn't see where you did any Parameter definitions for the Dynamic SQL that sp_executesql is expecting... see code below...

Oh, you can do this...set @sql = N'SELECT T.N FROM ' + @TBL_IN + ' T WHERE T.N <=@N_IN';

Hope this helps a bit...

declare @N_INTas int

declare @TBL_INas nvarchar(40)

declare @sqlas nvarchar(100)

declare @paramas nvarchar(100)

set @sql = N'SELECT T.N FROM @TBL_IN T WHERE T.N <=@N_IN';

SET @PARAM = N'@TBL_IN NVARCHAR(40), @N_IN INT';

EXECUTE sp_executesql @sql, @PARAM

, @TBL_IN = 'DBO.TALLY' -- OTHER.

, @N_IN = 3

set @sql = N'SELECT T.N FROM DBO.TALLY T WHERE T.N <=@N_IN';

SET @PARAM = N'@N_IN INT';

EXECUTE sp_executesql @sql, @PARAM

, @N_IN = 3

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 4

Nikhil Shikarkhane (10/23/2008)
@tablename is a local table variable.

No it's not... it's the name of a database... and it's @DBName... 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Roust_m SSCoach Points: 17410 More actions · Answer 5

jsteinbeck (10/23/2008)
I've been using sp_executesql and found that if I try to pass in the table name it never works... I think it's comparable to early binding or somthing...
I put in some code you can play with....
The other thing I noticed is you didn't put SET @s-2 = N'', you just put SET @s-2 = ''...
Also I didn't see where you did any Parameter definitions for the Dynamic SQL that sp_executesql is expecting... see code below...
Oh, you can do this...set @sql = N'SELECT T.N FROM ' + @TBL_IN + ' T WHERE T.N <=@N_IN';
Hope this helps a bit...

I am trying to pass the database name. I've created my script based on this example, where the parameters are described inside the 'sp_executesql' statement:

CREATE PROCEDURE general_select1 @tblname sysname,

@key varchar(10) AS

DECLARE @sql nvarchar(4000)

SELECT @sql = ' SELECT col1, col2, col3 ' +

' FROM dbo.' + quotename(@tblname) +

' WHERE keycol = @key'

EXEC sp_executesql @sql, N'@key varchar(10)', @key

I've corrected the 'set @s-2' command, but this did not help.

Yep, dynamic sql was my initial version, but I was just wondering if I could re-write using parametrized query. Apparently I can't...

Thanks anyway.