Dynamic delete stored procedure question

I have the following sp:

@.TABLE_NAMEvarchar(255),

@.CONTROL_IDint

AS

DECLARE@.sqlvarchar(4000)

SELECT@.sql ='DELETE FROM ['+ @.TABLE_NAME +'] WHERE CONTROL_ID = 5'
SELECT@.sql ='DELETE FROM ['+ @.TABLE_NAME +'] WHERE CONTROL_ID = '+ @.CONTROL_ID +''

EXEC(@.sql)

When I use the the first SELECT line, it works great.
When I use the second dynamic select, SQL raises an error:

Syntax error converting the varchar value 'DELETE FROM [TABLE_SETTINGS] WHERE CONTROL_ID = ' to a column of data type int.

What is wrong?

Try to convert the value to varchar (as you are using an inline sql string):

SELECT@.sql ='DELETE FROM ['+ @.TABLE_NAME +'] WHERE CONTROL_ID = '+ Convert(varchar(255),@.CONTROL_ID) +''

Hope this helps,

Vivek

|||

Perfect, exactly what I was looking for....thanx.

Dynamic DB size!

When I delete fotos from Albums database size do not decrease!
When I add a photo (after deleting), size do not increase but if I add more photos (more than I deleted) size do increase!

How can I get dynamic database size?

Hi,

take a look at this page:http://search.msdn.microsoft.com/search/results.aspx?qu=shrink+database&View=msdn&st=b

Grz, Kris.