What is wrong with this T-Sql statement

Question

What is wrong with this T-Sql statement

Ben Tam 216

Dear All,

What's wrong with the following select statement?

string M_Surname="";
string M_GivenName="";
string M_MiddleName="";
int M_StudentID=0
string sSql = "SELECT Top 14 StudentID,Surname,GivenName,MiddleName,ChiName,Remarks FROM Student "

"Order by Surname,GivenName,MiddleName,StudentID "
"Where Pad(Surname,50)+Pad(GivenName,50)+Pad(MiddleName,50)+Cast(StudentID as Char(8))>"
M_Surname.PadRight(50, ' ') + M_GivenName.PadRight(50, ' ') + M_MiddleName.PadRight(50, ' ') + M_StudentID.PadLeft(8, ' ');

TIA

Viorel 127K Reputation points

2021-09-20T05:04:22.037+00:00

I think that Order By must be put after Where part and you must use Parameterised Queries (with a single parameter). Or at least put apostrophes around the concatenated value after ‘>’.
Timon Yang-MSFT 9,611 Reputation points

2021-09-20T06:31:22.387+00:00
@Ben Tam
In addition, M_StudentID is an int value, and we cannot directly use PadLeft on it.
This is the sql statement generated by this code:

SELECT Top 14 StudentID,Surname,GivenName,MiddleName,ChiName,Remarks FROM Student Order by Surname,GivenName,MiddleName,StudentID Where Pad(Surname,50)+Pad(GivenName,50)+Pad(MiddleName,50)+Cast(StudentID as Char(8))>TestM_Surname TestM_GivenName TestM_MiddleName 1

This seems a bit confusing. If you copy the generated sql to the database for inspection, what error will be prompted?
Which database do you use? I can't find information about Pad function.
Ben Tam 216 Reputation points

2021-09-20T14:14:37.677+00:00

@Olaf Helper @Timon Yang-MSFT @Viorel

Thanks for your replies and helps. The issue has already been solved.

Answer accepted by question author

0 additional answers

Your answer

Viorel 127K Reputation points

2021-09-20T05:04:22.037+00:00

I think that Order By must be put after Where part and you must use Parameterised Queries (with a single parameter). Or at least put apostrophes around the concatenated value after ‘>’.
Timon Yang-MSFT 9,611 Reputation points

2021-09-20T06:31:22.387+00:00

@Ben Tam
In addition, M_StudentID is an int value, and we cannot directly use PadLeft on it.
This is the sql statement generated by this code:

SELECT Top 14 StudentID,Surname,GivenName,MiddleName,ChiName,Remarks FROM Student Order by Surname,GivenName,MiddleName,StudentID Where Pad(Surname,50)+Pad(GivenName,50)+Pad(MiddleName,50)+Cast(StudentID as Char(8))>TestM_Surname TestM_GivenName TestM_MiddleName 1

This seems a bit confusing. If you copy the generated sql to the database for inspection, what error will be prompted?
Which database do you use? I can't find information about Pad function.
Ben Tam 216 Reputation points

2021-09-20T14:14:37.677+00:00

@Olaf Helper @Timon Yang-MSFT @Viorel

Thanks for your replies and helps. The issue has already been solved.

Answer 1

Olaf Helper 47,621

What's wrong with the following select statement?

Nearly everthing. The command order ist wrong, right is SELECT FROM WHERE ORDER.
And PAD isn't a valid Transact SQL function.

0 comments

Share via

What is wrong with this T-Sql statement

0 additional answers

Your answer