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

SP Question

Author

Topic

cbruton
Starting Member

1 Post

Posted - 2006-06-01 : 09:46:32

I've done this before but can't seem to remember how I did it. What i'm trying to do is use an input parameter to specify the database of the table i'm querying from. This way the sp can be used across databases. Here's the code i have....

CREATE PROCEDURE dbo.SP_Name @databaseName varchar(100), @MyTableName varchar (100) as

SELECT cast(syscolumns.name as varchar(75))as ColumnName, cast(syscolumns.colid as int(75))ColumnId
Into #lookup
FROM @databaseName + '..' + syscolumns inner join @databaseName + '..' + sysobjects on syscolumns.id = sysobjects.id
WHERE sysobjects.name = @MyTableName
ORDER BY syscolumns.colorder

The @databaseName is the piece i'm struggling with. Any help would be much appreciated.

Chad

nr
SQLTeam MVY

12543 Posts

Posted - 2006-06-01 : 09:49:04

You have to do it using dynamic sql.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-06-01 : 10:07:08

Dynamic SQL :
--Define a varchar variable
Declare sSQL varchar(2000)

--Assign the Constants & variables to that variable
Set sSQL = 'Select a,b,c from ' + @databaseName + '..' + syscolumns ....

--Execute that
Exec (sSQL)

Srinika

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-01 : 10:16:29

sSQL should be @sSQL