building a select query in a stored procedure
-
I have a select query where I need to change the name of the database.
eg.
select master..sysxlogins.name ,
<<dbname>>..sysusers.name as user
How am I able to do this in a stored procedure?
Edited by - growl on 08/31/2003 11:41:13 PM
-
Try this example
create procedure p @p1 varchar(100)
as
exec('select * from ' + @p1 + '.dbo.sysobjects')
Pay Respect to People on your way up. For you will meet the same People on your way down.
He who knows others is learned but the wise one is one who knows himself. -
If you use the Exec command with a parameter, you may wish to validate the input for sql injection
e.g.
exec p 'master..syslogins ; drop procedure p--'
Steven
-
quote:
If you use the Exec command with a parameter, you may wish to validate the input for sql injectione.g.
exec p 'master..syslogins ; drop procedure p--'
in addition to this:
I would use sp_executeSQL rather than EXEC.
How many database are there? I mean, I'm not sure, if I would code this dynamically or if I would evaluate some input parameter in a CASE structure with hard coded access to the different db's.
Is this for administration purposes or a feature in some application?
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Fully Agree to what steven and frank says.
Pay Respect to People on your way up. For you will meet the same People on your way down.
He who knows others is learned but the wise one is one who knows himself.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply