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