Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mary_itohan
Posting Yak Master
191 Posts |
 Posted - 2008-09-14 : 18:50:38
|
Hello,I have the following code, and wondering whats the best way or most efficient way of running it ?Declare @s varchar(max)select @s = isnull(@s + ',', '') +cast(CountryCode as varchar(30))+'-'+ cast(NetworkCode as varchar(30))+'-'+cast(Length_Total as varchar(20))+'-'+cast(NetworkName as varchar(30)) from Supported_Networksprint @s thanks_____________________Yes O ! |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2008-09-14 : 20:35:05
|
| Once you decide to perform conversions (and store single values in variables) you loose the efficiencies that Sql Server (and any RDBMS) offers. So assuming you need this value assigned to the variable then there is not many better choices for doing it than what you have. The only suggestion may be to check your underlying datatypes, if any are already varchar then changing the length to 30 is unnecessary. (since they are VARchar)Unless there is only one row in [Supported_Networks] it looks like you omitted your WHERE clause. Without a WHERE clause the value will end up representing just the last row processed by your SELECT statement.I noticed that several of your posts deal with variable assignment. Not sure what you're up to but a common mistake people new to sql server make is to not realize that set based operations are possible most of the time and that they are far more efficient than a procedural, row-by-row, value-by-value approach.Be One with the OptimizerTG |
 |
|
|
mary_itohan
Posting Yak Master
191 Posts |
Posted - 2008-09-14 : 20:39:45
|
| they are all integers. Sorry i forgot to put the where clause. So whats the best approach, Pls advice.Thanks_____________________Yes O ! |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-09-15 : 00:39:02
|
It depends on the scope.Are you doing this in a function? To make a csv list?Then the inswer is no. There are more efficient ways to do this in SQL Server 2005 usin the new XML datatype.See http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254
E 12°55'05.63"N 56°04'39.26" |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-09-15 : 01:09:12
|
| I usually created csv list using the FOR XML PATH approach |
|
|
|
mary_itohan
Posting Yak Master
191 Posts |
Posted - 2008-09-15 : 17:55:42
|
| thanks a lot TG, Peso and Visakh for the advice_____________________Yes O ! |
|
|
|
|
|
|
Whats the best way of converting a string ?