SQL Statement and Variables

Question

SQL Statement and Variables

Viewing 8 posts - 16 through 22 (of 22 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

This won't work if you have a mix of different datatypes. Case expression must return only 1 datatype. And unless I'm mistaken, it'll take the first case as the return type.

So if you have a varchar in the list of columns, then you'll have to cast all the ints, dates to varchar so that the proc works.. but then the sort is alphabetical instead of numerical. This is why I showed you the multiple case statements : the datatype doesn't matter in this case.

It doesn't matter when you use the dynamic sql, if the user can input whatever he wants in that variable then you're screwed. he can literally take control of the server if he wants (and is good).

Roy Rogers SSCertifiable Points: 7712 More actions · Answer 2

Ha I did not realize that how about this

SELECT col1, col2, col3

FROM #tbl

ORDER BY CASE @SortCol

WHEN 'col1' THEN CASE @SortYpe WHEN 'DESC' THEN 1 DESC ELSE 1 END

WHEN 'col2' THEN CASE @SortYpe WHEN 'DESC' THEN 2 DESC ELSE 2 END

WHEN 'col3' THEN CASE @SortYpe WHEN 'DESC' THEN 3 DESC ELSE 3 END

END

Regards,
gova

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

It still won't work. It'll sort by the numerical value 1,2,3... not the colid. Which if I remember corectly won't even be supported starting with Yukon.

You either sort it at the client (dataset should provide a sort method), or with my technic if you want to avoid dynamic sql.

Roy Rogers SSCertifiable Points: 7712 More actions · Answer 4

Oops. I just tested with your query with slight change. Null is implicit I would say. (I removed Else NULL part)

Thank You.

DECLARE @SortTable TABLE

(INTCol INT, VarcharCol VARCHAR(50), DateCol DateTime)

INSERT @SortTable VALUES (1, 'CCCCCC','02/01/2005')

INSERT @SortTable VALUES (2, 'AAAAAA','03/01/2005')

INSERT @SortTable VALUES (3, 'BBBBBB','01/01/2005')

DECLARE @sort VARCHAR(12)

DECLARE @SortType VARCHAR(12)

SELECT @sort = 'DateCol', @SortType = ''

SELECT * FROM @SortTable

ORDER BY

CASE @sort WHEN 'VarcharCol' THEN VarcharCol END,

CASE @sort WHEN 'INTCol' THEN INTCol END,

CASE @sort WHEN 'DateCol' THEN DateCol END

Regards,
gova

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

Just make sure you test to see which performs best in your environement. There's no clear cut answer to this problem (even if it should really be done at the client ).

Just stay as far aways from dynamic sql as you can and good luck.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 6

I'm not completely against the use of dynamic sql. However, it is way too often too quickly advocated while there are better static solutions available. Just read Erland's dynamic search order article. That's a very good example where to use what approach.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

Ninja's_RGR'us

SSC Guru

Points: 294069

June 1, 2005 at 2:07 pm

#562354

Gonna reread it... looks like I missed that part .

fasttrack SSCertifiable Points: 6071 More actions · Answer 8

Thank to everybody.....

yor suggestion have been very usefull.......

Thank