Global stored procedure (rookie question)

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 2005 Forums

Transact-SQL (2005)

Global stored procedure (rookie question)

Author

Topic

davidmal
Starting Member

19 Posts

Posted - 2007-02-17 : 12:14:51

On a server that has 50+ databases, each one has a 'students' table. I have a script that searches for records with the same first and last name (possible duplicates assigned different PKs). I'm trying to create a global stored procedure in the master DB that accepts the database name as a paramenter.

I tried...
CREATE PROCEDURE PossibleDuplicateStudent

@Database varchar(50)

USE @Database

select t.*
from students t
inner join
(
select fname, lname
from students
group by fname, lname
having count(*) > 1
) d
on t.fname = d.fname
and t.lname = d.lname
END
GO

Will not allow the USE statement so I tried...

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[PossibleDuplicateStudent]

@Database varchar(50)

AS
BEGIN

select t.*
from CONCAT(@Database,".dbo.students") t
inner join
(
select fname, lname
from CONCAT(@Database,".dbo.students")
group by fname, lname
having count(*) > 1
) d
on t.fname = d.fname
and t.lname = d.lname
END
This gives me "Invalid object name 'CONCAT'."

Do I need to cast the concat statement as a DB object somehow? If so how?
Please help.
Thanks

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-02-17 : 12:39:49

read this for explanation of why below code works:
http://weblogs.sqlteam.com/mladenp/archive/2007/01/18/58287.aspx

here you go:


create database t1
go
use t1
create table test1 (col1 varchar(20))
insert into test1 
select 'In t1 DB'

go

create database t2
go
use t2

create table test1 (col1 varchar(20))
insert into test1 
select 'In t2 DB'

go
use master
go
create proc sp_GetTest -- must be prefixed with sp_
as
select * from test1
go
exec sys.sp_MS_marksystemobject sp_GetTest

go
use t1
exec sp_GetTest
go

use t2
exec sp_GetTest

go
use master

drop database t1
drop database t2

drop proc sp_GetTest

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

davidmal
Starting Member

19 Posts

Posted - 2007-02-17 : 12:54:02

Thanks for the reply and I looked over the link. This still will not accomplish what I'm attempting to do. I want to be able to open a query window and just enter
EXEC sp_PossibleDuplicateStudents DBName and have the procedure run the query in the DB specified in the parameter.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-02-17 : 12:59:36

then maybe you don't understand how this works.

by doing this you don't have to pass in the database name.
by marking the sproc in master as system object you execute it in the current database's context.
the only requirement is that you have the table with the same name and structure in all databases.

so you have to do:
use dbName
exec sp_YourSproc

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

davidmal
Starting Member

19 Posts

Posted - 2007-02-17 : 13:10:15

Ok I get it now

Thanks spirit1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources