Cfrgtkky

I am trying to update records and insert their audits to audit table.

For this purpose stored procedure waiting for above variables.

@m_obj_id INT,
@m_obj_code NVARCHAR(250),
@m_f_code NVARCHAR(250),
@m_nv NVARCHAR(4000),
@m_last_mod_by INTEGER,
@table_name SYSNAME,
--@where_clause NVARCHAR(4000)

Stored procedure formatting these variables as;

UPDATE @table_name SET @m_f_code=@m_nv WHERE id=@m_obj_id

And at last part inserting into audit.

I can use it with doing SELECT CONCAT and copying all the rows then execute.

But my goal is here not expecting @m_obj_id from user and replace it @where_clause. And use this @where_clause to get ids inside.

So far I tried;

    DECLARE @Sql NVARCHAR(MAX)

    DECLARE @RecordId int = 0

    BEGIN

    SET @SQL = N'

      SELECT @RecordId = MIN(id)

      FROM ' + @table_name + '

      WHERE id > @RecordId AND (' + @where_clause + ')

      IF @RecordId IS NULL BREAK

      SET @m_obj_id = @RecordId'

Exec sp_executesql @sql

But couldnt get far with it.

Then I tried something like;

DECLARE @RowsToProcess  int

DECLARE @CurrentRow     int

DECLARE @SelectCol1     int



DECLARE @sql NVARCHAR(MAX)

SET @sql = N'

DECLARE @table1 TABLE (RowID int not null primary key identity(1,1), col1 int )  

INSERT into @table1 (col1) SELECT id FROM ' + @table_name + ' Where ' + @where_clause + '

SET @RowsToProcess=@@ROWCOUNT'

EXEC sp_executesql @sql,

N'@RowsToProcess INT OUTPUT', @RowsToProcess OUTPUT



SET @CurrentRow=0

WHILE @CurrentRow<@RowsToProcess

BEGIN

    SET @CurrentRow=@CurrentRow+1

        DECLARE @sql2 NVARCHAR(MAX)

        SET @sql2 = N'

        SET @m_obj_id =

        (SELECT col1

        FROM @table1

        WHERE RowID=@CurrentRow)'

        EXEC sp_executesql @sql2

But still no luck.

Can I achieve this any how? I am trying to do this for more than it should be.
Thanks all.

The non-dynamic way to implement dynamic filtering on sql is the following:

where id=@m_obj_id or @m_obj_id is null

For a LOT of more details on how to choose between dynamic and non-dynamic sql on this, I recommend this article by Erland Sommarskog

I found a solution. Thanks everyone for responding.

I used a temp table like

DECLARE @RowsToProcess INTEGER

DECLARE @CurrentRow INTEGER

DECLARE @SelectCol1 INTEGER



CREATE TABLE #tmp (RowID INTEGER NOT NULL PRIMARY KEY IDENTITY(1,1), col1 int)



DECLARE @sql NVARCHAR(MAX)

SET @sql = N'

INSERT into #tmp (col1) SELECT id FROM ' + @table_name + ' Where ' + @where_clause + '

SET @RowsToProcess=@@ROWCOUNT'

INSERT INTO #tmp

EXEC sp_executesql @sql,

N'@RowsToProcess INT OUTPUT', @RowsToProcess OUTPUT



SET @CurrentRow=0

WHILE @CurrentRow<@RowsToProcess

BEGIN

    SET @CurrentRow=@CurrentRow+1

        SET @m_obj_id =

        (SELECT col1

        FROM #tmp

        WHERE RowID=@CurrentRow)



        Do stuff....