This view is then going to be used to update a table with only one record for each 'code'. i.e. NewTable = code, add1, add2, city, prov, postal, financialvalue, history value1, history value2
My current stumbling block is:
One of the fields in table1 is a free format address field (address).
eg. could be (street addres, city prov, postal)
or
could be (street address 1, address2, address 3, city prov, postal)
I want to be able to assign individual components of the address to corresponding fields
if # of commas = 2 then
address1 = substring(address,1, position of first comma)
cityprov = substring(address,possition of first comma, position of second comman)
postal = substring(address rest of field)
I have a UDF which returns the number of commas but I cannot figure out how to use either a nested case statement to parse the string...
ANy ideas on how best to accompish this?
( this table is needed for some leacy software which can only handle one record with all infor....
Hope someone can help... I need a function to parse a string using a beginning character parameter and an ending character parameter and extract what is between them. For example.....
Here is the sample string: MFD-2fdr4.zip
CREATE FUNCTION Parse(String, '-' , '.') ..... .... ..... END
I've the following query. I'm using the yellow highlighted to join 2 tables, as these tables dont have a relationship between them. The format of the name field is 'AAAA-BBBBBB-123' here A will be only 4 chars, followed by '-' B can be any number of chars again followed by '-' and the last is the id which I'm using to do a join. This query will fail if the id is just 1 digit as its taking the last 3 chars as the id. I dont know how to get the id from the right using charindex. Charindex would search for the first occurence of '-' from the right and get the chars after hypen i.e 123. How can this be achieved?
SELECT id AS 'ID', name AS 'name', sequence AS 'num' FROM FirstTable A INNER JOIN SecondTable q ON (CONVERT(INT,RIGHT(name,3))= a.id) INNER JOIN ThridTable t ON(t.id = q.id) INNER JOIN FourthTable s ON (q.name = s.name ) WHERE A.id = @ID AND t.name=LEFT(s.name,((CHARINDEX('-',s.name))-1)) ORDER BY 'ID','num'
One more question on this is: Is this a good way of joining tables? If I dont use this I've a very large query containing unions. Which one should be bug-free and more efficient?
Hello All,I'm trying to parse for a numeric string from a column in a table. WhatI'm looking for is a numeric string of a fixed length of 8.The column is a comments field and can contain the numeric string inany positionHere's an example of the values in the column1) Fri KX 3-21-98 5:48 P.M. arrival Cxled ATRI #27068935 3-17-982) wed.kx10/26 Netrez 95860536Now I need to parse through these lines and return only the 8 digitnumbers in itThe result set should be2706893595860536This is what I've done so farDeclare @tmp table(Comments_Txt varchar(255))Insert into @tmpselect Comments_Txt from Reservationselect * FROM @tmp where Comments_Txtlike ('%[0-9][0-9][0-9][0-9][0-9][0**9]%')But it returns the entire comments field in the result set. What I needis a way to return just those 8 digits.Any Ideas??Thanks in advance!!!
SELECT case when tab1.col2=tab1.col3 and tab1.col3!=0 then (SELECT tab3.col3 FROM tab3) else (case when tab5.col2=tab5.col6 then (SELECT tab7.col1 FROM tab7) else (case when tab1.col2=tab1.col3 then tab1.col4+7 end) end as value From tab5, tab1 WHERE tab1.col1=tab5.col1
I get the error as- Server: Msg 156, Level 15, State 1, Line 6 Incorrect syntax near the keyword 'as'. please help me.
I've got a SP that selects the best price from a table that has all info collected into it. Selecting the price is easy, I use COALESCE.
But I want to have a column next to it that contains which price that was choosen. I used CASE and nested it... worked fine until I reached the 10th level, there is a limit there.
"Case expressions may only be nested to level 10."
I'm sure som people will puke when they see this code and I'm open to suggestions on how to do it in another way. I can always do it in two queries, but it should be possible to do it in one.
I was looking at IF, THEN, ELSE, but I don't find any way to use it in a query, just to determine WHICH query will be run.
Here is my SP (how can I get it in a nice grey area like som people post?):
CREATE PROCEDURE dbo.ProcCOST_SET_TC AS
/* Empty TC table */ truncate table dbo.COST_TC
/* Collect info */ INSERT INTO dbo.COST_TC SELECT REGION,PROJECT,CPN, COALESCE ( Contract_usd, SITEINPUT_sitecontract_usd, SITEINPUT_lastPO_usd, SITEINPUT_lastreceipt_usd, SITEINPUT_other_usd, SITEINPUT_wac_usd, SYSTEM_Min_ContractPrice_usd, SYSTEM_Min_OpenOrder_usd, SYSTEM_Last_Receipt_usd, SYSTEM_Min_WAC_usd, [BP Q-1] ), Case Contract_usd WHEN IsNull(Contract_USD,0) THEN 'Contract' ELSE Case SITEINPUT_sitecontract_usd WHEN IsNull(SITEINPUT_sitecontract_usd,0) THEN 'SITEINPUT Site Contract' ELSE Case SITEINPUT_lastPO_usd WHEN IsNull(SITEINPUT_lastPO_usd,0) THEN 'SITEINPUT Last PO' ELSE Case SITEINPUT_lastreceipt_usd WHEN IsNull(SITEINPUT_lastreceipt_usd,0) THEN 'SITEINPUT Last Receipt' ELSE Case SITEINPUT_other_usd WHEN IsNull(SITEINPUT_other_usd,0) THEN 'SITEINPUT Other' ELSE Case SITEINPUT_wac_usd WHEN IsNull(SITEINPUT_wac_usd,0) THEN 'SITEINPUT WAC' ELSE Case SYSTEM_Min_ContractPrice_usd WHEN IsNull(SYSTEM_Min_ContractPrice_usd,0) THEN 'Min Contract Price' ELSE Case SYSTEM_Min_OpenOrder_usd WHEN IsNull(SYSTEM_Min_OpenOrder_usd,0) THEN 'Min Open Order' ELSE Case SYSTEM_Last_Receipt_usd WHEN IsNull(SYSTEM_Last_Receipt_usd,0) THEN 'Last Receipt' ELSE Case SYSTEM_Min_WAC_usd WHEN IsNull(SYSTEM_Min_WAC_usd,0) THEN 'Min WAC' ELSE Case [BP Q-1] WHEN IsNull([BP Q-1],0) THEN 'BP Q-1' ELSE 'NO DATA' END END END END END END END END END END END FROM COST_AllInfo GO
I'm not sure if this is possible but i'm trying to nest a case statement but keep on getting any error: Incorrect syntax near the keyword 'Then'
Query: SELECT F_DIVISION_NO, Bound, Primary_SIR, Case When Bound Then Sum(CASE WHEN dbo.THIT_RATIO_DETL.DED_AGR_AM=0 THEN dbo.THIT_RATIO_DETL.DED_OCR_AM else dbo.THIT_RATIO_DETL.DED_AGR_AM end)end as Test
From ( Select DBO.THIT_RATIO_DETL.F_DIVISION_NO, Sum(CASE WHEN dbo.THIT_RATIO_DETL.STATUS_CD = "B" or dbo.THIT_RATIO_DETL.STATUS_CD = "K" Then 1 ELSE 0 END) as Bound,
Sum(CASE WHEN dbo.THIT_RATIO_DETL.DED_AGR_AM=0 THEN dbo.THIT_RATIO_DETL.DED_OCR_AM else dbo.THIT_RATIO_DETL.DED_AGR_AM end) as Primary_SIR
FROM dbo.THIT_RATIO_DETL Group by F_DIVISION_NO ) t
Hi, I'm not new to SQL however I get confused when I transfer from different DBMS like Oracle, SQL Server, Firebird, MySQL and Access.
in SQL Server T-SQL, How do I go about a nested select case like the ff: And I supposed to use this as source for a Cross-Tab.
CREATE PROCEDURE SP_SAMPLE( @DateStart datetime, @DateEnd datetime, @Param1 char(3), @Param2 char(1), @Param3 decimal(7,4)) AS BEGIN
SELECT Field1, Field2, Field3, CASE WHEN FieldSomething = 0.0000 THEN '*' WHEN FieldSomething < @Param3 THEN '_' ELSE CASE @Param2 WHEN 'A' THEN SomeOtherField1 WHEN 'B' THEN SomeOtherField2 WHEN 'C' THEN SomeOtherField3 END END Alias1 FROM SampleTable WHERE UPPER(Field2) = UPPER(@Param1) AND Field1 BETWEEN @DateStart AND @DateEnd ORDER BY Field2, Field3, Field1
I have two tables:tb_news---story_id-productlisttb_lookup---story_id-productwhen an insert command is run on tb_news, productlist field ispopulated with a value such as 'abc, def, de'when this happens, i need tb_lookup to be populated with seperaterecords for each product in productlist and the story_id from tb_news.Example:INSERT INTO tb_news (story_id, product_list)VALUES (12345, 'abc, def, de')Results:tb_news--12345, 'abc, def, de'tb_lookup--12345, 'abc'12345, 'def'12345, 'de'Ideally, I would like this to use recursion and give me tha ability tochange the delimiter at any time (might not always be a comma). someproducts may have a period in them. number of products is unknown andmight be 0 (field may be empty or NULL).
Hi,I would like to parse out each value that is seperatedby a comma in a field and use that value to join to another table.What would be the easiest way to do so without having towrite a function or routine ?EX.Table AAACOL1 COL21 11, 124, 1562 11, 505, 600, 700, ...Table BBBCOL1 COL211 Desc11124 Desc124156 Desc 156
I'am trying to program a nested CASE statements (if this is not possible, does anyone have any alternate suggestions ?) and I'm getting syntax errors. The statement is:
SELECT @cmdLine = CASE @BackupType WHEN 1 THEN Select @tmpStr = CASE @initFlag WHEN 1 THEN 'BACKUP Database ' + @databaseName + 'TO '+ @backupDeviceName + ' WITH INIT, NOUNLOAD, NAME = ' + @backupJobName + ' , SKIP , STATS = 10, NOFORMAT' ELSE 'BACKUP Database ' + @databaseName + 'TO '+ @backupDeviceName + ' WITH NOINIT, NOUNLOAD, NAME = ' + @backupJobName + ' , SKIP , STATS = 10, NOFORMAT' END WHEN 2 THEN Select @tmpStr = CASE @initFlag WHEN 1 THEN 'BACKUP Database ' + @databaseName + 'TO '+ @backupDeviceName + ' WITH DIFFERENTIAL, INIT , NOUNLOAD, NAME = ' + @backupJobName + ', SKIP, STATS = 10, NOFORMAT' ELSE 'BACKUP Database ' + @databaseName + 'TO '+ @backupDeviceName + ' WITH DIFFERENTIAL, NOINIT , NOUNLOAD, NAME = ' + @backupJobName + ', SKIP , STATS = 10, NOFORMAT' END WHEN 3 THEN Select @tmpStr = CASE @initFlag WHEN 1 THEN 'BACKUP Log ' + @databaseName + 'TO '+ @backupDeviceName + ' WITH INIT, NOUNLOAD, NAME = ' + @backupJobName + ', SKIP , STATS = 10, NOFORMAT' ELSE 'BACKUP LOG ' + @databaseName + 'TO '+ @backupDeviceName + ' WITH NOINIT, NOUNLOAD, NAME = ' + @backupJobName + ', SKIP , STATS = 10, NOFORMAT' END ELSE '' END
Hi i am having some trouble with a nested case statement, what i want to do is set the value of a new column called Result depending on a series of case statements. Basically i want to check Test.Webstatus = 'Rd' and FinalResult = 'true' if this is true i want it to set the value in the Results field to ReportableResult + '~' + ReportableUnitDisplay then go through all the limits fields adding either the value of the field or 'blank' onto the end of the value in the Results field, depending on if the limits field has Null or a value in it. Producing a value in the Results field similiar to: 10~kg:10:5:2:1 or 10~kg:blank:5:blank:1 etc
select ClientRef, Sample.WebStatus as SampleStatus, Analysis, FinalResult, Test.WebStatus, 'Result' = Case when Test.WebStatus = 'Rd' and FinalResult = 'true' then Case Case when UpperCriticalLimit is null then ReportableResult + '~' + ReportableUnitDisplay + ':blank' else ReportableResult + '~' + ReportableUnitDisplay + ':' + UpperCriticalLimit end Case when UpperWarningLimit is null then ReportableResult + '~' + ReportableUnitDisplay + ':blank' else ReportableResult + '~' + ReportableUnitDisplay + ':' + UpperWarningLimit end Case when LowerWarningLimit is null then ReportableResult + '~' + ReportableUnitDisplay + ':blank' else ReportableResult + '~' + ReportableUnitDisplay + ':' + UpperWarningLimit end Case when LowerCriticalLimit is null then ReportableResult + '~' + ReportableUnitDisplay + ':blank' else ReportableResult + '~' + ReportableUnitDisplay + ':' + LowerCriticalLimit end end when FinalResult = 'false' then Null else Test.WebStatus from Job inner join sample on Job.JobID = Sample.JobID inner join Test on Sample.SampleID = Test.SampleID left join Result on Test.TestID = Result.TestID
I have a Sproc. to get records and I was looking to use a nested CASE statement
The thing is that first I want to check the availability of single quote in my where statement and replace it with double quote. The second case is to check for a given condition and make some calculation. The following statement is not running. So would you mind helping me in this regard?
Thank you in advance:
(CASE WHEN CHARINDEX(''ca.caCode'',''' + replace(@WhereStmt, char(39), char(39) + char(39)) + ''') > 0 THEN ca.ClusterAmount ELSE dbo.vwGrantsMaster.CurrentValueTotalCost END CASE WHEN dbo.vwGrantsMaster.StatusCode IN (3) THEN (IsNull(dbo.vwGrantsMaster.CurrentValueTotalCost, 0))-(IsNull(dbo.vwGrantsMaster.AwardedTotalCostAmount,0)) ELSE dbo.vwGrantsMaster.CurrentValueTotalCost END) AS CurrentValueTotalCost,
Is it possible to use nested case statements in the SQL for your dataset when you are using Reporting Services? I keep getting an error saying "Unable to parse expression" and my report won't run.
I know this is quite a complex statement, so at first I was worried that maybe I had brackets in the wrong places, but I've been through the code and made sure that every opening bracket has an equivalent closing bracket, and everything appears to be OK in that respect. So I'm thinking that maybe the structure of my nested case statements is incorrect? The inner case statement is necessary to calculate whether a transaction is due, overdue or not due. The outer case statement depends on the result of the inner case statement.
Basically, we only want the calculations following the "THEN" in the outer case statement to be executed if the result of the inner case statement is "not due" and Field2 is greater than zero. If either of those criteria aren't met, then it should go straight to the ELSE NULL END statement and stop.
Any way to parse out a text value (not varChar, using text data type) that is > than 8000 characters long? I'm looping through 1 big string passed to the DB that is pipe delimited, but I find myself needing the substring function to keep track of which segment I'm acting on (after an update, I then need to take that segment and remove it from the string)...but the subString function won't take anything larger than 8000 chars.
Say I have this string that is text data type...
'aaa|bbb|ccc|ddd|....'
..and so on, surpassing 8000 char length, how could you parse it out using the pipes as the delimter, then do an Update using that segment? Afterward, return to that string and find the next segment, then use it, and so on (in a loop). I tried using an update to set the string = replace(string, segmentJustUsed, '') to "erase" it, but replace can't take text as the datatype. Any help? Hope this isn't to confusing.
SP to parse a delimited string and insert the result in a table. I am using SQL Server 2008 R2. I have 2 tables - RawData & WIP. I have Robots on a manufacturing line capable of moving data to a DB. I move the raw data to RawData. On insert [RawData], I want to parse the string and move the contents to WIP as indicated below. I will run reports against the WIP Table.
Also, after the string is parsed, I'd like to change the Archive column, the _0 at the end of the raw string to 1 in the WIP table to indicate a successful parse.
Date Time Plant Program Line Zone Station BadgeID Message Alarm Archive ----------------------------------------------------------------------------------- 04102015 114830 10 13 9 8 6 99999 Test 1 1 1 04102015 115030 10 13 9 8 6 99999 Test 2 1 1
Server: Msg 125, Level 15, State 4, Line 16 Case expressions may only be nested to level 10.
I need to create a stored procedure that looks at two fields to generate a rating depending on each value. I have tried to use a case statement but am restricted to a certain number of nesting levels. Is there a way around this?
Code Snippetselect answerID,ram_severity, ram_probability, case when ram_severity='0' and ram_probability='A' then 10 else case when ram_severity='0' and ram_probability='B' then 10 else case when ram_severity='0' and ram_probability='C' then 10 else case when ram_severity='0' and ram_probability='D' then 10 else case when ram_severity='0' and ram_probability='E' then 10 else case when ram_severity='1' and ram_probability='A' then 10 else case when ram_severity='1' and ram_probability='B' then 10 else case when ram_severity='1' and ram_probability='C' then 10 else case when ram_severity='1' and ram_probability='D' then 10 else case when ram_severity='1' and ram_probability='E' then 10 else case when ram_severity='2' and ram_probability='A' then 10 else case when ram_severity='2' and ram_probability='B' then 10 else case when ram_severity='2' and ram_probability='C' then 10 else case when ram_severity='2' and ram_probability='D' then 100 else case when ram_severity='2' and ram_probability='E' then 100 else case when ram_severity='3' and ram_probability='A' then 10 else case when ram_severity='3' and ram_probability='B' then 10 else case when ram_severity='3' and ram_probability='C' then 100 else case when ram_severity='3' and ram_probability='D' then 100 else case when ram_severity='3' and ram_probability='E' then 1000 else case when ram_severity='4' and ram_probability='A' then 10 else case when ram_severity='4' and ram_probability='B' then 100 else case when ram_severity='4' and ram_probability='C' then 100 else case when ram_severity='4' and ram_probability='D' then 1000 else case when ram_severity='4' and ram_probability='E' then 1000 else case when ram_severity='5' and ram_probability='A' then 100 else case when ram_severity='5' and ram_probability='B' then 100 else case when ram_severity='5' and ram_probability='C' then 1000 else case when ram_severity='5' and ram_probability='D' then 1000 else case when ram_severity='5' and ram_probability='E' then 1000 end end end end end end end end end end end end end end end end end end end end end end end end end end end end end end as rating from table ua
Im sure there must be a way I just cant find it yet. Many thanks in advance
All- I'd be interested if any of you could advise as to how the nested CASE statement below could refactored to be more concise. (Works fine as is, but just interested to know!) Thanks! 1 SELECT headcount.person_id, person.last as Lname, person.first as Fname, 2 3 CASE headcount.coming WHEN 'False' THEN '0' ELSE 4 5 CASE person.is_adult WHEN 'True' THEN 6 7 CASE person.is_y_parent_or_kid WHEN 'True' THEN activity_session.usd_per_y_parent 8 ELSE activity_session.usd_per_guest_adult9 END10 ELSE11 12 CASE person.is_kid WHEN 'True' THEN13 14 CASE person.is_y_parent_or_kid WHEN 'True' THEN activity_session.usd_per_y_kid15 ELSE activity_session.usd_per_guest_kid16 END17 END18 END19 20 END AS 'Cost', 21 22 23 24 25 no_answer, not_coming, coming, wanted, comment, activity_session.act_session_id, for_sale, headcount_id FROM headcount26 27 INNER JOIN person28 ON headcount.person_id = person.person_id29 30 INNER JOIN activity_session31 ON headcount.act_session_id = activity_session.act_session_id
I have a query which works absolutely fine when connecting to an actual server:
WITH CLAIMDATA AS( SELECT DISTINCT DB_NAME() AS DBName, 'UA' AS Client, POLICY AS KMPONO,
[code]...
If i change the connector to REPLPROD (which is a linked server): From REPLPROD.XUNMDTAUAI.dbo.UPPOREP UP INNER JOIN REPLPROD.XUNMDTAUAI.dbo.UKKMREP UK
I get the error:
Msg 8180, Level 16, State 1, Line 1 Statement(s) could not be prepared. Msg 125, Level 15, State 4, Line 1
I have a question about what is possible with a prediction query against a nested table. Say I have a basic customer-product case and nested table mining model like so:
Mining Model DT_CustProd ( [Id] , [Gender] , [Age] [Products] Predict ( [ProductName] , [Quantity] ) ) Using Microsoft_Decision_Trees
I can write a query to find the probability of product (and quantity) A like so:
SELECT (select * from Predict(Products,INCLUDE_STATISTICS) where ProductName = 'A' )
FROM DT_CustProd
NATURAL PREDICTION JOIN
(SELECT 'M' AS [Gender], 27 AS [AGE] ) AS t
What if I know that the query customer (M,27) in question has purchased product B, how can I use that in the prediction join to predict product A? The fact that product B was purchased might influence the prediction, right?
I need to perform an update where there are multiple scenarios which determine the value that is entered. Below is a sort've psuedo code of how it needs to be.
Update MyTable SET MyColumn = CASE WHEN MyCol1 = 'Value1' Then NewValue Else WHEN MyCol1 <> 'Value1' And MyCol2 = 'Active' Then 'Value1'
In the scenario where MyCol1 <> Value1 and MyCol2 <> 'Active' then no update would occur and the original value would remain intact.
I am trying to select patient table as case and diseases table as nested to create an association model. i m getting following error.
Disease table cannot be used as a nested table because it does not have a many-to-one relationship with the case table. You need to create a many-to-one relationship between the two tables in the data source file.
i have created a relationship by dragging Disease_id from diseases table on Patient_id in patient table. when i am trying to select Patient_id as key, City as input, it is not showing disease_id to choose as a predict column.
please suggest me if i am doing anything wrong? i have not done any thing to do my datbase, just selected the tables i want to create an association model on and trying to create association model.
I am trying to erase some erroneous bad data in my table. The description column has a lot of </div>oqwiroiweuru</a> weird data attached to it, i want to keep the data to the left of where the </div> erroneous data begins
update MyTable set Description = LEFT(Description(CHARINDEX('<',Description)-1)) where myid = 1
that totally works.
update MyTable set Description = LEFT(Description(CHARINDEX('<',Description)-1)) where myid >= 2
gives me a Invalid length parameter passed to the LEFT or SUBSTRING function. The statement has been terminated error.
I have a problem in design the tables. My main task is to learn how to give the Match Score.
I have hundreds of dataset and one of them is like this:
Test Record Number: 19 Prospect ID = 254040088233400441105260031881009 Match Score = 95 Input Record Fielding ( eg wordnumber[Field] ) : 1[1] 2[1] 3[11] 4[11] 5[11] Prospect Word = 1 type = 1 match level = 4 input word = 1 input type = 1 Prospect Word = 2 type = 2 match level = 0 input word = NA input type = NA Prospect Word = 3 type = 3 match level = 4 input word = 2 input type = 1 Prospect Word = 4 type = 11 match level = 4 input word = 3 input type = 11 Prospect Word = 5 type = 13 match level = 4 input word = 4 input type = 11 Prospect Word = 6 type = 14 match level = 4 input word = 5 input type = 11
Now I have all my data stored in the DB and I seperated them into 3 tables and their structures are:
and the prspid in table 2 & 3 refers to the prospectID in table 1.What I did was setting:
a) prospect table as case table with id AS key, prospectID AS input & predictable;
b) and the other two as nested tables with inputword/inputfield AS key & input, prospectword/prospecttype/matchlevel/inputword/inputtype AS key & input .
But it shows error for having multiply key columns...
And also I am thinking about using the Naive bayes algorithm. Can I also have some suggestion on this?
i am trying to associate city in patient table --> disease in diseases table. I want to build association data mining model and use it on web form, such a way when the user enters city associated disease will be displayed.
should i select all 3 table to build the model? could help me to decide what tables should i select as Case and what tables as Nested? what attributes from the table should i select as key, input, predictive ?
i am using data mining tutorials on sqlserverdatamining.com to build this model. is there anything further during my model building i get into confusion? please suggest me where i can find complete resource or inform here.
i appreciate Mr.Jamie for his guidance so far in my academic project. i do have the book 'Data mining with sql server 2005'. I left with just one day to do this and document.
hoping someone could suggest. your help is much appreciated.
Hello. I'm trying to reduce some code in my stored procedure and I'm running into lots of errors. I'm somewhat of a novice with SQL and stored procedures so any help would be beneficial. I have a SP that gets a page of user data and is also called when sorting by one of the columns (this data is placed in a repeater, btw). I quickly learned that I wasn't able to pass in string parameters the way I had hoped in order to handle the ORDER BY and direction (ASC/DESC) so I'm trying to work around this. So far I've tried the following with many errors.WITH Users AS ( SELECT ROW_NUMBER() OVER (ORDER BY CASE WHEN @OrderBy='FirstName' AND @Direction='DESC' THEN (FirstName + ' DESC') WHEN @OrderBy='FirstName' THEN FirstName WHEN @OrderBy='LastName' AND @Direction='DESC' THEN (LastName + ' DESC') WHEN @OrderBy='LastName' THEN LastName END ) AS Row, UserID, FirstName, LastName, EmailAddress, [Role], Active, LastLogin, DateModified, ModifiedBy, ModifiedByName FROM vRF_Users )
SELECT UserID, FirstName, LastName, EmailAddress, [Role], Active, LastLogin, DateModified, ModifiedBy, ModifiedByName FROM Users WHERE Row BETWEEN @StartRowIndex AND @EndRowIndex
I've tried a combination of similar things with parenthesises, without, doing "THEN FirstName DESC" without concatenating anything, etc. I also tried: DECLARE @OrderByDirection varchar(32) DECLARE @DESC varchar(4) SET @DESC = ' DESC'
IF @Direction = 'DESC' BEGIN SET @OrderByDirection = (@OrderBy + @DESC) END And then writing my case statemet like this:ORDER BY CASE WHEN @Direction='DESC' THEN @OrderByDirection ELSE @OrderBy ENDObviously this didn't work either. Is there any way to gracefully accomplish this or do I just have to use a bunch of if/else statements and lots of redundant code to evaluate all my @OrderBy and @Direction parameters???
