April 27, 2010 at 5:01 am
Dear All
I have a project which contains more than 30 tables in the database and i want to create a stored procedure for the delete action, but i do not want to create stored procedure for each table because they are all the same.
So I want to pass the table name , the column which i will use in the where clause and the value to check against.
Some thing like this:
@Mytablename
@column name
@@Value
DELETE FROM @Mytablename WHERE @Columnname = @Value
Is it possible to do that or i have to make stored procedure for each table
Your help will be highly appreciated.
Thank you very much.
April 27, 2010 at 5:19 am
~Edit: Code removed - Better version of this is posted in the next post of mine
Cheers!!
April 27, 2010 at 5:20 am
You need to prepare dynamic SQL to build the delete query from the parameters you are passing to this procedure and then run the dynamic SQL using exec.
April 27, 2010 at 6:39 am
Dear ColdCoffee
Thank you very much for your great and quick reply.
I did the following SP as your instructions :
ALTER PROCEDURE [dbo].[SPDel]
-- Add the parameters for the stored procedure here
(
@Tb VARCHAR(100),
@Col VARCHAR(100),
@val int
)
AS
DECLARE @Mytablename VARCHAR(100)
DECLARE @columnname VARCHAR(100)
DECLARE @DELETE_QUERY VARCHAR(2000)
DECLARE @Value int
SET @DELETE_QUERY = ''
SET @Mytablename = @Tb
SET @columnname = @Col
SET @Value = @val
BEGIN
SET NOCOUNT ON;
SELECT @DELETE_QUERY = @DELETE_QUERY + 'DELETE FROM '+@Mytablename+ ' WHERE '+@Columnname +' = '''+@Value+''
PRINT @DELETE_QUERY
EXEC (@DELETE_QUERY)
END
It complied and saved fine
and then i execute it as :
DECLARE @rc int
DECLARE @Tb varchar(100)
DECLARE @Col varchar(100)
DECLARE @val int
-- TODO: Set parameter values here.
EXECUTE @rc = [TestTransactionDb].[dbo].[SPDel]
@Tb = 'dbo.Dtl_Table'
,@Col = 'Dtl_Id'
,@Val = 8
But it gave me me the following error:
Msg 245, Level 16, State 1, Procedure SPDel, Line 29
Conversion failed when converting the varchar value 'DELETE FROM dbo.Dtl_Table WHERE Dtl_Id = '' to data type int.
Can you please help me in this.
Again thank you very much
April 27, 2010 at 6:56 am
Happy that my code helped you..
As for your error, you are trying to concatenate INT value to a VARCHAR variable.. To negate his error , we will have to type-cast the @Value variable.. so the new code will be like this :
DECLARE @Mytablename VARCHAR(100)
DECLARE @columnname VARCHAR(100)
DECLARE @Value VARCHAR(100)
DECLARE @DELETE_QUERY VARCHAR(2000)
SET @DELETE_QUERY = ''
SELECT @DELETE_QUERY = @DELETE_QUERY + 'DELETE FROM '+@Mytablename+ ' WHERE '+@Columnname +' = '+CAST(@Value AS VARCHAR(100))
PRINT @DELETE_QUERY
--EXEC (@DELETE_QUERY)
Hope this helps you..
Cheers!!
~Edit : Fixed silly spelling mistakes 😀
April 27, 2010 at 7:12 am
ColdCoffee (4/27/2010)
Happy that my code helped you..As for your error, you are trying to concatenate INT value to a VARCHAR variable.. To negate his error , we will have to type-cast the @Value variable.. so the new code will be like this :
DECLARE @Mytablename VARCHAR(100)
DECLARE @columnname VARCHAR(100)
DECLARE @Value VARCHAR(100)
DECLARE @DELETE_QUERY VARCHAR(2000)
SET @DELETE_QUERY = ''
SELECT @DELETE_QUERY = @DELETE_QUERY + 'DELETE FROM '+@Mytablename+ ' WHERE '+@Columnname +' = '+CAST(@Value AS VARCHAR(100))
PRINT @DELETE_QUERY
--EXEC (@DELETE_QUERY)
Hope this helps you..
Cheers!!
~Edit : Fixed silly spelling mistakes 😀
Thank you very much for the solution.
It works just fine and the result as I wanted it to be.
Please accept my best wishes and best regards
Cheers
April 27, 2010 at 7:16 am
T.alkathiri (4/27/2010)
Thank you very much for the solution.It works just fine and the result as I wanted it to be.
Please accept my best wishes and best regards
Cheers
Oh boy :blush:!! Thanks for such a nice compliment and appreciation!
Glad to have helped you and happy that your issue is fixed!
Thanks and Cheers!!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply