using araylist in stored proc

Question

using araylist in stored proc

natigsqlserver

SSC-Addicted

Points: 488
More actions
December 30, 2017 at 6:57 am

#334185

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)

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

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

Thom A SSC Guru Points: 99130 More actions · Answer 1

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

natigsqlserver SSC-Addicted Points: 488 More actions · Answer 2

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

Thom A SSC Guru Points: 99130 More actions · Answer 3

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

Thom A SSC Guru Points: 99130 More actions · Answer 4

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

Thom A SSC Guru Points: 99130 More actions · Answer 5

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

natigsqlserver SSC-Addicted Points: 488 More actions · Answer 6

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

Thom A SSC Guru Points: 99130 More actions · Answer 7

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

natigsqlserver SSC-Addicted Points: 488 More actions · Answer 8

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?

Thom A SSC Guru Points: 99130 More actions · Answer 9

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

natigsqlserver SSC-Addicted Points: 488 More actions · Answer 10

natigsqlserver

SSC-Addicted

Points: 488

December 30, 2017 at 9:13 am

#1973958

ok
thank you very much
i'm waiting

natigsqlserver SSC-Addicted Points: 488 More actions · Answer 11

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

Thom A SSC Guru Points: 99130 More actions · Answer 12

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

natigsqlserver SSC-Addicted Points: 488 More actions · Answer 13

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

Thom A SSC Guru Points: 99130 More actions · Answer 14

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