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 |
|
kiddoOnSQL
Starting Member
16 Posts |
 Posted - 2011-06-22 : 02:00:20
|
| I have a requirement to return a "string" of values based on whether a value exists in some columns or not. If a value exists, then it has to be appended to a variable and finally displayed.Is there a way to optimise or do this in a better manner? As you can see I end up executing the select statement a few times (and I have 7 more columns that I haven't listed in this example). This block of sql will runs for at least 50 records per time and it doesn't look or seem very efficient to me.Thanks in advance-------------------------------------------------------------------DECLARE @payitemval1 varchar(100)DECLARE @payitemval2 varchar(100)DECLARE @payitemval3 varchar(100)Declare @paysummary varchar(100)Set @payitemval1 = (Select Ord from Table1 where TimesheetNo= '0604531887')Set @payitemval2 = (Select THalf from Table1 where TimesheetNo= '0604531887')Set @payitemval3 = (Select TDouble from Table1 where TimesheetNo= '0604531887')If Cast(@payitemval1 as numeric) <> 0 Set @paysummary = 'Ord: ' + Cast(@payitemval1 as varchar)If Cast(@payitemval2 as numeric) <> 0 Set @paysummary = @paysummary + ' , THalf: ' + Cast(@payitemval2 as varchar)If Cast(@payitemval3 as numeric) <> 0 Set @paysummary = @paysummary + ' , TDouble: ' + Cast(@payitemval3 as varchar)select @paysummary |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2011-06-22 : 02:14:13
|
Try:select@payitemval1 = Ord,@payitemval2 = THalf,@payitemval3 = TDoublefrom Table1 where TimesheetNo= '0604531887'
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
kiddoOnSQL
Starting Member
16 Posts |
Posted - 2011-06-22 : 20:05:47
|
| Thanks WebFred, that works beautifully! |
|
|
|
|
|
|
optimise SQL query