dynamic sql - 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)

dynamic sql

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-11-03 : 07:41:18

orit writes "Hello
I am trying to wtite stored procedure that will delete a record and will get some parameters:

CREATE PROCEDURE ItemRemove
(
@BoardName nvarchar(50),
@ProductID int
)
AS
DECLARE @SQL nvarchar(4000)
SET @SQL='DELETE FROM '+ @BoardName+' WHERE ProductID='+@ProductID
EXEC sp_executesql @SQL
GO

The error message I get from my application is:
"Procedure or function ItemRemove has too many arguments specified"
What is the problem?
Thank you for your help."

harshal_in
Aged Yak Warrior

633 Posts

Posted - 2004-11-03 : 07:45:59

quote:
Originally posted by AskSQLTeam

orit writes "Hello
I am trying to wtite stored procedure that will delete a record and will get some parameters:

CREATE PROCEDURE ItemRemove
(
@BoardName nvarchar(50),
@ProductID int
)
AS
DECLARE @SQL nvarchar(4000)
SET @SQL='DELETE FROM '+ @BoardName+' WHERE ProductID='+@ProductID
EXEC sp_executesql @SQL
GO

The error message I get from my application is:
"Procedure or function ItemRemove has too many arguments specified"
What is the problem?
Thank you for your help."

how are u calling the stored procedure?

He is a fool for five minutes who asks ,
but who does not ask remains a fool for life!

http://www.sqldude.4t.com
http://www.sqljunkies.com/weblog/sqldude

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-11-03 : 10:24:59

Why do you have mutliple tables out there with the same data in it? this should probably all be in 1 table. Often a bad database design requires over-complicated, dynamic SQL such as this as opposed to simple DELETE statements with a parmeter or two.

As I've said many times, unless it is an administrative stored procedure, sp's should never accept database objects (table names, column names, etc) as parameters. The whole point of a stored procedure to is abstract the database structure, not require intimate knowledge of it.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources