procedure error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

procedure error

Author

Topic

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2008-05-13 : 01:28:17

Dear all,
i'm getting error while i'm trying to create the procedure. my aim is while executing the procedure, i should get the records in the table which i enter while executing.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
CREATE PROCEDURE DBO.GET_RECORDS(@TABLE_NAME VARCHAR(100))
AS
SELECT * FROM @TABLE_NAME
go

Vinod
Even you learn 1%, Learn it with 100% confidence.

sunil
Constraint Violating Yak Guru

282 Posts

Posted - 2008-05-13 : 01:37:07

You need to use dynamic sql to accomplish this.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
CREATE PROCEDURE DBO.GET_RECORDS(@TAB_NAME VARCHAR(100))
AS

declare @dynamicSQL varchar(8000)

set @dynamicSQL = 'select * FROM ' + @TAB_NAME
exec(@dynamicSQL);

GO

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-13 : 02:02:19

Why are passing table name as a parameter? Can i ask what your requirement is?

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2008-05-13 : 02:17:55

Dear Visakh,
my requirement is like this

we have around 1400 tables in my database for the application.
we need to check which table behind screen is having the records, and which are not.....

Vinod
Even you learn 1%, Learn it with 100% confidence.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-13 : 02:21:58

quote:
Originally posted by sunsanvin

Dear Visakh,
my requirement is like this

we have around 1400 tables in my database for the application.
we need to check which table behind screen is having the records, and which are not.....

Vinod
Even you learn 1%, Learn it with 100% confidence.

So you will be passing each of 1400 names to sp and executing it?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-05-13 : 02:44:44

Make sure you read this article fully
www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2008-05-13 : 07:03:24

Thankyou very much for your response experts, actually i dont pass all the table names at a time, but at client place, we will check when ever we missed some transactions. suppose the data is missed from 5 tables.(we know the tables based on screens). then for the five tables the developer will check. now i've one more doubt. is it possible to take the @table_name at the below query?
that means my procedure should be like this.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
CREATE PROCEDURE DBO.GET_RECORDS(@TABLE_NAME VARCHAR(100))
AS
SELECT *
FROM TABLE22
WHERE (COLUMN1 IN
(SELECT COLUMN1
FROM table21
WHERE column2 = @TABLE_NAME))

please guide me in this regard.
--the solution given by sunil is worked for me. like the result i've tried with the above query. but i got error. Madhi and sunil,Visakh please help me in this regard.

Vinod
Even you learn 1%, Learn it with 100% confidence.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-05-13 : 09:04:11

CREATE PROCEDURE DBO.GET_RECORDS(@TABLE_NAME VARCHAR(100))
AS
EXEC('SELECT *
FROM TABLE22
WHERE (COLUMN1 IN
(SELECT COLUMN1
FROM table21
WHERE column2 = '+@TABLE_NAME))

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources