whats wrong with this script

Question

Post reply

whats wrong with this script

ckmoied

Ten Centuries

Points: 1092
More actions
September 25, 2007 at 2:25 am

#175148

whats wrong with the following script

declare @TableName nvarchar(50)

set @TableName = 'stg.dbo.Disposal'

select count(*) from @TableName

go

why do I get the following error

Server: Msg 137, Level 15, State 2, Line 3

Must declare the variable '@TableName'.

Viewing 8 posts - 1 through 7 (of 7 total)

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

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 1

If you take a look at the FROM clause in Books Online, you'll see that using a variable is NOT one of the options for a table name... in order to do this, you'll need to use Dynamic SQL... for example...

--===== From your original code...



DECLARE @TableName NVARCHAR(50)

SET @TableName = 'stg.dbo.Disposal'

--===== Create the required dynamic SQL

DECLARE @SQL VARCHAR(8000)

SET @SQL = 'SELECT COUNT * FROM ' + @TableName

--===== Execute the dynamic SQL

EXEC (@SQL)

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

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 2

HI There,

You can't "Select * from" nvarchar.

You need to use a table or table variable.

What is it that you are trying to do?

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

Phil Factor SSC-Insane Points: 20224 More actions · Answer 3

declare @TableName nvarchar(50)

set @TableName = 'stg.dbo.Disposal'

select count(*) from @TableName

go

In the first case you declare a nvarchar called @Tablename and then you treat it as though it were a table variable. SQL Server assumes you haven't declared the table called @Tablename, because you haven't, have you?

To do what you want to do, you'd need to do

execute ('select count(*) from '+ @TableName)

..but then, you want the result in a variable so you'd have to use sp_ExecuteSQL and have an output variable.

See http://www.simple-talk.com/sql/learn-sql-server/pop-rivetts-sql-server-faq/

Best wishes,
Phil Factor

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

Heh... now that's funny. Near his avatar, it says that Phil Factor is a "Rookie" 😀

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

ckmoied Ten Centuries Points: 1092 More actions · Answer 5

Thanks Phil,

worked great.............good learning

Just another question guys

How can I put the output from two select statements in one row.

Like

Select count(*) from A as 'Rows from A'

Select count(*) from B as 'Rows from B'

gives me

Rows from A

111

Rows from B

222

I want it like

Rows from A Rows from B

111 222

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 6

SELECT



(SELECT COUNT(*) FROM A) AS [Count From A],

(SELECT COUNT(*) FROM B) AS [Count From B]

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

Karl Hewlett Mr or Mrs. 500 Points: 528 More actions · Answer 7

Hi

If you only want a single row this is easily acheived with a symntax like

select max(a) a, max(b) b

from (

select a, '' b from a

union

select '', b from b) abc

To try the above example us this to set up

create table a

(a varchar(5))

create table b

(b varchar(5))

insert into a values ('abcde')

insert into b values ('vwxyz')

If you wnat multiple row the approach is similar but you cannot use the max(column) approach. However in my experience it is generally less valid to do this with mulitple rows as you then run into issues with matching the rows to display.

Hope this helps

Karl