Delete not working - 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)

Delete not working

Author

Topic

shaoen01
Yak Posting Veteran

78 Posts

Posted - 2006-01-17 : 01:02:13

Currently, my this delete method gives me an error msg: Syntax error converting the varchar value 'DELETE FROM BRANPROM WHERE PROMOTIONID IN (21) AND BRANCHID=' to a column of data type int.

If anyone can provide me another alternative to delete multiple rows not using dynamic sql, i am at all ears. However, cant seem to find any method for deleting without using the EXEC.

I have posted a topic before:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=60179

Thanks

CREATE PROCEDURE [DELETEBRANCHPROM]
(
@PROMOTIONIDS VARCHAR (500),
@BRANCHID INT
)

As

DECLARE @SQL VARCHAR(500)

SET @SQL = 'DELETE FROM BRANPROM WHERE PROMOTIONID IN (' +
@PROMOTIONIDS + ') AND BRANCHID=' + @BRANCHID

EXEC(@SQL)
GO

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-17 : 01:36:32

you need to convert the @BRANCHID from INT to VARCHAR before concat.

SET @SQL = 'DELETE FROM BRANPROM WHERE PROMOTIONID IN (' +
@PROMOTIONIDS + ') AND BRANCHID=' + convert(varchar(10), @BRANCHID)

-----------------
'KH'

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-17 : 03:28:25

It seems you didnt follow fully on the above topic
What is the difficulty in using the methods suggested here?
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=60179

Madhivanan

Failing to plan is Planning to fail

shaoen01
Yak Posting Veteran

78 Posts

Posted - 2006-01-18 : 09:12:59

This was explained another member, thats why i think its better to not use dynamic sql.

There are two downsides to this (compared to the other approach that has been suggested), but that doens't
mean its wrong - just thought you might like to know!
1) The user has to have SELECT access to the MSTRITEM and ITEMIMAG tables. Without the dynamic SQL
they would only need EXECUTE permission on the SProc itself - which is obviously a lot tighter - with SELECT
permission they can just hook up, say, Excel to the DB and start pulling data at will ... and successful hackers
can do too!

quote:
Originally posted by madhivanan

It seems you didnt follow fully on the above topic
What is the difficulty in using the methods suggested here?
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=60179

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources