.NET Architecture Guides

Learn how to build production-ready .NET apps with free application architecture guidance.

from : https://dotnet.microsoft.com/learn/dotnet/architecture-guides

ROW_NUMBER() OVER Not Fast Enough With Large Result Set, any good solution?

Years back, while working with Sql Server 2000, which did not have this function, we had the same issue.

We found this method, which at first look seems like the performance can be bad, but blew us out the water.

Try this out

DECLARE @Table TABLE(
        ID INT PRIMARY KEY
)

--insert some values, as many as required.

DECLARE @I INT
SET @I = 0
WHILE @I < 100000
BEGIN
    INSERT INTO @Table SELECT @I
    SET @I = @I + 1
END

DECLARE @Start INT,
        @Count INT

SELECT  @Start = 10001,
        @Count = 50

SELECT  *
FROM    (       
            SELECT  TOP (@Count)
                    *
            FROM    (
                        SELECT  TOP (@Start + @Count)
                                *
                        FROM    @Table
                        ORDER BY ID ASC
                    ) TopAsc
            ORDER BY ID DESC
        ) TopDesc
ORDER BY ID

from : https://stackoverflow.com/questions/1897436/row-number-over-not-fast-enough-with-large-result-set-any-good-solution

Creating Stored Procedure with TableName as parameter

ALTER PROCEDURE dbo.TableSelect -- always use schema prefix!
    @TableName NVARCHAR(512) -- this must be a Unicode data type!
AS
BEGIN
    SET NOCOUNT ON;

    -- should at least validate that the @TableName is valid
    -- which can at least somewhat help avoid SQL injection
    IF OBJECT_ID(@TableName) IS NULL
    BEGIN
      RETURN;
    END

    DECLARE @String NVARCHAR(4000);-- should always use Unicode here too
        -- and always use semi-colons

    SET @String = N'Update ' + @TableName + N' Set Internet = 1';
        -- needed a space here ---------------^
        -- which PRINT @String would have shown you.

    EXEC sys.sp_executesql @String;
        -- sp_executesql preferred over EXEC()
END
GO

from : https://dba.stackexchange.com/questions/105705/creating-stored-procedure-with-tablename-as-parameter

Set variables in stored procedure using EXECUTE sp_executesql

While this doesn't need to be dynamic SQL:

ALTER PROCEDURE [dbo].[selectRecords]
  @psID INT
AS
BEGIN
  SET NOCOUNT ON; 

  DECLARE @rc INT;

  SELECT @rc = COUNT(Name) 
    FROM dbo.Asset_Log
    WHERE ID = @psID;
END
GO

I'll assume there is other logic here that you haven't shared that makes it a necessity. If that is the case:

ALTER PROCEDURE [dbo].[selectRecords]
  @psID INT
AS
BEGIN
  SET NOCOUNT ON; 

  DECLARE 
    @rc     INT = 0,
    @sql    NVARCHAR(1000),
    @params NVARCHAR(1000);

  SET @params = N'@psID INT, @rc INT OUTPUT';

  SET @sql = N'SELECT @rc = COUNT(Name)
    FROM dbo.Asset_Log
    WHERE ID = @psID;';

  EXEC sp_executesql @sql, @params, @psID, @rc OUTPUT;
END
GO

from : https://stackoverflow.com/questions/11507413/set-variables-in-stored-procedure-using-execute-sp-executesql