Help please - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Help please

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-06-19 : 07:32:18

Mikael writes "We have some problem with a stored procedure!

We want to create a proc.. that delete all information that a user has written in one of our tables.

Want to to like this:

exec sp_delete 'miboboy@online.no' in Q.A

But how can it be done in with stored procedure

Have tried:

Create procedure sp_delete
as
delete from tblInformation
where username = @username

Sinc. Mikael the freebie! "

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2003-06-19 : 08:05:47

-- sample data for testing

create table #test(surname varchar(20), age int)

insert into #test(surname, age)
select 'garcia', 24
union all
select 'el-bastardo', 15

-- create sp

create procedure spUserDelete(@person varchar(20), @tablename varchar(20))
as
declare @sql varchar(1000)
set @sql = ''
select @sql = @sql + 'delete from ' + @tablename + ' where surname = ''' + @person + ''''
exec(@sql)

-- execute sp giving surname as parameter

exec spUserDelete 'garcia', '#test'

Edited by - Amethystium on 06/19/2003 08:35:05

macka
Posting Yak Master

162 Posts

Posted - 2003-06-19 : 08:24:14

You shouldn't really prefix your stored procs with "sp_" as SQL Server will think its a system stored proc and go hunting in the master database before it looks at your database. Personally I use just an sp prefix . ie. spUserDelete

Cheers,

macka.

--
There are only 10 types of people in the world - Those who understand binary, and those who don't.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources