Query with parameters in C# (using SQL Server)

Question

I have a problem with query in C#.

I have this part of code :

string query1 = @"
            SELECT TOP @howManyRows * FROM
            (
            SELECT
               PRODUCTCODE_.ID_ AS PRODUCTCODE_ID_,
               PRODUCTCODE_.CATEGORY_ AS CATEGORY_,
               PRODUCTCODE_.DESCRIPTION_ AS DESCRIPTION_,
               PRODUCTCODE_.MANUFACTURER_ AS MANUFACTURER_,
               PRODUCTLINE_.CREATION_DATE_ AS CREATION_DATE_,
               ROW_NUMBER() OVER (ORDER BY PRODUCTCODE_.CATEGORY_) AS ROWNUMBER_,
               TOTALROWS_ = COUNT(*) OVER()
            FROM
               PRODUCTCODE_
            INNER JOIN
               PRODUCTLINE_ ON PRODUCTLINE_.ID_ = PRODUCTCODE_.PRODUCTLINE_ID_        
            ) _tmpList
            WHERE 
               ROWNUMBER_ >= @startingWith
               ORDER BY CATEGORY_
            ";


SqlParameter param1 = new SqlParameter();
param1.ParameterName = "@howManyRows";
param1.Value = resultPerPage; //`resultPerPage` is an integer function parameter

SqlParameter param2 = new SqlParameter();
param2.ParameterName = "@startingWith";
param2.Value = startsWith;  //`startWith` is an integer function parameter

SqlCommand cmd = new SqlCommand( query1, connect );
cmd.Parameters.Add( param1 );
cmd.Parameters.Add( param2 );

When debug arrived to SqlDataReader reader = cmd.ExecuteReader(); then the exception is thrown:

Incorrect syntax near @howManyRows ...

Why ? I defined and added howManyRows with Parameters property.

Where is my mistakes ?

Fraz Sundal · Accepted Answer · 2012-01-18 13:14:56Z

7

Change your top query syntax from

SELECT TOP @howManyRows * FROM

to

SELECT TOP (@howManyRows) * FROM

answered Jan 18, 2012 at 13:14

Fraz Sundal

10.4k22 gold badges87 silver badges133 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Paul Alan Taylor Over a year ago

Awesome. Never knew you could do this.

Snake Eyes Over a year ago

@Fraz: the () is needed to @startingWith from WHERE clause ?

gbn · Accepted Answer · 2012-01-18 13:15:41Z

3

You need parenthesis to parametrise TOP

SELECT TOP (@howManyRows) * FROM

answered Jan 18, 2012 at 13:15

gbn

434k84 gold badges602 silver badges690 bronze badges

Comments

Dave Markle · Accepted Answer · 2012-01-18 13:15:01Z

2

Try wrapping the parameter specified next to TOP in parentheses, like so:

SELECT TOP (@howManyRows) * FROM

answered Jan 18, 2012 at 13:15

Dave Markle

98.3k20 gold badges152 silver badges172 bronze badges

Comments

user208567 · Accepted Answer · 2012-01-18 13:15:01Z

1

This will work.

SELECT
           PRODUCTCODE_.ID_ AS PRODUCTCODE_ID_,
           PRODUCTCODE_.CATEGORY_ AS CATEGORY_,
           PRODUCTCODE_.DESCRIPTION_ AS DESCRIPTION_,
           PRODUCTCODE_.MANUFACTURER_ AS MANUFACTURER_,
           PRODUCTLINE_.CREATION_DATE_ AS CREATION_DATE_,
           ROW_NUMBER() OVER (ORDER BY PRODUCTCODE_.CATEGORY_) AS ROWNUMBER_,
           TOTALROWS_ = COUNT(*) OVER()
        FROM
           PRODUCTCODE_
        INNER JOIN
           PRODUCTLINE_ ON PRODUCTLINE_.ID_ = PRODUCTCODE_.PRODUCTLINE_ID_        
        ) _tmpList
        WHERE 
           ROWNUMBER_ between @startingWith and (@startingWith + @howManyRows)
           ORDER BY CATEGORY_

answered Jan 18, 2012 at 13:15

user208567

Comments

Mikael Eriksson · Accepted Answer · 2012-01-18 13:15:44Z

1

You need to add a couple of parenthesis to make it work.

SELECT TOP (@howManyRows) * FROM

answered Jan 18, 2012 at 13:15

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

Comments

Oleg Dok · Accepted Answer · 2012-01-18 13:17:54Z

1

Use SELECT TOP(@howManyRows) syntax

answered Jan 18, 2012 at 13:17

Oleg Dok

21.9k4 gold badges48 silver badges55 bronze badges

Comments

Community · Accepted Answer · 2020-06-20 09:12:55Z

1

You can do

SELECT TOP (@howManyRows) * FROM

But this really depends on the Database Server you are using. For example, this is only supported from MSSQL Server 2005 upwards

If this does not work you can do the following...

You can include this into your query string. But this can result in Sql Injection if you don't check the value of your variable.

Sample

int top = 10;
Int32.TryParse(howManyRows.ToString(), out top);

string query1 = "SELECT TOP " + top.ToString() + @" * FROM
(
SELECT
    PRODUCTCODE_.ID_ AS PRODUCTCODE_ID_,
    PRODUCTCODE_.CATEGORY_ AS CATEGORY_,
    PRODUCTCODE_.DESCRIPTION_ AS DESCRIPTION_,
    PRODUCTCODE_.MANUFACTURER_ AS MANUFACTURER_,
    PRODUCTLINE_.CREATION_DATE_ AS CREATION_DATE_,
    ROW_NUMBER() OVER (ORDER BY PRODUCTCODE_.CATEGORY_) AS ROWNUMBER_,
    TOTALROWS_ = COUNT(*) OVER()
FROM
    PRODUCTCODE_
INNER JOIN
    PRODUCTLINE_ ON PRODUCTLINE_.ID_ = PRODUCTCODE_.PRODUCTLINE_ID_        
) _tmpList
WHERE 
    ROWNUMBER_ >= @startingWith
    ORDER BY CATEGORY_
";

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Jan 18, 2012 at 13:17

dknaack

60.7k28 gold badges166 silver badges211 bronze badges

1 Comment

Snake Eyes Over a year ago

Yes, I used that but at every line of SQL syntax (described above) I have to use " " and + symbol that is very difficult.

Collectives™ on Stack Overflow

Query with parameters in C# (using SQL Server)

7 Answers 7

2 Comments

Comments

Comments

Comments

Comments

Comments

Sample

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

2 Comments

Comments

Comments

Comments

Comments

Comments

Sample

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related