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