Home Forums SQL Server 2017 SQL Server 2017 - Development using araylist in stored proc RE: using araylist in stored proc
December 30, 2017 at 9:06 am
Thom A - Saturday, December 30, 2017 9:02 AMnatigsqlserver - Saturday, December 30, 2017 8:57 AMThom A - Saturday, December 30, 2017 8:51 AMI'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 = NULLASDECLARE @SQL nvarchar(4000); --This will hold our Dynamic SQLDECLARE @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 statementSELECT @SQL = N'SELECT *FROM ' + QUOTENAME([name]) --Quote the table name, incase it has special charactersFROM 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 IDIF @ID IS NOT NULL BEGINSET @SQL = @SQL + N'WHERE id = @dID;'; --This adds a WHERE clauseSET @parm = N'@dID int'; --And that's the definition of our parameter in our Dynamic SQLPRINT @SQL;EXEC sp_executesql @SQL, @parm, @dID = @ID; --And the bit that runs it all. We're much safer using sp_executesqlEND ELSE BEGIN--This is is there is no IDSET @SQL = @SQL + N';'; --Future proofingPRINT @SQL;EXEC sp_executesql @SQL;ENDGOthank you very much
but i need insert and update
operationYes, 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?