I just need a simple store procedure which deletes a record from 2 tables. The second delete is comparing file_name field. What is the correct syntax for the second delete statement. Any help will be much appreciated. Thanks.

CREATE procedure up_test_delete(
@test_id int
)
as
delete from tests where test_id=@test_id
delete from (another table) where file_name ='filename.aspx?test_id='+@test_id
GO

what do u mean by comparing filename field? did u mean to compare filename field with test_id? Please give good description about what is file type and test_id and how they are related. Then I can help you.

file_name is a field in the second table. content of the file_name is like something.aspx?test_id=1 (@test_id)

where @test_id is the test_id value from first table.

यसो गर्‍यो भने के हुन्छ ?

CREATE PROCEDURE up_test_delete
@test_id int
AS
BEGIN
DELETE FROM tests
WHERE tests.test_id= @test_id;
DELETE FROM otherTable
WHERE otherTable.file_name = @test_id;

END
GO

But file_name has "something.aspx?test_id=" too.

What is the problem you are getting with this?
delete from (another table) where file_name ='filename.aspx?test_id='+@test_id

maybe try
delete from (another table) where file_name ='filename.aspx?test_id='+ convert(varchar(40),@test_id)

Something like this

how about trying below thing ?

CREATE PROCEDURE up_test_delete (test_id in varchar)
AS
BEGIN
DELETE FROM tests
WHERE tests.test_id= 'up_test_delete.test_id';
DELETE FROM otherTable
WHERE otherTable.file_name like '%up_test_delete.test_id%';
END;
/

visit:
www.sqlserverperformance.com
www.microsoft.com/sql
www.umachandar.com

I figured that it is not possible to do what I was trying to do using Store Porcedure so I had to write the function in the code page itself.

Thanks all for your help. :)

its very possible. do this, it might work

CREATE procedure up_test_delete(
@test_id int
)
as
delete from tests where test_id=@test_id
delete from (another table) where file_name ='filename.aspx?test_id=' + cast(@test_id, varchar(10))
GO

- just a thought!

Hyaa, you are right as per my thought. Here is my SP:

CREATE procedure up_test_delete(
@test_id int
)
as
delete from tests where test_id=@test_id
delete from (another table) where file_name =’filename.aspx?test_id=’+CAST(@test_id AS VARCHAR(100)

Let me know if it helps you .

Regards