using araylist in stored proc

  • hello i have this stored proc for  dynamically insert update delete select  and select with id

    but hier have a 2 column name and 2 values for this crud operaion
    may be more columns and more values for crud opertaion
    can i use arraylist 
    for example i will add 20 column for inserting proc will give me 20 values will insert 20 
    all in dynamically 
    can that structure?
    thanks for discussion 

    GO
    ALTER Procedure [dbo].[crud_operation]
        @TableName VarChar(100),
        @id int=null,
        @action int,@COL1 sysname=null,@COL2 sysname=null,@cval1 nvarchar(max)=null,@cval2 nvarchar(max)=null
    AS

    Declare @sql VarChar(1000)
    ----simple select--

    SELECT @sql = case
    when (@action = 0 and @id is null) then 'SELECT * FROM ' + @TableName
    when (@action=0 and @id is not null) then 'SELECT * FROM ' + @TableName +' '+ N'where id=' + rtrim(cast (@id as int))
    ----insert----
    when (@action=1 and @COL1 is not null and @COL2 is null) then 'insert into ' + @TableName + ' ' +'('+@COL1+')' + ' ' + 'select '+ ''''+@cval1+''''
    when (@action=1 and @COL1 is not null and @COL2 is not null) then
    'insert into ' + @TableName + '('+@COL1+','+@COL2 + ')' + ' ' + 'select '+ ''''+@cval1+'''' +','+''''+@cval2+''''
    ----delete----
     when(@action=2 and @id is not null) then
    'delete from ' + @TableName + ' ' + N'where id='+ rtrim(cast(@id as int))
    ----update ---
    when (@action=3 and @id is not null and (@COL1 is not null and @COL2 is null))
     then 'update ' + @TableName + ' set ' + @COL1 + '=' + @cval1 +' where id=' + rtrim(cast(@id as int))

    else '' end
    print @sql
    Exec ( @sql)

  • Personally, I see a lot more problems with this than simply the ability to pass an unlimited amount t if columns. SQL injection is a concern with this type of set up.

    What is your reason for choosing to have a one size fits all SP? you would be much better splitting this out to start with.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • Thom A - Saturday, December 30, 2017 7:47 AM

    Personally, I see a lot more problems with this than simply the ability to pass an unlimited amount t if columns. SQL injection is a concern with this type of set up.

    What is your reason for choosing to have a one size fits all SP? you would be much better splitting this out to start with.

    yes this structure is not normally
    i want to pass table name  columnname dyanically for insert and update

  • Grrr... Firefox crashed. Let's start again.

    Ok, so, firstly, Let's start simple. We'll use your Stored Procedure and pass the following values (note I have removed the EXEC(@SQL) from my version:

    EXEC [dbo].[crud_operation] @TableName = 'Test; DROP TABLE [test];', @action = 0;

    That results in the following Dynamic SQL being produced:
    SELECT * FROM Test; DROP TABLE [test];
    Excellent, you have just selected all the values from your table, [Test] and then dropped it. That is hardly likely to be intended.

    Let's, however, go a little further shall we? Now, I don't know the privileges of your account that is accessing that SP, but let's assume they're pretty high (note I've used a variable for readability, you could just as easily pass that string straight to the SP itself):

    DECLARE @inject nvarchar(MAX);
    SET @inject = 'a'';' + CHAR(10) +
    'CREATE DATABASE [Injection];'
    + CHAR(10) +
    'USE master;' + CHAR(10) +
    'CREATE LOGIN [Hackzor] WITH PASSWORD = ''Hackz'', CHECK_EXPIRATION = OFF, CHECK_POLICY = OFF;'
    + CHAR(10) +
    'EXEC sp_addsrvrolemember ''Hackzor'', ''sysadmin'';--'
    EXEC [dbo].[crud_operation] @TableName = 'YourTable', @action = 1, @COL1 = 'YourColumn', @cval1 = @inject;

    And the resulting dynamic SQL?
    insert into YourTable (YourColumn) select 'a';
    CREATE DATABASE [Injection];
    USE master;
    CREATE LOGIN [Hackzor] WITH PASSWORD = 'Hackz', CHECK_EXPIRATION = OFF, CHECK_POLICY = OFF;
    EXEC sp_addsrvrolemember 'Hackzor', 'sysadmin';--'

    So, what does that do? Well, I just inserted a simple value into your Table. Then, I created a new database called [Injection]. After that I switched to the [master] database, created a Login called Hackzor with a very simple password and gave it sysadmin privileges!

    Now, that might not all go through, if the login doesn't have sysadmin rights itself, but anyone with malicious intent will be happy to try a few combinations until that get the result that want.

    Can we fix this? Yes, definitely, but before I, or anyone else starts posting how to, it would be good for you to acknowledge the problems, and say you're ready to fix them; there's quite a bit of work here; and I'd rather not it be a fruitless endeavour.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • natigsqlserver - Saturday, December 30, 2017 8:16 AM

    Thom A - Saturday, December 30, 2017 7:47 AM

    Personally, I see a lot more problems with this than simply the ability to pass an unlimited amount t if columns. SQL injection is a concern with this type of set up.

    What is your reason for choosing to have a one size fits all SP? you would be much better splitting this out to start with.

    yes this structure is not normally
    i want to pass table name  columnname dyanically for insert and update

    And, as I said in my original post, why a one size fits all? Apart from fixing the injection issues, I'd at least ensure that you have separate SP's to INSERT, UPDATE, SELECT, Etc... Yes, the table can be dynamic, but highly recommend against it being able to do anything.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • I'll start off with an introduction anyway. This version below will avoid injection. I've commented the lines, which should help you understand what it's doing. if however, you don't, please do ask. You'll note this only does a SELECT, nothing else. As I said above, I suggest splitting out the operations:

    CREATE PROC dbo.crud_select_operation @TableName varchar(100), @id INT = NULL
    AS
      DECLARE @SQL nvarchar(4000); --This will hold our Dynamic SQL
      DECLARE @parm nvarchar(100); --This hold the definition of our parameter for our D-SQL, if we need it.
     
      --Start off with the basic of the SELECT statement
      SELECT @SQL = N'
      SELECT *
      FROM ' + QUOTENAME([name]) --Quote the table name, incase it has special characters
      FROM sys.tables --We use sys.tables, to ensure the table exists. If it doesn't exist, then NULL is returned (and no SQL is run)
      WHERE [name] = @TableName;
      --Let's do the part where there is an ID
      IF @ID IS NOT NULL BEGIN
       SET @SQL = @SQL + N'
      WHERE id = @dID;'; --This adds a WHERE clause
       SET @parm = N'@dID int'; --And that's the definition of our parameter in our Dynamic SQL
       PRINT @SQL;
       EXEC sp_executesql @SQL, @parm, @dID = @ID; --And the bit that runs it all. We're much safer using sp_executesql
      END ELSE BEGIN
       --This is is there is no ID
       SET @SQL = @SQL + N';'; --Future proofing
       PRINT @SQL;
       EXEC sp_executesql @SQL;
      END
    GO

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • Thom A - Saturday, December 30, 2017 8:51 AM

    I'll start off with an introduction anyway. This version below will avoid injection. I've commented the lines, which should help you understand what it's doing. if however, you don't, please do ask. You'll note this only does a SELECT, nothing else. As I said above, I suggest splitting out the operations:

    CREATE PROC dbo.crud_select_operation @TableName varchar(100), @id INT = NULL
    AS
      DECLARE @SQL nvarchar(4000); --This will hold our Dynamic SQL
      DECLARE @parm nvarchar(100); --This hold the definition of our parameter for our D-SQL, if we need it.
     
      --Start off with the basic of the SELECT statement
      SELECT @SQL = N'
      SELECT *
      FROM ' + QUOTENAME([name]) --Quote the table name, incase it has special characters
      FROM sys.tables --We use sys.tables, to ensure the table exists. If it doesn't exist, then NULL is returned (and no SQL is run)
      WHERE [name] = @TableName;
      --Let's do the part where there is an ID
      IF @ID IS NOT NULL BEGIN
       SET @SQL = @SQL + N'
      WHERE id = @dID;'; --This adds a WHERE clause
       SET @parm = N'@dID int'; --And that's the definition of our parameter in our Dynamic SQL
       PRINT @SQL;
       EXEC sp_executesql @SQL, @parm, @dID = @ID; --And the bit that runs it all. We're much safer using sp_executesql
      END ELSE BEGIN
       --This is is there is no ID
       SET @SQL = @SQL + N';'; --Future proofing
       PRINT @SQL;
       EXEC sp_executesql @SQL;
      END
    GO

    thank you very much 
    but i need insert and update 
    operation

  • natigsqlserver - Saturday, December 30, 2017 8:57 AM

    Thom A - Saturday, December 30, 2017 8:51 AM

    I'll start off with an introduction anyway. This version below will avoid injection. I've commented the lines, which should help you understand what it's doing. if however, you don't, please do ask. You'll note this only does a SELECT, nothing else. As I said above, I suggest splitting out the operations:

    CREATE PROC dbo.crud_select_operation @TableName varchar(100), @id INT = NULL
    AS
      DECLARE @SQL nvarchar(4000); --This will hold our Dynamic SQL
      DECLARE @parm nvarchar(100); --This hold the definition of our parameter for our D-SQL, if we need it.
     
      --Start off with the basic of the SELECT statement
      SELECT @SQL = N'
      SELECT *
      FROM ' + QUOTENAME([name]) --Quote the table name, incase it has special characters
      FROM sys.tables --We use sys.tables, to ensure the table exists. If it doesn't exist, then NULL is returned (and no SQL is run)
      WHERE [name] = @TableName;
      --Let's do the part where there is an ID
      IF @ID IS NOT NULL BEGIN
       SET @SQL = @SQL + N'
      WHERE id = @dID;'; --This adds a WHERE clause
       SET @parm = N'@dID int'; --And that's the definition of our parameter in our Dynamic SQL
       PRINT @SQL;
       EXEC sp_executesql @SQL, @parm, @dID = @ID; --And the bit that runs it all. We're much safer using sp_executesql
      END ELSE BEGIN
       --This is is there is no ID
       SET @SQL = @SQL + N';'; --Future proofing
       PRINT @SQL;
       EXEC sp_executesql @SQL;
      END
    GO

    thank you very much 
    but i need insert and update 
    operation

    Yes, I know you do; as i said before, are you happy to have these separately? Do you understand what the above is doing, and why it's safer?

    The most important thing you need to learn from this post is the danger of SQL injection. I may well solve the problem you intially came here for later, however, what you need to learn is how bad the current SP you have is. You need to be able to come away from this topic knowing to never create a stored procedure like that, and to know how you should do it instead in the future as well.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • Thom A - Saturday, December 30, 2017 9:02 AM

    natigsqlserver - Saturday, December 30, 2017 8:57 AM

    Thom A - Saturday, December 30, 2017 8:51 AM

    I'll start off with an introduction anyway. This version below will avoid injection. I've commented the lines, which should help you understand what it's doing. if however, you don't, please do ask. You'll note this only does a SELECT, nothing else. As I said above, I suggest splitting out the operations:

    CREATE PROC dbo.crud_select_operation @TableName varchar(100), @id INT = NULL
    AS
      DECLARE @SQL nvarchar(4000); --This will hold our Dynamic SQL
      DECLARE @parm nvarchar(100); --This hold the definition of our parameter for our D-SQL, if we need it.
     
      --Start off with the basic of the SELECT statement
      SELECT @SQL = N'
      SELECT *
      FROM ' + QUOTENAME([name]) --Quote the table name, incase it has special characters
      FROM sys.tables --We use sys.tables, to ensure the table exists. If it doesn't exist, then NULL is returned (and no SQL is run)
      WHERE [name] = @TableName;
      --Let's do the part where there is an ID
      IF @ID IS NOT NULL BEGIN
       SET @SQL = @SQL + N'
      WHERE id = @dID;'; --This adds a WHERE clause
       SET @parm = N'@dID int'; --And that's the definition of our parameter in our Dynamic SQL
       PRINT @SQL;
       EXEC sp_executesql @SQL, @parm, @dID = @ID; --And the bit that runs it all. We're much safer using sp_executesql
      END ELSE BEGIN
       --This is is there is no ID
       SET @SQL = @SQL + N';'; --Future proofing
       PRINT @SQL;
       EXEC sp_executesql @SQL;
      END
    GO

    thank you very much 
    but i need insert and update 
    operation

    Yes, I know you do; as i said before, are you happy to have these separately? Do you understand what the above is doing, and why it's safer?

    yes i was understand 
    but ned pass column name and values for insert and update process dynamically?

  • natigsqlserver - Saturday, December 30, 2017 9:06 AM

    yes i was understand 
    but ned pass column name and values for insert and update process dynamically?

    Ok, provided you understand, that's fine. I'll be providing several SP's, which is going to take awhile to write up. I'll post again later.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • ok 
    thank you very much 
    i'm waiting

  • Thom A - Saturday, December 30, 2017 9:10 AM

    natigsqlserver - Saturday, December 30, 2017 9:06 AM

    yes i was understand 
    but ned pass column name and values for insert and update process dynamically?

    Ok, provided you understand, that's fine. I'll be providing several SP's, which is going to take awhile to write up. I'll post again later.

    ok thank you very much 
    i'm waiitng

  • Ok, that was a lot of work... Now, I'm made the comment in the script I've made as well, however, I'm going to repeat i'm not 100% happy with this. I pretty confident i've avoided injection, but the UPDATE and INSERT statement weren't fun with the addition of dynamic columns (or number of). I'm therefore, more than happy for someone to make changes to those. At the moment, however, it's a Saturday, and that was more work than I wanted to entertain on a day off.

    Anyway, there's probably a decent amount to take in below. This script create a sample database [Crud], however, if you have a test environment I still suggest you run it there first, and make sure you understand it. Feel free to ask questions about it. Apologies for the formatting; that's SSC's fault. As such, I've also attached a copy to this post, which maintains the original formatting.

    This script was tested on:
    Microsoft SQL Server 2017 (RTM-CU2) (KB4052574) - 14.0.3008.27 (X64)
    Nov 16 2017 10:00:49
    Copyright (C) 2017 Microsoft Corporation
    Developer Edition (64-bit) on Linux (Ubuntu 17.10)

    As you posted in the SQL Server 2017, I've assumed that's what you are using.

    USE [master];
    --Create a sample DB
    CREATE DATABASE Crud;
    GO
    USE Crud;
    GO
    --Sample Table
    CREATE TABLE Test (ID int, String varchar(10));
    --Initial Sample Data
    INSERT INTO Test
    VALUES (1,'A'),(2,'B'),(2,'BA');
    GO
    --Select Proc
    CREATE PROC dbo.crud_select_operation @TableName varchar(100), @ID int = NULL
    AS
      DECLARE @SQL nvarchar(4000); --This will hold our Dynamic SQL
      DECLARE @parm nvarchar(100); --This hold the definition of our parameter for our D-SQL, if we need it.
     
      --Start off with the basic of the SELECT statement
      SELECT @SQL = N'
      SELECT *
      FROM ' + QUOTENAME([name]) --Quote the table name, incase it has special characters
      FROM sys.tables --We use sys.tables, to ensure the table exist. If it doesn't exist, then NULL the returned (and no SQL is run)
      WHERE [name] = @TableName;
      --Let's do the part where there is an ID
      IF @ID IS NOT NULL BEGIN
       SET @SQL = @SQL + N'
      WHERE ID = @dID;'; --This add a WHERE clause
       SET @parm = N'@dID int'; --And that's the definition of our parameter in our Dynamic SQL
       PRINT @SQL;
       EXEC sp_executesql @SQL, @parm, @dID = @ID; --And the bit that runs it all. We're much safer using sp_executesql
      END ELSE BEGIN
       --This is is there is no ID
       SET @SQL = @SQL + ';'; --Future proofing
       PRINT @SQL;
       EXEC sp_executesql @SQL;
      END
    GO
    --Testing
    EXEC crud_select_operation @TableName = 'Test'; --3 rows!
    EXEC crud_select_operation @TableName = 'Test', @Id = 2; --2 row, For ID 2;
    GO
    --Delete is going to be easier, so let's do that next
    CREATE PROC crud_delete_operation @TableName varchar(100), @ID int --ID is required, so no default value
    AS
      --Unsurprisingly, this is very similar to the SELECT
      DECLARE @SQL nvarchar(4000), @parm nvarchar(100);
      --Start off with the basic of the SELECT statement
      SELECT @SQL = N'
      DELETE
      FROM ' + QUOTENAME([name]) + N'
      WHERE ID = @dID;' --We can go straight to a WHERE this time
      FROM sys.tables --We use sys.tables, to ensure the table exist. If it doesn't exist, then NULL the returned (and no SQL is run)
      WHERE [name] = @TableName;
      SET @parm = N'@dID int'; --And that's the definition of our parameter in our Dynamic SQL
      PRINT @SQL;
      EXEC sp_executesql @SQL, @parm, @dID = @ID; --And the bit that runs it all. We're much safer using sp_executesql 
    GO
    --Testing time
    EXEC crud_delete_operation @TableName = 'Test', @ID = 1;
    --And might as well check that ID one has gone:
    EXEC crud_select_operation @TableName = 'Test'; --2 rows! Success!
    GO
    --Now UPDATE and SELECT have multiple columns involved.
    --That adds an extra layer of complexity. So let's use a Custom Table Data Type:
    CREATE TYPE crud_columns_and_values AS TABLE (ColumnName varchar(100), ColumnValue varchar(100)); --the size of Column Value may need to be larger
    GO
    --Now, we can use that new datatype to do our other SP's:
    --Let's do the INSERT
    CREATE PROC crud_insert_operation @TableName varchar(100), @ColVals crud_columns_and_values READONLY
    AS
      DECLARE @SQL nvarchar(4000);
      --Start off with the basics again
      SELECT @SQL = N'
      INSERT INTO' + QUOTENAME([name]) --Quote the table name, incase it has special characters
      FROM sys.tables --We use sys.tables, to ensure the table exist. If it doesn't exist, then NULL the returned (and no SQL is run)
      WHERE [name] = @TableName;
      --Now, however, we have to get all those columns into there
      SELECT @SQL = @SQL + N'(' +
             --This creates a delimited list of the columns
             --Again, it uses sys.columns as we want to ensure the column really exists.
             STUFF((SELECT N',' + QUOTENAME(c.[name])
               FROM sys.columns c
                 JOIN sys.tables t ON c.object_id = t.object_id
               WHERE t.[name] = @TableName
                AND c.Name IN (SELECT CV.ColumnName FROM @ColVals CV)
               ORDER BY c.[name] --ORDERING IS IMPORTANT!
               FOR XML PATH(N'')),1,1,N'') +
             --This is called the FOR XML STUFF technique. Have a Google if you don't understand what it's doing
             --or reply to my post.
             N')' + NCHAR(10); --NCHAR(10) is a line break
      --This is the "hard" bit, we need to get those values in
      --I'm not 100% happy on this, but I *think* we've avoided injection here. I am more than happy for someone to prove me wrong here though
      SET @SQL = @SQL + N'SELECT ' +STUFF((SELECT N',' + N'''' + REPLACE(ColumnValue,N'''',N'''''') + N''''
                  FROM @ColVals
                  ORDER BY ColumnName
                  FOR XML PATH(N'')),1,1,N'') + N';';
      PRINT @SQL;
      EXEC sp_executesql @SQL;
    GO
    --Now to test
    DECLARE @ValuesToInsert crud_columns_and_values;
    INSERT INTO @ValuesToInsert
    VALUES ('ID','3'),('String','C');
    EXEC crud_insert_operation @TableName = 'Test', @ColVals = @ValuesToInsert;
    GO
    DECLARE @ValuesToInsert crud_columns_and_values;
    INSERT INTO @ValuesToInsert
    VALUES ('ID','4'),('String','D''d');
    EXEC crud_insert_operation @TableName = 'Test', @ColVals = @ValuesToInsert;
    GO
    --Better check that as well!
    EXEC crud_select_operation @TableName = 'Test'; --4 rows!
    GO
    --Ok, finally, the UPDATE
    CREATE PROC crud_update_operation @TableName varchar(100), @ID int, @ColVals crud_columns_and_values READONLY
    AS
      DECLARE @SQL nvarchar(4000);
      --Start off with the basics again
      SELECT @SQL = N'
      UPDATE ' + QUOTENAME([name]) + NCHAR(10)--Quote the table name, incase it has special characters
      FROM sys.tables --We use sys.tables, to ensure the table exist. If it doesn't exist, then NULL the returned (and no SQL is run)
      WHERE [name] = @TableName;
      --Again, I'm not 100% happy on this, but I *think* we've avoided injection here. I am more than happy for someone to prove me wrong here though
      SET @SQL = @SQL + N'SET ' + STUFF((SELECT N',' + QUOTENAME(c.name) + N' = ''' + REPLACE(CV.ColumnValue,N'''',N'''''') + N''''
                 FROM sys.columns c
                   JOIN sys.tables t ON c.object_id = t.object_id
                   JOIN @ColVals CV ON c.[name] = CV.ColumnName
                 WHERE t.[name] = @TableName
                 
                 ORDER BY c.[name] --ORDERING IS IMPORTANT!
                 FOR XML PATH(N'')),1,1,N'') + N';'
      PRINT @SQL;
      EXEC sp_executesql @SQL;
    GO
    DECLARE @ValuesToUpdate crud_columns_and_values;
    INSERT INTO @ValuesToUpdate
    VALUES ('String','E''e');
    EXEC crud_update_operation @TableName = 'Test', @ID = 4, @ColVals = @ValuesToUpdate;
    EXEC crud_select_operation @TableName = 'Test', @ID = 4; --Value has E'e!!!
    GO
    --Clean up
    USE [master];
    DROP DATABASE Crud;
    GO

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • Thom A - Saturday, December 30, 2017 10:21 AM

    Ok, that was a lot of work... Now, I'm made the comment in the script I've made as well, however, I'm going to repeat i'm not 100% happy with this. I pretty confident i've avoided injection, but the UPDATE and INSERT statement weren't fun with the addition of dynamic columns (or number of). I'm therefore, more than happy for someone to make changes to those. At the moment, however, it's a Saturday, and that was more work than I wanted to entertain on a day off.

    Anyway, there's probably a decent amount to take in below. This script create a sample database [Crud], however, if you have a test environment I still suggest you run it there first, and make sure you understand it. Feel free to ask questions about it. Apologies for the formatting; that's SSC's fault. As such, I've also attached a copy to this post, which maintains the original formatting.

    This script was tested on:
    Microsoft SQL Server 2017 (RTM-CU2) (KB4052574) - 14.0.3008.27 (X64)
    Nov 16 2017 10:00:49
    Copyright (C) 2017 Microsoft Corporation
    Developer Edition (64-bit) on Linux (Ubuntu 17.10)

    As you posted in the SQL Server 2017, I've assumed that's what you are using.

    USE [master];
    --Create a sample DB
    CREATE DATABASE Crud;
    GO
    USE Crud;
    GO
    --Sample Table
    CREATE TABLE Test (ID int, String varchar(10));
    --Initial Sample Data
    INSERT INTO Test
    VALUES (1,'A'),(2,'B'),(2,'BA');
    GO
    --Select Proc
    CREATE PROC dbo.crud_select_operation @TableName varchar(100), @ID int = NULL
    AS
      DECLARE @SQL nvarchar(4000); --This will hold our Dynamic SQL
      DECLARE @parm nvarchar(100); --This hold the definition of our parameter for our D-SQL, if we need it.
     
      --Start off with the basic of the SELECT statement
      SELECT @SQL = N'
      SELECT *
      FROM ' + QUOTENAME([name]) --Quote the table name, incase it has special characters
      FROM sys.tables --We use sys.tables, to ensure the table exist. If it doesn't exist, then NULL the returned (and no SQL is run)
      WHERE [name] = @TableName;
      --Let's do the part where there is an ID
      IF @ID IS NOT NULL BEGIN
       SET @SQL = @SQL + N'
      WHERE ID = @dID;'; --This add a WHERE clause
       SET @parm = N'@dID int'; --And that's the definition of our parameter in our Dynamic SQL
       PRINT @SQL;
       EXEC sp_executesql @SQL, @parm, @dID = @ID; --And the bit that runs it all. We're much safer using sp_executesql
      END ELSE BEGIN
       --This is is there is no ID
       SET @SQL = @SQL + ';'; --Future proofing
       PRINT @SQL;
       EXEC sp_executesql @SQL;
      END
    GO
    --Testing
    EXEC crud_select_operation @TableName = 'Test'; --3 rows!
    EXEC crud_select_operation @TableName = 'Test', @Id = 2; --2 row, For ID 2;
    GO
    --Delete is going to be easier, so let's do that next
    CREATE PROC crud_delete_operation @TableName varchar(100), @ID int --ID is required, so no default value
    AS
      --Unsurprisingly, this is very similar to the SELECT
      DECLARE @SQL nvarchar(4000), @parm nvarchar(100);
      --Start off with the basic of the SELECT statement
      SELECT @SQL = N'
      DELETE
      FROM ' + QUOTENAME([name]) + N'
      WHERE ID = @dID;' --We can go straight to a WHERE this time
      FROM sys.tables --We use sys.tables, to ensure the table exist. If it doesn't exist, then NULL the returned (and no SQL is run)
      WHERE [name] = @TableName;
      SET @parm = N'@dID int'; --And that's the definition of our parameter in our Dynamic SQL
      PRINT @SQL;
      EXEC sp_executesql @SQL, @parm, @dID = @ID; --And the bit that runs it all. We're much safer using sp_executesql 
    GO
    --Testing time
    EXEC crud_delete_operation @TableName = 'Test', @ID = 1;
    --And might as well check that ID one has gone:
    EXEC crud_select_operation @TableName = 'Test'; --2 rows! Success!
    GO
    --Now UPDATE and SELECT have multiple columns involved.
    --That adds an extra layer of complexity. So let's use a Custom Table Data Type:
    CREATE TYPE crud_columns_and_values AS TABLE (ColumnName varchar(100), ColumnValue varchar(100)); --the size of Column Value may need to be larger
    GO
    --Now, we can use that new datatype to do our other SP's:
    --Let's do the INSERT
    CREATE PROC crud_insert_operation @TableName varchar(100), @ColVals crud_columns_and_values READONLY
    AS
      DECLARE @SQL nvarchar(4000);
      --Start off with the basics again
      SELECT @SQL = N'
      INSERT INTO' + QUOTENAME([name]) --Quote the table name, incase it has special characters
      FROM sys.tables --We use sys.tables, to ensure the table exist. If it doesn't exist, then NULL the returned (and no SQL is run)
      WHERE [name] = @TableName;
      --Now, however, we have to get all those columns into there
      SELECT @SQL = @SQL + N'(' +
             --This creates a delimited list of the columns
             --Again, it uses sys.columns as we want to ensure the column really exists.
             STUFF((SELECT N',' + QUOTENAME(c.[name])
               FROM sys.columns c
                 JOIN sys.tables t ON c.object_id = t.object_id
               WHERE t.[name] = @TableName
                AND c.Name IN (SELECT CV.ColumnName FROM @ColVals CV)
               ORDER BY c.[name] --ORDERING IS IMPORTANT!
               FOR XML PATH(N'')),1,1,N'') +
             --This is called the FOR XML STUFF technique. Have a Google if you don't understand what it's doing
             --or reply to my post.
             N')' + NCHAR(10); --NCHAR(10) is a line break
      --This is the "hard" bit, we need to get those values in
      --I'm not 100% happy on this, but I *think* we've avoided injection here. I am more than happy for someone to prove me wrong here though
      SET @SQL = @SQL + N'SELECT ' +STUFF((SELECT N',' + N'''' + REPLACE(ColumnValue,N'''',N'''''') + N''''
                  FROM @ColVals
                  ORDER BY ColumnName
                  FOR XML PATH(N'')),1,1,N'') + N';';
      PRINT @SQL;
      EXEC sp_executesql @SQL;
    GO
    --Now to test
    DECLARE @ValuesToInsert crud_columns_and_values;
    INSERT INTO @ValuesToInsert
    VALUES ('ID','3'),('String','C');
    EXEC crud_insert_operation @TableName = 'Test', @ColVals = @ValuesToInsert;
    GO
    DECLARE @ValuesToInsert crud_columns_and_values;
    INSERT INTO @ValuesToInsert
    VALUES ('ID','4'),('String','D''d');
    EXEC crud_insert_operation @TableName = 'Test', @ColVals = @ValuesToInsert;
    GO
    --Better check that as well!
    EXEC crud_select_operation @TableName = 'Test'; --4 rows!
    GO
    --Ok, finally, the UPDATE
    CREATE PROC crud_update_operation @TableName varchar(100), @ID int, @ColVals crud_columns_and_values READONLY
    AS
      DECLARE @SQL nvarchar(4000);
      --Start off with the basics again
      SELECT @SQL = N'
      UPDATE ' + QUOTENAME([name]) + NCHAR(10)--Quote the table name, incase it has special characters
      FROM sys.tables --We use sys.tables, to ensure the table exist. If it doesn't exist, then NULL the returned (and no SQL is run)
      WHERE [name] = @TableName;
      --Again, I'm not 100% happy on this, but I *think* we've avoided injection here. I am more than happy for someone to prove me wrong here though
      SET @SQL = @SQL + N'SET ' + STUFF((SELECT N',' + QUOTENAME(c.name) + N' = ''' + REPLACE(CV.ColumnValue,N'''',N'''''') + N''''
                 FROM sys.columns c
                   JOIN sys.tables t ON c.object_id = t.object_id
                   JOIN @ColVals CV ON c.[name] = CV.ColumnName
                 WHERE t.[name] = @TableName
                 
                 ORDER BY c.[name] --ORDERING IS IMPORTANT!
                 FOR XML PATH(N'')),1,1,N'') + N';'
      PRINT @SQL;
      EXEC sp_executesql @SQL;
    GO
    DECLARE @ValuesToUpdate crud_columns_and_values;
    INSERT INTO @ValuesToUpdate
    VALUES ('String','E''e');
    EXEC crud_update_operation @TableName = 'Test', @ID = 4, @ColVals = @ValuesToUpdate;
    EXEC crud_select_operation @TableName = 'Test', @ID = 4; --Value has E'e!!!
    GO
    --Clean up
    USE [master];
    DROP DATABASE Crud;
    GO

    thanks Alot TOM 
    great working 
    but have a problem because this script will working in c# asp.net web app
    i think delete and select will succes work 
    but insert and update need first look table have a this data in sql table?if have dont need need insert or 
    have't then insert 
    and need pass parameter thats structure 
    exec dbo.ourproc _insert  @tablenmae='xxx',coumns='col1,col2,....coln' ,values='val1,val2....valn'
    can we create thi structure in our sroed proc ?
    than you very much for discussion and helping

  • You can define table parameters in C#. I'd suggest keeping with the Custom Table Data Type and incorporating it into your application, rather than a delimited string.

    Have a research yourself on how to achieve this first.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

Viewing 15 posts - 1 through 15 (of 32 total)

You must be logged in to reply to this topic. Login to reply