Search Results

Search found 6988 results on 280 pages for 'statement'.

Page 1/280 | 1 2 3 4 5 6 7 8 9 10 11 12  | Next Page >

  • Performance of if statement versus switch statement

    - by behrk2
    Hi Everyone, I have an if statement with 16 cases (I am checking the state of four boolean variables). Would there be any value in trying to implement this differently, with nested switch statements perhaps? What is the actual performance gain of a switch statement over an if statement? Thanks!

    Read the article

  • Making a Statement: How to retrieve the T-SQL statement that caused an event

    - by extended_events
    If you’ve done any troubleshooting of T-SQL, you know that sooner or later, probably sooner, you’re going to want to take a look at the actual statements you’re dealing with. In extended events we offer an action (See the BOL topic that covers Extended Events Objects for a description of actions) named sql_text that seems like it is just the ticket. Well…not always – sounds like a good reason for a blog post. When is a statement not THE statement? The sql_text action returns the same information that is returned from DBCC INPUTBUFFER, which may or may not be what you want. For example, if you execute a stored procedure, the sql_text action will return something along the lines of “EXEC sp_notwhatiwanted” assuming that is the statement you sent from the client. Often times folks would like something more specific, like the actual statements that are being run from within the stored procedure or batch. Enter the stack Extended events offers another action, this one with the descriptive name of tsql_stack, that includes the sql_handle and offset information about the statements being run when an event occurs. With the sql_handle and offset values you can retrieve the specific statement you seek using the DMV dm_exec_sql_statement. The BOL topic for dm_exec_sql_statement provides an example for how to extract this information, so I’ll cover the gymnastics required to get the sql_handle and offset values out of the tsql_stack data collected by the action. I’m the first to admit that this isn’t pretty, but this is what we have in SQL Server 2008 and 2008 R2. We will be making it easier to get statement level information in the next major release of SQL Server. The sample code For this example I have a stored procedure that includes multiple statements and I have a need to differentiate between those two statements in my tracing. I’m going to track two events: module_end tracks the completion of the stored procedure execution and sp_statement_completed tracks the execution of each statement within a stored procedure. I’m adding the tsql_stack events (since that’s the topic of this post) and the sql_text action for comparison sake. (If you have questions about creating event sessions, check out Pedro’s post Introduction to Extended Events.) USE AdventureWorks2008GO -- Test SPCREATE PROCEDURE sp_multiple_statementsASSELECT 'This is the first statement'SELECT 'this is the second statement'GO -- Create a session to look at the spCREATE EVENT SESSION track_sprocs ON SERVERADD EVENT sqlserver.module_end (ACTION (sqlserver.tsql_stack, sqlserver.sql_text)),ADD EVENT sqlserver.sp_statement_completed (ACTION (sqlserver.tsql_stack, sqlserver.sql_text))ADD TARGET package0.ring_bufferWITH (MAX_DISPATCH_LATENCY = 1 SECONDS)GO -- Start the sessionALTER EVENT SESSION track_sprocs ON SERVERSTATE = STARTGO -- Run the test procedureEXEC sp_multiple_statementsGO -- Stop collection of events but maintain ring bufferALTER EVENT SESSION track_sprocs ON SERVERDROP EVENT sqlserver.module_end,DROP EVENT sqlserver.sp_statement_completedGO Aside: Altering the session to drop the events is a neat little trick that allows me to stop collection of events while keeping in-memory targets such as the ring buffer available for use. If you stop the session the in-memory target data is lost. Now that we’ve collected some events related to running the stored procedure, we need to do some processing of the data. I’m going to do this in multiple steps using temporary tables so you can see what’s going on; kind of like having to “show your work” on a math test. The first step is to just cast the target data into XML so I can work with it. After that you can pull out the interesting columns, for our purposes I’m going to limit the output to just the event name, object name, stack and sql text. You can see that I’ve don a second CAST, this time of the tsql_stack column, so that I can further process this data. -- Store the XML data to a temp tableSELECT CAST( t.target_data AS XML) xml_dataINTO #xml_event_dataFROM sys.dm_xe_sessions s INNER JOIN sys.dm_xe_session_targets t    ON s.address = t.event_session_addressWHERE s.name = 'track_sprocs' SELECT * FROM #xml_event_data -- Parse the column data out of the XML blockSELECT    event_xml.value('(./@name)', 'varchar(100)') as [event_name],    event_xml.value('(./data[@name="object_name"]/value)[1]', 'varchar(255)') as [object_name],    CAST(event_xml.value('(./action[@name="tsql_stack"]/value)[1]','varchar(MAX)') as XML) as [stack_xml],    event_xml.value('(./action[@name="sql_text"]/value)[1]', 'varchar(max)') as [sql_text]INTO #event_dataFROM #xml_event_data    CROSS APPLY xml_data.nodes('//event') n (event_xml) SELECT * FROM #event_data event_name object_name stack_xml sql_text sp_statement_completed NULL <frame level="1" handle="0x03000500D0057C1403B79600669D00000100000000000000" line="4" offsetStart="94" offsetEnd="172" /><frame level="2" handle="0x01000500CF3F0331B05EC084000000000000000000000000" line="1" offsetStart="0" offsetEnd="-1" /> EXEC sp_multiple_statements sp_statement_completed NULL <frame level="1" handle="0x03000500D0057C1403B79600669D00000100000000000000" line="6" offsetStart="174" offsetEnd="-1" /><frame level="2" handle="0x01000500CF3F0331B05EC084000000000000000000000000" line="1" offsetStart="0" offsetEnd="-1" /> EXEC sp_multiple_statements module_end sp_multiple_statements <frame level="1" handle="0x03000500D0057C1403B79600669D00000100000000000000" line="0" offsetStart="0" offsetEnd="0" /><frame level="2" handle="0x01000500CF3F0331B05EC084000000000000000000000000" line="1" offsetStart="0" offsetEnd="-1" /> EXEC sp_multiple_statements After parsing the columns it’s easier to see what is recorded. You can see that I got back two sp_statement_completed events, which makes sense given the test procedure I’m running, and I got back a single module_end for the entire statement. As described, the sql_text isn’t telling me what I really want to know for the first two events so a little extra effort is required. -- Parse the tsql stack information into columnsSELECT    event_name,    object_name,    frame_xml.value('(./@level)', 'int') as [frame_level],    frame_xml.value('(./@handle)', 'varchar(MAX)') as [sql_handle],    frame_xml.value('(./@offsetStart)', 'int') as [offset_start],    frame_xml.value('(./@offsetEnd)', 'int') as [offset_end]INTO #stack_data    FROM #event_data        CROSS APPLY    stack_xml.nodes('//frame') n (frame_xml)    SELECT * from #stack_data event_name object_name frame_level sql_handle offset_start offset_end sp_statement_completed NULL 1 0x03000500D0057C1403B79600669D00000100000000000000 94 172 sp_statement_completed NULL 2 0x01000500CF3F0331B05EC084000000000000000000000000 0 -1 sp_statement_completed NULL 1 0x03000500D0057C1403B79600669D00000100000000000000 174 -1 sp_statement_completed NULL 2 0x01000500CF3F0331B05EC084000000000000000000000000 0 -1 module_end sp_multiple_statements 1 0x03000500D0057C1403B79600669D00000100000000000000 0 0 module_end sp_multiple_statements 2 0x01000500CF3F0331B05EC084000000000000000000000000 0 -1 Parsing out the stack information doubles the fun and I get two rows for each event. If you examine the stack from the previous table, you can see that each stack has two frames and my query is parsing each event into frames, so this is expected. There is nothing magic about the two frames, that’s just how many I get for this example, it could be fewer or more depending on your statements. The key point here is that I now have a sql_handle and the offset values for those handles, so I can use dm_exec_sql_statement to get the actual statement. Just a reminder, this DMV can only return what is in the cache – if you have old data it’s possible your statements have been ejected from the cache. “Old” is a relative term when talking about caches and can be impacted by server load and how often your statement is actually used. As with most things in life, your mileage may vary. SELECT    qs.*,     SUBSTRING(st.text, (qs.offset_start/2)+1,         ((CASE qs.offset_end          WHEN -1 THEN DATALENGTH(st.text)         ELSE qs.offset_end         END - qs.offset_start)/2) + 1) AS statement_textFROM #stack_data AS qsCROSS APPLY sys.dm_exec_sql_text(CONVERT(varbinary(max),sql_handle,1)) AS st event_name object_name frame_level sql_handle offset_start offset_end statement_text sp_statement_completed NULL 1 0x03000500D0057C1403B79600669D00000100000000000000 94 172 SELECT 'This is the first statement' sp_statement_completed NULL 1 0x03000500D0057C1403B79600669D00000100000000000000 174 -1 SELECT 'this is the second statement' module_end sp_multiple_statements 1 0x03000500D0057C1403B79600669D00000100000000000000 0 0 C Now that looks more like what we were after, the statement_text field is showing the actual statement being run when the sp_statement_completed event occurs. You’ll notice that it’s back down to one row per event, what happened to frame 2? The short answer is, “I don’t know.” In SQL Server 2008 nothing is returned from dm_exec_sql_statement for the second frame and I believe this to be a bug; this behavior has changed in the next major release and I see the actual statement run from the client in frame 2. (In other words I see the same statement that is returned by the sql_text action  or DBCC INPUTBUFFER) There is also something odd going on with frame 1 returned from the module_end event; you can see that the offset values are both 0 and only the first letter of the statement is returned. It seems like the offset_end should actually be –1 in this case and I’m not sure why it’s not returning this correctly. This behavior is being investigated and will hopefully be corrected in the next major version. You can workaround this final oddity by ignoring the offsets and just returning the entire cached statement. SELECT    event_name,    sql_handle,    ts.textFROM #stack_data    CROSS APPLY sys.dm_exec_sql_text(CONVERT(varbinary(max),sql_handle,1)) as ts event_name sql_handle text sp_statement_completed 0x0300070025999F11776BAF006F9D00000100000000000000 CREATE PROCEDURE sp_multiple_statements AS SELECT 'This is the first statement' SELECT 'this is the second statement' sp_statement_completed 0x0300070025999F11776BAF006F9D00000100000000000000 CREATE PROCEDURE sp_multiple_statements AS SELECT 'This is the first statement' SELECT 'this is the second statement' module_end 0x0300070025999F11776BAF006F9D00000100000000000000 CREATE PROCEDURE sp_multiple_statements AS SELECT 'This is the first statement' SELECT 'this is the second statement' Obviously this gives more than you want for the sp_statement_completed events, but it’s the right information for module_end. I leave it to you to determine when this information is needed and use the workaround when appropriate. Aside: You might think it’s odd that I’m showing apparent bugs with my samples, but you’re going to see this behavior if you use this method, so you need to know about it.I’m all about transparency. Happy Eventing- Mike Share this post: email it! | bookmark it! | digg it! | reddit! | kick it! | live it!

    Read the article

  • Short circuiting statement evaluation -- is this guaranteed? [C#]

    - by larryq
    Hi everyone, Quick question here about short-circuiting statements in C#. With an if statement like this: if (MyObject.MyArray.Count == 0 || MyObject.MyArray[0].SomeValue == 0) { //.... } Is it guaranteed that evaluation will stop after the "MyArray.Count" portion, provided that portion is true? Otherwise I'll get a null exception in the second part.

    Read the article

  • Prepared statement help, Number of variables doesn't match number of parameters in prepared statement

    - by Sam Gabriel
    I'm getting this error : Number of variables doesn't match number of parameters in prepared statement every time I run this code: $dbh = new mysqli("localhost", "***", "***", "pics"); $stmt = $dbh->prepare("INSERT INTO comments (username, picture, comment) VALUES (?, ?, ?)"); $stmt->bind_Param('s', $username); $stmt->bind_Param('d', $picture); $stmt->bind_Param('s', $comment); $username=$_SESSION['username']; $picture=$_GET['id']; $comment=$_POST['comment']; $stmt->execute(); What's the problem?

    Read the article

  • Using a Case statement within the values section of an Insert statement

    - by mattgcon
    Please forgive my ignorance and poor SQL programming skills but I am normally a basic SQL developer. I need to create a trigger off the insertion of data in one table to insert different data into another table. Within this trigger I need to insert certain data into the new table based upon values within the newly inserted data from the original table. I am totally confused on this. i thought I would be creative and use a case statement within teh Values section but it is not working. Can anyone please help me on this? (below is the code for the trigger that I have as of now) INSERT INTO dbo.WebOnlineUserPeopleDashboard ( ONLINE_USERACCOUNT_ID, ONLINE_ROOMS_DIRECTORY, ONLINE_ROOMS_LIST, ONLINE_ROOMS_PLACEMENT, ONLINE_ROOMS_MANAGEMENT, ONLINE_MAILINGLIST_DIRECTORY, ONLINE_MAILINGLIST_LIST, ONLINE_MAILINGLIST_MEMBERS, ONLINE_MAILINGLIST_MANAGER, ONLINE_PEOPLESEARCH_DIRECTORY ) VALUES IF (SELECT ONLINE_PEOPLE_FULL_ACCESS FROM INSERTED) = 1 BEGIN SELECT ONLINE_USERACCOUNT_ID, 1, 1, 1, 1, 1, 1, 1, 1, 1 FROM INSERTED END ELSE IF (SELECT ONLINE_PEOPLE_FULL_ACCESS FROM INSERTED) = 0 BEGIN SELECT ONLINE_USERACCOUNT_ID, 0, 0, 0, 0, 0, 0, 0, 0, 0 FROM INSERTED END ELSE BEGIN SELECT ONLINE_USERACCOUNT_ID, CASE --DIRECTORY WHEN ONLINE_PEOPLE_ROOMS_PLACEMENT_FULL_ACCESS = 1 OR ONLINE_PEOPLE_ROOMS_PLACEMENT_VIEW = 1 OR ONLINE_PEOPLE_ROOMS_PLACEMENT_ADD = 1 OR ONLINE_PEOPLE_ROOMS_PLACEMENT_UPDATE = 1 OR ONLINE_PEOPLE_ROOMS_PLACEMENT_DELETE = 1 THEN 1 WHEN ONLINE_PEOPLE_ROOMS_PLACEMENT_FULL_ACCESS = 0 THEN 0 END, CASE WHEN ONLINE_PEOPLE_ROOMS_PLACEMENT_VIEW = 1 THEN 1 WHEN ONLINE_PEOPLE_ROOMS_PLACEMENT_VIEW = 0 THEN 0 END, CASE WHEN ONLINE_PEOPLE_ROOMS_PLACEMENT_ADD = 1 OR ONLINE_PEOPLE_ROOMS_PLACEMENT_UPDATE = 1 OR ONLINE_PEOPLE_ROOMS_PLACEMENT_DELETE = 1 THEN 1 WHEN ONLINE_PEOPLE_ROOMS_PLACEMENT_ADD = 0 AND ONLINE_PEOPLE_ROOMS_PLACEMENT_UPDATE = 0 AND ONLINE_PEOPLE_ROOMS_PLACEMENT_DELETE = 0 THEN 0 END, CASE WHEN ONLINE_PEOPLE_ROOMS_MANAGEMENT_FULL_ACCESS = 1 THEN 1 WHEN ONLINE_PEOPLE_ROOMS_MANAGEMENT_FULL_ACCESS = 0 THEN 0 END, CASE WHEN ONLINE_PEOPLE_MAILING_LISTS_FULL_ACCESS = 1 OR ONLINE_PEOPLE_MAILING_LISTS_VIEW = 1 OR ONLINE_PEOPLE_MAILING_LISTS_MEMBERS_ADD = 1 OR ONLINE_PEOPLE_MAILING_LISTS_MEMBERS_UPDATE = 1 OR ONLINE_PEOPLE_MAILING_LISTS_MEMBERS_DELETE = 1 THEN 1 WHEN ONLINE_PEOPLE_MAILING_LISTS_FULL_ACCESS = 0 THEN 0 END, CASE WHEN ONLINE_PEOPLE_MAILING_LISTS_VIEW = 1 THEN 1 WHEN ONLINE_PEOPLE_MAILING_LISTS_VIEW = 0 THEN 0 END, CASE WHEN ONLINE_PEOPLE_MAILING_LISTS_MEMBERS_ADD = 1 OR ONLINE_PEOPLE_MAILING_LISTS_MEMBERS_UPDATE = 1 OR ONLINE_PEOPLE_MAILING_LISTS_MEMBERS_DELETE = 1 THEN 1 WHEN ONLINE_PEOPLE_MAILING_LISTS_MEMBERS_ADD = 0 AND ONLINE_PEOPLE_MAILING_LISTS_MEMBERS_UPDATE = 0 AND ONLINE_PEOPLE_MAILING_LISTS_MEMBERS_DELETE = 0 THEN 0 END, CASE WHEN ONLINE_PEOPLE_MAILING_LISTS_ADD = 1 OR ONLINE_PEOPLE_MAILING_LISTS_UPDATE = 1 OR ONLINE_PEOPLE_MAILING_LISTS_DELETE = 1 THEN 1 WHEN ONLINE_PEOPLE_MAILING_LISTS_ADD = 1 OR ONLINE_PEOPLE_MAILING_LISTS_UPDATE = 1 OR ONLINE_PEOPLE_MAILING_LISTS_DELETE = 1 THEN 0 END, CASE WHEN ONLINE_PEOPLE_PEOPLE_SEARCH = 1 THEN 1 WHEN ONLINE_PEOPLE_PEOPLE_SEARCH = 0 THEN 0 END FROM INSERTED END END

    Read the article

  • switch statement in for loop and if statement not completing

    - by user2373912
    I'm trying to find out how many of each character are in a string. I've searched around for a while and can't seem to figure out why my switch statement is stopping after the first case. function charFreq(string){ var splitUp = string.split(""); console.log(splitUp); var a; var b; var c; var v; for (var i = 0; i<splitUp.length; i++){ if (i<1){ switch (splitUp[i]){ case "a": a = 1; break; case "b": b = 1; break; case "c": c = 1; break; case "v": v = 1; break; } } else { switch (splitUp[i]){ case "a": a += 1; break; case "b": b += 1; break; case "c": c += 1; break; case "v": v += 1; break; } } } console.log("There are " + a + " A's, " + b + " B's, " + c + " C's, and " + v + " V's.") } charFreq("aaabccbbavabac"); What am I doing wrong that would make the console read: There are 6 A's, NaN B's, NaN C's, and NaN V's.

    Read the article

  • If statement within If statement's else

    - by maebe
    Still new to Java/android so trying to figure out the best way to code a multilevel if statement. What I'm trying to do is for a combat system that needs to check if player/npc is alive. If they are alive it then will check to see if they scored a critical hit. If they didn't critical hit then will see if they hit or missed. combat = mydbhelper.getCombat(); startManagingCursor(combat); if(playerCurHp == 0){ combat.moveToPosition(11); npcCombatStory = combat.getString(combat.getColumnIndex(dbhelper.KEY_COMBATDESC));} else{ if(playerCritFlag.equals("Critical")){ combat.moveToPosition(2); playerCombatStory = combat.getString(combat.getColumnIndex(dbhelper.KEY_COMBATDESC));} else{ if(playerHitFlag.equals("Hit")){ combat.moveToPosition(1); playerCombatStory = combat.getString(combat.getColumnIndex(dbhelper.KEY_COMBATDESC));} if(playerHitFlag.equals("Miss")){ combat.moveToPosition(3); playerCombatStory = combat.getString(combat.getColumnIndex(dbhelper.KEY_COMBATDESC));} }} if (npcCurHp == 0){ combat.moveToPosition(10); npcCombatStory = combat.getString(combat.getColumnIndex(dbhelper.KEY_COMBATDESC));} else{ if(npcCritFlag.equals("Critical")){ combat.moveToPosition(5); npcCombatStory = combat.getString(combat.getColumnIndex(dbhelper.KEY_COMBATDESC));} else{ if(npcHitFlag.equals("Hit")){ combat.moveToPosition(4); npcCombatStory = combat.getString(combat.getColumnIndex(dbhelper.KEY_COMBATDESC));} if(npcHitFlag.equals("Miss")){ combat.moveToPosition(6); npcCombatStory = combat.getString(combat.getColumnIndex(dbhelper.KEY_COMBATDESC));} }} } Is what I'm using. Was working when I had the if statements all separate. But it would check each one and do actions I don't need (If they hit, pull String, if crit pull another, then if dead pull again). Trying to make it stop when it finds the "Flag" that matches. When doing my rolls if the player hits it sets the flag to "Hit" like below code. Random attackRandom = new Random(); int attackRoll = attackRandom.nextInt(100); totalAtt = attackRoll + bonusAttack + weaponAtt + stanceAtt; Random defensiveRandom = new Random(); int defenseRoll = defensiveRandom.nextInt(100); npcDef = defenseRoll + npcDodge + npcBonusDodge; if (totalAtt > npcDef){ playerHitFlag = "Hit"; playerDamage();} else{playerHitFlag = "Miss"; npcAttack();} At the end it takes these playerCombatStory and npcCombatStory strings and uses them to setText to show the player what happened on that turn of combat.

    Read the article

  • What if(event) statement means in JavaScript?

    - by j flo
    I'm rather new to JavaScript and programming in general so I am pretty much only used to seeing if statements that have some kind of comparison operator like, if (x < 10) or if(myBool). I have seen an if statement checking against an event, but I don't understand what or why the event is being checked like that. What's the semantic meaning behind that check or comparison? Here is the code in question: if(event){ event.preventDefault(); }

    Read the article

  • If statement is ignored

    - by user2898120
    I am making a simple matchmaker as a learning project in JAVA. My program so far just asks a few questions, but I wanted to do gender specific questions, so I asked for their sex (m or f) and then attempted to add a message that only showed if sex was m. The dialog should say "well done, you are male!". Else it restarts method. Every time, no matter what I type it restarts the program. Here is my code: import javax.swing.JOptionPane; public class Main { public static void main(String[] args){ setVars(); } public static void setVars(){ String name = JOptionPane.showInputDialog(null, "What is your name?"); String sAge = JOptionPane.showInputDialog(null, "What is your age?"); String sex = JOptionPane.showInputDialog(null, "What is your sex?\n(Enter m or f)"); if (sex == "m"){ JOptionPane.showMessageDialog(null, "Well done, you are male.\nKeep Going!"); } int age = Integer.parseInt(sAge); String chars = JOptionPane.showInputDialog(null, "Name three charectaristics"); } }

    Read the article

  • Why is there never any controversy regarding the switch statement? [closed]

    - by Nick Rosencrantz
    We all know that the gotostatement should only be used on very rare occasions if at all. It has been discouraged to use the goto statement countless places countless times. But why it there never anything like that about the switch statement? I can understand the position that the switch statement should always be avoided since anything with switch can always be expressed by if...else... which is also more readable and the syntax of the switch statement if difficult to remember. Do you agree? What are the arguments in favor of keeping the 'switch` statement? It can also be difficult to use if what you're testing changes from say an integer to an object, then C++ or Java won't be able to perform the switch and neither C can perform switch on something like a struct or a union. And the technique of fall-through is so very rarely used that I wonder why it was never presented any regret of having switch at all? The only place I know where it is best practice is GUI code and even that switch is probably better coded in a more object-oriented way.

    Read the article

  • T-SQL (SCD) Slowly Changing Dimension Type 2 using a merge statement

    - by AtulThakor
    Working on stored procedure recently which loads records into a data warehouse I found that the existing record was being expired using an update statement followed by an insert to add the new active record. Playing around with the merge statement you can actually expire the current record and insert a new record within one clean statement. This is how the statement works, we do the normal merge statement to insert a record when there is no match, if we match the record we update the existing record by expiring it and deactivating. At the end of the merge statement we use the output statement to output the staging values for the update,  we wrap the whole merge statement within an insert statement and add new rows for the records which we inserted. I’ve added the full script at the bottom so you can paste it and play around.   1: INSERT INTO ExampleFactUpdate 2: (PolicyID, 3: Status) 4: SELECT -- these columns are returned from the output statement 5: PolicyID, 6: Status 7: FROM 8: ( 9: -- merge statement on unique id in this case Policy_ID 10: MERGE dbo.ExampleFactUpdate dp 11: USING dbo.ExampleStag s 12: ON dp.PolicyID = s.PolicyID 13: WHEN NOT MATCHED THEN -- when we cant match the record we insert a new record record and this is all that happens 14: INSERT (PolicyID,Status) 15: VALUES (s.PolicyID, s.Status) 16: WHEN MATCHED --if it already exists 17: AND ExpiryDate IS NULL -- and the Expiry Date is null 18: THEN 19: UPDATE 20: SET 21: dp.ExpiryDate = getdate(), --we set the expiry on the existing record 22: dp.Active = 0 -- and deactivate the existing record 23: OUTPUT $Action MergeAction, s.PolicyID, s.Status -- the output statement returns a merge action which can 24: ) MergeOutput -- be insert/update/delete, on our example where a record has been updated (or expired in our case 25: WHERE -- we'll filter using a where clause 26: MergeAction = 'Update'; -- here   Complete source for example 1: if OBJECT_ID('ExampleFactUpdate') > 0 2: drop table ExampleFactUpdate 3:  4: Create Table ExampleFactUpdate( 5: ID int identity(1,1), 3: go 6: PolicyID varchar(100), 7: Status varchar(100), 8: EffectiveDate datetime default getdate(), 9: ExpiryDate datetime, 10: Active bit default 1 11: ) 12:  13:  14: insert into ExampleFactUpdate( 15: PolicyID, 16: Status) 17: select 18: 1, 19: 'Live' 20:  21: /*Create Staging Table*/ 22: if OBJECT_ID('ExampleStag') > 0 23: drop table ExampleStag 24: go 25:  26: /*Create example fact table */ 27: Create Table ExampleStag( 28: PolicyID varchar(100), 29: Status varchar(100)) 30:  31: --add some data 32: insert into ExampleStag( 33: PolicyID, 34: Status) 35: select 36: 1, 37: 'Lapsed' 38: union all 39: select 40: 2, 41: 'Quote' 42:  43: select * 44: from ExampleFactUpdate 45:  46: select * 47: from ExampleStag 48:  49:  50: INSERT INTO ExampleFactUpdate 51: (PolicyID, 52: Status) 53: SELECT -- these columns are returned from the output statement 54: PolicyID, 55: Status 56: FROM 57: ( 58: -- merge statement on unique id in this case Policy_ID 59: MERGE dbo.ExampleFactUpdate dp 60: USING dbo.ExampleStag s 61: ON dp.PolicyID = s.PolicyID 62: WHEN NOT MATCHED THEN -- when we cant match the record we insert a new record record and this is all that happens 63: INSERT (PolicyID,Status) 64: VALUES (s.PolicyID, s.Status) 65: WHEN MATCHED --if it already exists 66: AND ExpiryDate IS NULL -- and the Expiry Date is null 67: THEN 68: UPDATE 69: SET 70: dp.ExpiryDate = getdate(), --we set the expiry on the existing record 71: dp.Active = 0 -- and deactivate the existing record 72: OUTPUT $Action MergeAction, s.PolicyID, s.Status -- the output statement returns a merge action which can 73: ) MergeOutput -- be insert/update/delete, on our example where a record has been updated (or expired in our case 74: WHERE -- we'll filter using a where clause 75: MergeAction = 'Update'; -- here 76:  77:  78: select * 79: from ExampleFactUpdate 80: 

    Read the article

  • Variable amount of columns returned in mysqli prepared statement

    - by manyxcxi
    I have a situation where a dynamic query is being generated that could select anywhere from 1 to over 300 different columns across multiple tables. It currently works fine just doing a query, however the issue I'm running into in using a prepared statement is that I do not know how to handle the fact that I don't know how many columns I will be asking for each time and therefor don't know how to process the results. The reason I believe a bind statement will help is because once this query is run once, it will most likely (though not always) be run again with the exact same parameters. Currently I have something like this: $rows = array(); $this->statement = $this->db->prepare($query); $this->statement->bind_param('i',$id); $this->statement->execute(); $this->statement->bind_result($result); while($this->statement->fetch()) { $rows[] = $result; } I know this doesn't work as I want it to, my question is how do I get the data back out of the query. Is it possible to bring the columns back in an associative array by column name, like a standard mysqli query?

    Read the article

  • CASE statement within WHERE statement

    - by niao
    Greetings, I would like to include CASE Statement inside my where statement as follows: SELECT a1.ROWGUID FROM Table1 a1 INNER JOIN Table2 a2 on a1.ROWGUID=a2.Table1ROWGUID WHERE a1.Title='title' AND (CASE WHEN @variable is not null THEN a1.ROWGUID in (SELECT * FROM #TempTable)) However, this 'CASE' statement does not work inside 'WHERE' statement. How can I do it correct?

    Read the article

  • Question about DBD::CSB Statement-Functions

    - by sid_com
    From the SQL::Statement::Functions documentation: Function syntax When using SQL::Statement/SQL::Parser directly to parse SQL, functions (either built-in or user-defined) may occur anywhere in a SQL statement that values, column names, table names, or predicates may occur. When using the modules through a DBD or in any other context in which the SQL is both parsed and executed, functions can occur in the same places except that they can not occur in the column selection clause of a SELECT statement that contains a FROM clause. # valid for both parsing and executing SELECT MyFunc(args); SELECT * FROM MyFunc(args); SELECT * FROM x WHERE MyFuncs(args); SELECT * FROM x WHERE y < MyFuncs(args); # valid only for parsing (won't work from a DBD) SELECT MyFunc(args) FROM x WHERE y; Reading this I would expect that the first SELECT-statement of my example shouldn't work and the second should but it is quite the contrary. #!/usr/bin/env perl use warnings; use strict; use 5.010; use DBI; open my $fh, '>', 'test.csv' or die $!; say $fh "id,name"; say $fh "1,Brown"; say $fh "2,Smith"; say $fh "7,Smith"; say $fh "8,Green"; close $fh; my $dbh = DBI->connect ( 'dbi:CSV:', undef, undef, { RaiseError => 1, f_ext => '.csv', }); my $table = 'test'; say "\nSELECT 1"; my $sth = $dbh->prepare ( "SELECT MAX( id ) FROM $table WHERE name LIKE 'Smith'" ); $sth->execute (); $sth->dump_results(); say "\nSELECT 2"; $sth = $dbh->prepare ( "SELECT * FROM $table WHERE id = MAX( id )" ); $sth->execute (); $sth->dump_results(); outputs: SELECT 1 '7' 1 rows SELECT 2 Unknown function 'MAX' at /usr/lib/perl5/site_perl/5.10.0/SQL/Parser.pm line 2893. DBD::CSV::db prepare failed: Unknown function 'MAX' at /usr/lib/perl5/site_perl/5.10.0/SQL/Parser.pm line 2894. [for Statement "SELECT * FROM test WHERE id = MAX( id )"] at ./so_3.pl line 30. DBD::CSV::db prepare failed: Unknown function 'MAX' at /usr/lib/perl5/site_perl/5.10.0/SQL/Parser.pm line 2894. [for Statement "SELECT * FROM test WHERE id = MAX( id )"] at ./so_3.pl line 30. Could someone explaine me this behavior?

    Read the article

  • Why does Clang/LLVM warn me about using default in a switch statement where all enumerated cases are covered?

    - by Thomas Catterall
    Consider the following enum and switch statement: typedef enum { MaskValueUno, MaskValueDos } testingMask; void myFunction(testingMask theMask) { switch theMask { case MaskValueUno: {}// deal with it case MaskValueDos: {}// deal with it default: {} //deal with an unexpected or uninitialized value } }; I'm an Objective-C programmer, but I've written this in pure C for a wider audience. Clang/LLVM 4.1 with -Weverything warns me at the default line: Default label in switch which covers all enumeration values Now, I can sort of see why this is there: in a perfect world, the only values entering in the argument theMask would be in the enum, so no default is necessary. But what if some hack comes along and throws an uninitialized int into my beautiful function? My function will be provided as a drop in library, and I have no control over what could go in there. Using default is a very neat way of handling this. Why do the LLVM gods deem this behaviour unworthy of their infernal device? Should I be preceding this by an if statement to check the argument?

    Read the article

  • How is a switch statement better than a series of if statements? [closed]

    - by user1276078
    Possible Duplicate: Should I use switch statements or long if…else chains? I'm working on a small program that will conduct an Insertion Sort. A number will be inputted through the keyboard and stored in a variable I called "num." I've decided to use a switch statement in order to obtain the number inputted. switch( e.getKeyCode() ) { case KeyEvent.VK_0: num = 0; break; case KeyEvent.VK_1: num = 1; break; case KeyEvent.VK_2: num = 2; break; case KeyEvent.VK_3: num = 3; break; case KeyEvent.VK_4: num = 4; break; case KeyEvent.VK_5: num = 5; break; case KeyEvent.VK_6: num = 6; break; case KeyEvent.VK_7: num = 7; break; case KeyEvent.VK_8: num = 8; break; case KeyEvent.VK_9: num = 9; break; } I realized one other course of action could have been to use a set of if statements. if( e.getKeyCode() == KeyEvent.VK_0 ) num = 0; else if( e.getKeyCode() == KeyEvent.VK_1 ) num = 1; etc. for every number up until 9. I then wondered what the essential difference is between a switch statement and a series of if statements. I know it saves space and time to write, but it's not that much. So, my question is, aside from the space, does a switch statement differ from a series of if statments in any way? Is it faster, less error-prone, etc.? This question really doesn't affect my code that much. I was just wondering. Also, this question pertains to the JAVA language, not any other programming language.

    Read the article

  • Search select statement

    - by Nana
    I am creating a page which would have different field for the user to search from. e.g. search by: Grade: -dropdownlist1- Student name: -dropdownlist2- Student ID: -dropdownlist3- Lessons: -dropdownlist4- Year: -dropdownlist5- How do I write the select statement for this? Each dropdownlist would need a select statement which would extract out different data from the database. But, I want to write ONE select statement which can dynamically choose the dropdownlist options. Instead of writing many many select statement. Lets say; Grade: -dropdownlist1- ; default value(all) Student name: -dropdownlist2-; default value(all) Student ID: -dropdownlist3-; 0-100 is choosen Lessons: -dropdownlist4-; A-C is choosen Year: -dropdownlist5-; 2009 is choosen

    Read the article

  • Am I right in thinking there is no way to put an if statement and an else statement on line in Python?

    - by Louise
    Am I right in thinking I can't put an if-statement and the corresponding else-statement on one line in Python? NB: variable = value1 if condition else value2 is NOT two statements. It's one statement which can take the value of one of two expressions. I want to do something like if condition a=value else b=value Am I right in thinking this requires a full if-else in Python? Like if condition: a=value else: b=value Thanks, Louise

    Read the article

  • How to combine a list of choices to determine which select statement

    - by Larry
    I have a mysql db and am using php 5.2 What I am trying to do is offer a list of options for a person to select (only 1). The chosen option will cause a select, update, or delete statement to be ran. The results of the statement do not need to be shown, although, showing the old and then the new would be nice (no problems with that part tho'.). Pseudo-Code: Assign $choice = 0 Check the value of $choice // This way, if it = 100, we do a break Select a Choice:<br> 1. Adjust Status Value (+60) // $choice = 1<br> 2. Show all Ships <br> // $choice = 2 3. Show Ships in Port <br> // $choice = 3 ... 0. $choice="100" // if the value =100, quit this part Use either case (switch) or if/else statements to run the users choice1 If the choice is 1, then run the "Select" statement with the variable of $sql1 -- "SELECT .... If the choice is 2, then run the "Select" statement with the variable of $sql2 --- SELECT * FROM Ships If the choice is 3, then run the "Select" statement with the variable of $sql3 <br> .... If the choice is 0, then we are done. I figured the (3) statements would be assigned in php as: $sql1="...". $sql2="SELECT * FROM Ships" $sql3="SELECT * FROM Ships WHERE nPort="1" My idea was to use the switch statement, but got lost on it. :( I would like the options to be available over and over again, until a variable ($choice) is selected. In which case, this particular page is done and goes back to the "Main Menu"? The coding and display, if I use it, I can do. Just not sure how to write the way to select which one I want. It is possible that I would run all of the queries, and other times, only one, so that is why I would like the choice. An area I get confused in is the proper forms to use such as -- ' ' " " and ...?? Not sure the # of options I will end up with, but it will be more than 5 but less than 20 / page. So if I get the system down for 2-3 choices, I can replicate it for as many as I may need. And, as always, if a better way exists, I am willing to try it. Thanks again... Larry

    Read the article

  • how to go back to first if statement if no choices are valid - python

    - by wondergoat77
    how can i have python move to the top of an if statement if nothing is satisfied correctly i have a basic if/else statement like this: print "pick a number, 1 or 2" a = int(raw_input("> ") if a == 1: print "this" if a == 2: print "that" else: print "you have made an invalid choice, try again." what i want is to prompt the user to make another choice for 'a' this if statement without them having to restart the entire program, but am very new to python and am having trouble finding the answer online anywhere.

    Read the article

  • Preparing a MySQL INSERT/UPDATE statement with DEFAULT values

    - by Raveren
    Quoting MySQL INSERT manual - same goes for UPDATE: Use the keyword DEFAULT to set a column explicitly to its default value. This makes it easier to write INSERT statements that assign values to all but a few columns, because it enables you to avoid writing an incomplete VALUES list that does not include a value for each column in the table. Otherwise, you would have to write out the list of column names corresponding to each value in the VALUES list. So in short if I write INSERT INTO table1 (column1,column2) values ('value1',DEFAULT); A new row with column2 set as its default value - whatever it may be - is inserted. However if I prepare and execute a statement in PHP: $statement = $pdoObject-> prepare("INSERT INTO table1 (column1,column2) values (?,?)"); $statement->execute(array('value1','DEFAULT')); The new row will contain 'DEFAULT' as its text value - if the column is able to store text values. Now I have written an abstraction layer to PDO (I needed it) and to get around this issue am considering to introduce a const DEFAULT_VALUE = "randomstring"; So I could execute statements like this: $statement->execute(array('value1',mysql::DEFAULT_VALUE)); And then in method that does the binding I'd go through values that are sent to be bound and if some are equal to self::DEFAULT_VALUE, act accordingly. I'm pretty sure there's a better way to do this. Has someone else encountered similar situations?

    Read the article

  • Better Alternative to Case Statement

    - by Kyle Rozendo
    Hi All, I currently have a switch statement that runs around 300 odd lines. I know this is not as giant as it can get, but I'm sure there's a better way to handle this. The switch statement takes an Enum that is used to determine certain properties that pertain to logging. Right now the problem sets in that it is very easy to leave out an enumeration value and that it will not be given a value as it is not in the switch statement. Is there an option one can use to ensure that every enumeration is used and given a custom set of values it needs to do its job? Thanks, Kyle

    Read the article

1 2 3 4 5 6 7 8 9 10 11 12  | Next Page >