Tbale names in stored procedures

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

SQL Server 2000 Forums

SQL Server Development (2000)

Tbale names in stored procedures

Author

Topic

archna
Starting Member

2 Posts

Posted - 2005-02-02 : 00:33:59

I want to use table name and field name as a paramter into stored procedure in select statement. How i can do that?

vganesh76
Yak Posting Veteran

64 Posts

Posted - 2005-02-02 : 01:10:42

Try using dynamic sql. Here is a sample code to perform that
-------------------------------------------------------------
Create procedure ExecProc(@strtable varchar(50),@fieldname varchar(200))
as
begin
Declare @sql varchar(800)
set @sql = 'select ' + @fieldname + ' from ' + @strtable
execute (@sql )
end

-------------------------------------------------------------
--------Code to execute the same-------------------
use pubs ---- I have used pubs to execute this procedure
exec ExecProc 'authors','au_id,au_lname'

Enjoy working

archna
Starting Member

2 Posts

Posted - 2005-02-02 : 01:44:10

But i want to select values in local variable based upon the where conditions.SOmething like that
@tempId = 'Select ' + @FieldName + 'From' + @Tablename + 'Where' + @TableNAme + '=' + @Count

@Count is another variable.

Please provide solution in this direction.

SqlStar
Posting Yak Master

121 Posts

Posted - 2005-02-02 : 02:11:27

quote:
@Count is another variable.

Hi,

See the following example:

Declare @a int
declare @str varchar(100)

set @str = 'Select @a=Count(au_id) from Authors'

Exec(@str)

It will give an error message like "Must declare the variable '@a'."

If you want to execute like this, you can't do. You have to create a dummy table for this "Count" result storage and get from that dummy table then use as per your requirement.

:) While we stop to think, we often miss our opportunity :)

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-02-02 : 02:56:48

I would NOT recommend doing this for security reasons and certainly wouldnt give this as a solution

quote:

exec ExecProc 'authors DROP TABLE authors','au_id,au_lname'

Nice one!!!!!!

What are you trying to do?

vganesh76
Yak Posting Veteran

64 Posts

Posted - 2005-02-02 : 07:38:30

Try this,
Declare @a int
declare @str varchar(100)
set @str = 'Declare @a int Select @a=Count(au_id) from Authors select @a a'

Exec(@str)

Enjoy working

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources