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.

Transfer SQL to SQL SERVER stored procedure

Insert Link

Embed YouTube Video

Table Options

Insert Image

Answers (9)