C# SQL Top as Parameter

C# SQL Top as parameter

In SQL Server 2005 and above, you can do this:

SELECT TOP (@topparam) * from table1

How to seperate the ''select top * x from query string?

In order to avoid string concatenation to make the SQL statement you could (and IMHO you should) use a parametrized query. It is a security risk called SQL Injection you should always avoid. For example:

string commandText = @"
SELECT * FROM dbo.TABLE
ORDER BY orderColum
OFFSET @offset ROWS
FETCH NEXT @page ROWS ONLY";

command.CommandText = commandText;
command.Parameters.Add(SqlDbType.Int, "@offset").Value =  (request.Page - 1) * request.PageSize;
command.Parameters.Add(SqlDbType.Int, "@page").Value =  request.PageSize;

Please do note that parametrized queries do have a bonus, the database engine will likely do an execution plan for it and future queries using the same CommandText would execute faster because of that plan regarless of having different values in their parameters.

Set variable for Select Top in SQL Server from a variable in code behind in ASP.NET

Assuming you have a SQL data source declared in the front end.

<asp:SqlDataSource ID="SqlDataSource" runat="server">
</asp:SqlDataSource>

In the code behind of your page add to the appropriate event code something like this to programmatically set the SQL data source command.

string sTop = "10";
SqlDataSource.SelectCommand = "SELECT Top " + sTop + " * FROM dbo.clients";

Also, consider using a DataTable or DataSet instead.

Dynamically sql query to select top (n) elements

if you are writing query in your c# code you can use:

int topCount=1000;

string query= "SELECT TOP ("+i.toString()+") * FROM DSMS_Log.dbo.LoggingDetail WITH(NOLOCK) ORDER BY LogTime DESC" 

But if you want to send parameter to your SP then you can use:

Declare @i int=1000;

 SELECT TOP (@i) * FROM DSMS_Log.dbo.LoggingDetail WITH(NOLOCK)  ORDER BY LogTime DESC

Dapper Parameter replace not working for Top

In SQL Server any top expression other than a numeric constant needs to be in parentheses.

SELECT TOP (@MaxLimit) FROM ...

C# | Use parameter in SqlCommand query

You need to add a SqlParameter and set its value:

public IEnumerable<IKarakter> GetSortedKarakters(string givenStringFromUser)
{
    using (SqlConnection connection = GetConnection())
    {
        var command = new SqlCommand("SELECT TOP 2 * FROM Karakter WHERE KarakterSoort = @UserInput ORDER BY NEWID();", connection);
        command.Parameters.Add("@UserInput", SqlDbType.VarChar, 100).Value = givenStringFromUser;

        connection.Open();
        var reader = command.ExecuteReader();
        var sortedKarakters = new List<IKarakter>();

        while (reader.Read())
        {
            var karakter = new KarakterDTO
            {
                KarakterId = (int)reader["KarakterId"],
                KarakterSoort = reader["KarakterSoort"]?.ToString(),
                KarakterNaam = reader["KarakterNaam"]?.ToString()
            };

            sortedKarakters.Add(karakter);
        }

        connection.Close();

        return sortedKarakters;
    }
}

How to add SQL parameter with MAX value in C#

Just use VARCHAR or NVARCHAR without specifying the size, which is default to MAX, but should not exceed 8000 for VARCHAR and 4000 for NVARCHAR

Use of SqlParameter in SQL LIKE clause not working

What you want is:

tblCustomerInfo.Info LIKE '%' + @SEARCH + '%'

(or edit the parameter value to include the % in the first place).

Otherwise, you are either (first sample) searching for the literal "@SEARCH" (not the arg-value), or you are embedding some extra quotes into the query (second sample).

In some ways, it might be easier to have the TSQL just use LIKE @SEARCH, and handle it at the caller:

command.Parameters.AddWithValue("@SEARCH","%" + searchString + "%");

Either approach should work.

Add SqlParameter to bind LIKE '%@x%'

I think @vendor is being treated as a literal in your query instead of a parameter.

Try defining your query as follows:

string strQuery =
   "select TOP 500 * from [mike_db].[dbo].[na_pe_sql_import] where vendname like '%' + @vendor + '%'";

Then add the parameter like this:

cmd.Parameters.AddWithValue("@vendor", search);

---

Related Topics