Stored Proc passing Table Name Fails - Help

Question

Post reply

Stored Proc passing Table Name Fails - Help

bshulam

SSC-Addicted

Points: 406
More actions
December 26, 2007 at 11:19 am

#377742

I created the following Stored Procedure however when I try to Exec and pass a value for the table name it says Invalid Object Name '@CheckTable'
Msg 208, Level 16, State 1, Procedure Invalid object name '@CheckTable'.
Exec sp_vcGetStop 'CHECKS_DEC2007','3300000001'
CREATE PROCEDURE [dbo].[sp_vcGetStop]
@CheckTable varchar(15),
@Sequence numeric(10,0)
AS
BEGIN
SELECT CUID As 'Rt', WorkType
FROM [@CheckTable]
WHERE Sequence = @Sequence
END

Viewing 9 posts - 1 through 9 (of 9 total)

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

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

You must use Dynamic SQL to do such a thing...

CREATE PROCEDURE [dbo].[sp_vcGetStop]

@CheckTable varchar(15),

@Sequence numeric(10,0)

AS

EXEC ('SELECT CUID As Rt, WorkType FROM ' + @CheckTable + ' WHERE Sequence = ' + CAST(@Sequence AS VARCHAR(10)))

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

bshulam SSC-Addicted Points: 406 More actions · Answer 2

bshulam

SSC-Addicted

Points: 406

December 26, 2007 at 12:14 pm

#762773

Thanks that worked.

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 3

You're welcome and thank you for the feedback.

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

Mauve SSChampion Points: 11316 More actions · Answer 4

Use the built-in stored procedure sp_executesql vs. just plain EXECUTE. Then you can pass your variable as a parameter without having to cast it as a string.

Additional benefits:

- reusable query plan

- prevention from SQL injection.

- others

See the Books Online for full details.

[font="Arial Narrow"](PHB) I think we should build an SQL database. (Dilbert) What color do you want that database? (PHB) I think mauve has the most RAM.[/font]

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 5

... and be limited to 4k bytes... 😉

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

h2sut SSC Veteran Points: 241 More actions · Answer 6

Hello: I have a similar question. I have created this SP and i was able to take the database name as a parameter but i cant get the # of backups as a paramter. I have attached my sp. Where i have "select top 3 name..... How would i make the Top 3 a paramter so i could use top1,2,3,4,5 etc. Right now i just limited to top 3 names do i have use some cast if so i not sure how to write the syntax.

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

-- =============================================

-- Author:

-- Create date:

-- Description:

-- =============================================

ALTER PROCEDURE [dbo].[sp_moveBackupTables]

@serverName varchar(50) --Parameter

AS

BEGIN

declare @tableName varchar(50) --Variable

--Declare Cursor

DECLARE backup_Cursor CURSOR FOR

select name from adventureworksdw.dbo.sysobjects

where name like 'MyUsers_backup_%' and xtype = 'U'

and name not in(select top 3 name from adventureworksdw.dbo.sysobjects

where name like 'MyUsers_backup_%' and xtype = 'U' order by name desc)

OPEN backup_Cursor

--Move to initial record in the cursor and set variable(s) to result values

FETCH NEXT FROM backup_Cursor

INTO @tableName

--Loop through cursor records

WHILE @@FETCH_STATUS = 0

BEGIN

--dynamically build create table

Declare @sql varchar(2000)

Set @sql = 'CREATE TABLE ' + @serverName + '.dbo.' + @tableName + '(

[Id] [numeric](18, 0) NOT NULL,

[Field1] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[Field2] [numeric](18, 0) NOT NULL,

[Field3] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[Field4] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[Field5] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

CONSTRAINT [PK_1' + @tableName + '] PRIMARY KEY CLUSTERED

(

[Id] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]'

EXEC(@SQL)

--Insert values into new dynamically created table

Declare @backupTableRecords varchar(1000)

Set @backupTableRecords = 'Insert into ' + @serverName + '.dbo.' + @tableName + ' SELECT * FROM AdventureWorksDW.dbo.'+ @tableName

exec(@backupTableRecords)

--Drop old table

Declare @dropTable varchar(200)

set @dropTable = 'drop table adventureworksdw.dbo.' + @tableName

exec(@dropTable)

--Move to the next record in the Cursor and set variable(s) value(s)

FETCH NEXT FROM backup_Cursor INTO @tableName

END

CLOSE backup_Cursor

DEALLOCATE backup_Cursor

END

Mauve SSChampion Points: 11316 More actions · Answer 7

... and be limited to 4k bytes...

Jeff,

In SQL Server 2005, sp_executesql is NOT limited to 4K (NVARCHAR(4000)) characters. You can use NVARCHAR(MAX).

[font="Arial Narrow"](PHB) I think we should build an SQL database. (Dilbert) What color do you want that database? (PHB) I think mauve has the most RAM.[/font]

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 8

Heh... dang it... I keep forgetting this is an SQL Server 2k5 forum! Thanks John.

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