August 26, 2019 at 5:43 pm
hello
can i use with one procedure or 3(1 for insert ,1 for update,1 for delete operation)?
for example will be that
exec dbo.delete_table(@table_name,@id) ------ this for delete any rows any table
exec dbo.update_table(@table_name,@col1,@col2....col4,@id) --- update any rows for id any tables
exec dbo.insert_table(@table_name,@values ) or (@table_name,@value1,@value2.....@valuen ) for insert
August 26, 2019 at 6:13 pm
I suppose you could force that to work, but it's a bad idea. Individual tables have vastly different columns and requirements.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
August 26, 2019 at 6:47 pm
i know
but have a more 200 tables and if have 2 procedure will be 400 stored proc?
where i will give this procedure?
have any another optimization method?
August 26, 2019 at 8:19 pm
Job security.
Can't you just right-click on the table and ...Script Table As... Insert/Update/Delete?
August 26, 2019 at 8:46 pm
OH NO SIR
I'M ABOUT SAVING THIS ALL STORED PROCEDURES?
CAN I SAVE IN ANOTHER DATABASE?
August 26, 2019 at 9:24 pm
Maybe the best thing to do for 200 tables is to have a script that generates the stored procedures for you? I know there are such things around although I haven't used one for many years. 400 stored procedures isn't that many for a database, I have one with 2300.
August 26, 2019 at 10:17 pm
i want to give values (for insert update delete)
rule is that
if @idvalue>0 and another values is empty then delete
if @idvalue =0 or is empty then insert
and was create this script for example
how will this stored proc ?
DECLARE @I INT, @COUNT_DELETE INT
DECLARE @T TABLE(ID INT,M_NAME NVARCHAR(50),CAP NVARCHAR(50))
INSERT INTO @T (ID,M_NAME,CAP)
VALUES(1,'A','')
SELECT * FROM @T
SET @COUNT_DELETE = (SELECT COUNT(*) FROM @T WHERE (M_NAME IS NULL OR M_NAME='') AND (CAP IS NULL OR CAP=''))
SET @I = (SELECT ID FROM @T)
IF @I>0
BEGIN
IF @COUNT_DELETE>0
BEGIN
SELECT 'DELETE'
END
ELSE
SELECT 'UPDATE'
END
ELSE
BEGIN
SELECT 'INSERT'
END
August 26, 2019 at 10:36 pm
Again, very bad idea trying to combine all three actions, even for one table, let alone for all tables.
It's easy enough to generate individual procs. If you wanted to, you could have a single "driver" proc that called the appropriate proc based on the table name. I would not recommend that, but it's way better than just a single proc period.
EXEC dbo.super_proc_handling_every_table @table_id, @value1, @value2, ...
Here's a very rough shell of how that proc would generally look. It would need adjustments to include the specific number of column values to pass, etc..
Again, this whole approach is a nightmare compared to separate procs for each table.
SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO
CREATE PROCEDURE dbo.super_proc_handling_every_table
@table_name nvarchar(128),
@id int, /*0=INSERT*/
@value01 sql_variant = NULL,
@value02 sql_variant = NULL,
@value03 sql_variant = NULL,
@value04 sql_variant = NULL,
@value05 sql_variant = NULL,
@value06 sql_variant = NULL,
@value07 sql_variant = NULL,
@value08 sql_variant = NULL,
@value09 sql_variant = NULL /*, ...*/
AS
SET NOCOUNT ON;
DECLARE @sql nvarchar(max)
IF @id = 0 AND @value01 IS NOT NULL
BEGIN
SET @sql = 'EXEC dbo.' + @table_name + '_INSERT @id, @value01, @value02 /*, ...*/'
EXEC sys.sp_execute_sql @sql, N'@id, @value01, ...', @id, @value01, @value02 /*, ...*/
END /*IF*/
ELSE
IF @id > 0 AND @value01 IS NULL
BEGIN
SET @sql = 'EXEC dbo.' + @table_name + '_DELETE @id'
EXEC sys.sp_execute_sql @sql, N'@id', @id
END /*IF*/
ELSE
BEGIN
SET @sql = 'EXEC dbo.' + @table_name + '_UPDATE @id, @value01 /, ...'
EXEC sys.sp_execute_sql @sql, N'@id, @value01, ...', @id, @value01, @value02 /*, ...*/
END /*ELSE*/
/*end of proc*/
GO
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply