Transfer SQL to SQL SERVER stored procedure

Question

Can someone help me transfer the logic into an stored procedure, the entire syntax, this is the query I am using in access, i want to transfer to sql server 2008

SELECT CountOfColorValue, ColorTable*, IIf([CountOfColorValue]<7,"Invalid Color",[ColorCode]) AS COLOR STATUS
FROM INNER JOIN ColorTable ON (ColorLocation=ColorLocation) AND (ColorPart=ColorPart) AND (ColorCode=ColorCode);

Jignesh Trivedi · Answer

Hi As per my knowledge IIF is only support in sql server 2012 so you must use CASE WHEN.. try... SELECT CountOfColorValue ColorTable* case when ([CountOfColorValue] FROM INNER JOIN ColorTable ON (ColorLocation=ColorLocation) AND (ColorPart=ColorPart) AND (ColorCode=ColorCode) hope this help.

Vulpes · Answer

For two anonymous types to be equal both the property names and their types (inferred from the types of the expressions assigned to them) must be the same. That's probably not the case in this section: on new { KEY1 = ColorListB.TestLocation KEY2 = ColorListB.UutIdentification KEY3 = ColorListB.ColorTestPhase KEY4 = ColorListB.Pcode } equals new { KEY1 = FinalColorDataTable[COLOR_TEST_LOCATION] KEY2 = FinalColorDataTable[COLOR_IDENTIFICATION] KEY3 = FinalColorDataTable[COLOR_TEST_PHASE] KEY4 = FinalColorDataTable[ColorCode] } where I'd imagine that FinalColorDataTable's indexer has a return type of object. So I think you need to either cast the indexer return values to their actual types or if it's string you could apply the ToString() method instead. If Count is a property of ColorListB then VIEW_COLOR_STATUS should be expressed as follows: VIEW_COLOR_STATUS = (ColorListB.Count

David Smith · Answer

you forgot about the the join so I am going to give you the exact linq I am doing. var ColorList_A = from s in ColorRawTable.AsEnumerable() orderby s.Field (COLOR_TEST_LOCATION) s.Field (COLOR_IDENTIFICATION) s.Field (ColorCode) s.Field (COLOR_TEST_PHASE) group s by new { ColorValue= s.Field (ColorValue) ColorCode = s.Field (ColorCode) Environment = s.Field (COLOR_TEST_PHASE) ColorIdentification = s.Field (COLOR_IDENTIFICATION) ColorTestLocation = s.Field (COLOR_TEST_LOCATION) } into g select new { Count = g.Select(s => s[ColorValue]).Count() ColorValue= g.Key.ColorValue ColorCode = g.Key.ColorCode ColorTestPhase= g.Key.Environment ColorIdentification = g.Key.ColorIdentification  ColorTestLocation = g.Key.ColorTestLocation }; var ColorList_B = from s in ColorList_A.AsEnumerable() group s by new { ColorCode = s.ColorCode Environment = s.EnvironmentTestPhase ColorIdentification = s.ColorIdentification  ColorTestLocation = s.ColorTestLocation } into g select new { CountOfColorValue = g.Select(s => s.ColorValue).Count() ColorCode = g.Key.ColorCode ColorTestPhase= g.Key.Environment ColorIdentification = g.Key.ColorIdentification  ColorTestLocation = g.Key.ColorTestLocation }; Up to this point I am good below is where I am trying to incorporate the inner join and also the if condition IIf([CountOfColorValue] var FinalColorReportList = from productionListB in productionList_B.AsEnumerable() join FinalColorDataTable in ColorRawTable.AsEnumerable() on new { KEY1 = ColorListB.TestLocation KEY2 = ColorListB.UutIdentification KEY3 = ColorListB.ColorTestPhase KEY4 = ColorListB.Pcode } equals new { KEY1 = FinalColorDataTable[COLOR_TEST_LOCATION] KEY2 = FinalColorDataTable[COLOR_IDENTIFICATION] KEY3 = FinalColorDataTable[COLOR_TEST_PHASE] KEY4 = FinalColorDataTable[ColorCode] } select new { COLOR_TEST_DATA = ColorRawTable[COLOR_TEST_DATA] COLOR_WRITE_TIME = ColorRawTable[COLOR_WRITE_TIME] COLOR_IDENTIFICATION = ColorRawTable[COLOR_IDENTIFICATION] COLOR_REVISION = ColorRawTable[COLOR_REVISION] COLOR_SERIAL_NUMBER = ColorRawTable[COLOR_SERIAL_NUMBER] COLOR_TEST_STATUS = ColorRawTable[COLOR_STATUS] COLOR_START_TIME = ColorRawTable[COLOR_START_TIME] //VIEW_COLOR_STATUS = ColorListB.Count };

Jignesh Trivedi · Answer

Hi In LINQ consider below example. I have two class test and test1 and defination are. class test { public int id { get; set; } public string name { get; set; } } class test1 { public bool IsId { get; set; } public string name { get; set; } } now i add some value in of Test class. List tall = new List (); test t = new test { id = 0 name = jignesh }; test t1 = new test { id = 1 name = jignesh1 }; test t2 = new test { id = 2 name = jignesh2 }; test t3 = new test { id = 3 name = jignesh3 }; test t4 = new test { id = 4 name = jignesh4 }; tall.Add(t); tall.Add(t1); tall.Add(t2); tall.Add(t3); tall.Add(t4); now i am use equivalent statement of IIF var k = (from g in tall select new test1 { IsId = (g.id > 1) ? true : false  name = g.name } ); hope this help.

Jignesh Trivedi · Answer

Hi IIF is only support in SQL 2012. please go through.. http://www.mssqltips.com/sqlservertip/2570/new-logical-functions-in-sql-server-2012-iif-and-choose/

David Smith · Answer

thanks this actually works how can i tranfer this to linq in c# below SELECT CountOfColorValue ColorTable* case when ([CountOfColorValue] FROM INNER JOIN ColorTable ON (ColorLocation=ColorLocation) AND (ColorPart=ColorPart) AND (ColorCode=ColorCode)

David Smith · Answer

I am getting an error in management studio said incorrect syntax near  CountOfColorValue ColorTable* IIf([CountOfColorValue] ) AS COLOR STATUS I dont think it like the less than sign

David Smith · Answer

I was doing this earlier I need to check my syntax to see what i am doing wrong. Let me look carefully also what is is the two set below mean SET NOCOUNT ON SET XACT_ABORT ON Can you also show me how to transef that exact syntax into linq that way I dont have to go back and hit the database went I get to that point I can do it all all in memory using linq to befor setting the datasource

Senthilkumar · Answer

Hi Darnell There is no table in the from clause. I have assumed and written the stored procedure. CREATE PROCEDURE Proc_RetrieveColors AS BEGIN SET NOCOUNT ON SET XACT_ABORT ON SELECT CountOfColorValue ColorTable* IIf([CountOfColorValue] FROM MainTable MT INNER JOIN ColorTable CT ON CT.ColorLocation = MT.ColorLocation AND CT.ColorPart = MT.ColorPart AND CT.ColorCode = MT.ColorCode END I have given the MainTable in From clause.You need to change the correct table and use the appropriate table object in the select column list. Hope this will help you. If you need any thing then post me. If it is helpful then mark it as accept answer.