create view in a stored procedure - possible? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

create view in a stored procedure - possible?

Author

Topic

fogofogo
Starting Member

11 Posts

Posted - 2008-11-13 : 10:23:13

Hello

I have a table that contains duplicate records. When users query this table, I want to filter out the duplicates. The problem is, I'm not allowed to delete records form the table, so I needed to come up with another way of doing this.

So, I thought maybe I could create a view, delete the duplicates from that, and return the results to the user. Would that be the best way to do it?

In the stored proc I was going to do the following...

1. create a view and select the master table
2. select the new view table
3. remove the dups
4. select the results
5. delete the view

Is it possible to dp all that from 1 stored proc?

Another question... there is only going to be a few people who will use this query, but there is a chance that 2 users will try to use the stored proc/view at the same time. Will this lead to data issues? The reason I ask is, if user1 calls the sp and creates the view, and user2 comes in and calls it again. As both views are the same name, will it still work?

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-13 : 10:26:22

No need of creating view to delete duplicates. just select only unique records using row_number() function available in sql 2005. see 5th point in below link

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/multipurpose-row-number-function.aspx

fogofogo
Starting Member

11 Posts

Posted - 2008-11-13 : 10:27:46

that was quick! Thanks. I'll have a look at that link.

Sorry I should have mentioned. Its a sql 2000 database. This office is stuck in the past!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-13 : 10:29:39

quote:
Originally posted by fogofogo

that was quick!

Sorry I should have mentioned. Its a sql 2000 database. This office is stuck in the past!

Ok. even then no need of view. you can use temporary table with identity column to achieve this. if you can provide some sample duplicate data and explain what unique values you want out of them, i will show you query.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-13 : 10:30:46

Also in future please post questions only in relevant forum. This is 2005 forum. you must have posted this in 2000 forum.

fogofogo
Starting Member

11 Posts

Posted - 2008-11-13 : 10:36:47

Thanks again

Heres some sample data

ID.....Fname.....Lname.....Add1
1 .....James.....Brown ....21 GetDown St
2 .....John .....Cash .....14 Jacksonville
3 .....J.........Brown ....21 GetDown St
4 .....Micky ....Jackson ..Shamone Road
5 .....Dolly ... Parton ...Cannes, France

Ok so you can see from above that reocrd 1 and 3 are basically the same record. So I only want to select one of those (either). So I thought I'd need to group by last name and the address. But I also need to select the first name. Group by woudnt let me query like this

select lname, fname, add1 from table group by fname, add1

for obvious reasons.

fogofogo
Starting Member

11 Posts

Posted - 2008-11-13 : 10:37:21

quote:
Originally posted by visakh16

Also in future please post questions only in relevant forum. This is 2005 forum. you must have posted this in 2000 forum.

whoops! sorry

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-13 : 10:42:27

quote:
Originally posted by fogofogo

Thanks again

Heres some sample data

ID.....Fname.....Lname.....Add1
1 .....James.....Brown ....21 GetDown St
2 .....John .....Cash .....14 Jacksonville
3 .....J.........Brown ....21 GetDown St
4 .....Micky ....Jackson ..Shamone Road
5 .....Dolly ... Parton ...Cannes, France

Ok so you can see from above that reocrd 1 and 3 are basically the same record. So I only want to select one of those (either). So I thought I'd need to group by last name and the address. But I also need to select the first name. Group by woudnt let me query like this

select lname, fname, add1 from table group by fname, add1

for obvious reasons.

SELECT ID,
       Fname,
       Lname,
       Addl
FROM
(
SELECT ID,
       Fname,
       Lname,
       Addl,
       (SELECT COUNT(*) FROM YourTable WHERE Lname=t.Lname AND Addl=t.Addl AND ID<=t.ID) AS Seq
FROM YourTable
)t
WHERE t.Seq=1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources