parametrized query errors

Question

Post reply

parametrized query errors

Roust_m

SSCoach

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

#192524

Hi,

I am trying to build the following query:

declare @S 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= 'select CompanyName from [@Dbname].dbo.company (nolock)'

EXEC sp_executesql @S, 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

Nikhil Shikarkhane

Default port

Points: 1498

October 23, 2008 at 6:17 pm

#888968

@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: 17402 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 = N'', you just put SET @S = ''...

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_INT	as int

declare @TBL_IN	as nvarchar(40)

declare @SQL	as nvarchar(100)

declare @param	as 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: 1000539 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)
Intro to Tally Tables and Functions

Roust_m SSCoach Points: 17402 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 = N'', you just put SET @S = ''...

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