sql server scalar function example

Back in the evening, the classmate said the interview encountered a SQL interview topic, did a bit of their own, summary summary.The topics are as follows:　　The following is a product data sheet (Product ID, color col, num), each of which is available in one or two colors.　　Ask for the difference in the number of colors for each product (for the total number of reserved products with only one color)　　　　　　　　The solution is as follows:1. Find out the n

Label:SQL Server Operations XML example /*Getting Started with SQL xml:--by jinjazz--http://blog.csdn.net/jinjazz 1, xml: Can recognize elements, attributes, and Values 2, XPath: Addressing language, similar to the Search for Windows directories (wall) syntax format, which can be combined as a condition: "." Express Yourself, "..." Represents the father, "/" for

' √ ' Else "' End) allow null, IsNull (E.text,"') default value, IsNull (g.value,"') asDescription fromSyscolumns A Left JoinSystypes b onA.xtype = B.xusertypeInner Joinsysobjects D ona.ID = D.id andD.xtype =' U ' andD.name ' dtproperties ' Left Joinsyscomments E onA.cdefault = e.id Left JoinSys.extended_properties g ona.ID = g.major_id andA.colid = g.minor_id Left JoinSys.extended_properties F onD.id = F.class andf.minor_id =0 whereB.name is not NUL

(for the current bit being parsed), the bit in the result is set to 1; if neither of the two bits in the input expression is 1, the bits in the result will be set to 0. Example: select 170 | 75 1010 1010 0100 1011 -------------------1110 1011 (3) "logical bit exclusive or ". If the values of the corresponding two bits are both 0 or 1, the value of this bits in the result is cleared to 0; otherwise (only one of the two corresponding bits is 1), and

An example of full-text index of the SQL Server database, using the pubs database as an example.The following describes how to create a full-text index using the system stored procedure: 1 (Sp_fulltext_database) 2 ) Create a full-text directory (sp_fulltext_catalog) 3 (Sp_fulltext_table) 4 (Sp_fulltext_column) 5 ) Create a full-text index for the table

Create a job and execute the following command:Exec master .. xp_mongoshell 'HTTP: // srm.rapoo.cn? Op = sapintferace I = 1 t = 1'Activate and execute synchronization stepsThe following describes how to enable xp_mongoshell to extend the Stored Procedure commands.I. IntroductionThe xp_cmdshell extended stored procedure uses the command string as the operating system command shell for execution and returns all the output in the form of text lines.Iii. xp_cmdshell in

Import SQL server data into Mysql database using php (example) // Connect to the SQL server database $ Conn = mssql_connect ("localhost", "sa", ""); // server name, user name, and password

SQL Server database version Lt; % @ Page Language = quot; C # quot; AutoEventWireup = quot; true quot; % gt; lt; script runat = quot; server quot; gt; public int TrapezoidIndex = 1; int LastNumer = 0; protected void Page_Load (object sender, EventArgs e) {string ConnectionString = quot; persist Security Info = False; User ID = sa; Password =; Initi

Stored procedure (procedure. SQL ):Create procedure hello@ Yourname varchar (50) = "no name"AsSelect @ yourname + ", Hello! "GoPHP3 program (procedure. php3 ):$ Server = mssql_connect ("localhost", "sa", "") or die ("unable to connect to the database server! ");$ Db = mssql_select_db ("test", $

start, SQL Server also supports the use of the ORDER BY clause in the window function (note: The book says that MSSQLServer is not supported by the use of an ORDER BY clause in the window function). Anyway, I've integrated the online content. Because the window function sup

, status 0, procedure TT1, 17th rowsThe insert statement conflicts with the check constraint "T1. This conflict occurs in the Database "test", table "DBO. Salary", column 'salary '.The statement has been terminated. (One row is affected) No output in the result 111 indicates that the stored procedure is not executed. Test environment: SQL Server 2005 Express This is another

Usage Scenario: When you need to access data from another SQL Server database from an Oracle database, Oracle provides a tool: gateways. With this tool, you can create dblink to connect to SQL Server or a different company's database----depending on the options you install. After you install gateways, you can create d

A few days ago, my colleague asked me how to convert a field value to a column. I wrote a simple Demo to share it. The Code is as follows: -- create a test table and add Test Data createtable # temp (amoney, bvarchar (10) * insertinto # temp (a, B) values () insert A few days ago, a colleague asked me how to convert a field value to a column. I wrote a simple Demo to share it. The Code is as follows: -- create a test table and add Test Data create table # temp (a money, B varchar (10)/* insert i

Label: ------------------date is converted into a month and a day and seconds--------------- Select ' R ' +convert (varchar), GETDATE (), 112)+right (CAST (Power (10,2) as varchar) +datepart (hour, GETDATE ()), 2)+right (CAST (Power (10,2) as varchar) +datepart (minute, GETDATE ()), 2)+right (CAST (Power (10,2) as varchar) +datepart (SS, GETDATE ()), 2)+right (CAST (Power (10,3) as varchar) +datepart (MS, GETDATE ()), 3) SQL

Algorithm is an important research direction in computer science, and it is the key to solve complex problems. In the computer world, algorithms are everywhere. The database is the place that stores the data and executes the large quantity computation, in the database uses some simple SQL commands, carries on the storage, the inquiry, the statistics, solves the real world problem already is not uncommon. With the increase of data volume and the increa

DateTime-related functions are often used in the T-SQL, and common functions are summarized to be forgotten.We often use various date formats for some purpose. Of course we can use string operations to construct various date formats, but why are there any ready-made functions?The default datetime format of the Chinese version of SQL Server is yyyy-mm-dd Thh: mm:

number of rows scanned using this method will be 1+2+3+n, and you will scan 50005000 rows for a 100000-row table as a whole. Incidentally, the formula for calculating the first N positive integers is (n+n squared)/2. --see what the example means. Use [AdventureWorks] SET STATISTICS time on SELECT Salesorderid,row_number () over (SalesOrderID) as RowNum From sales. [SalesOrderHeader] Select SalesOrderID, (select COUNT (*) from sales.[ SalesOrd

, because it will delete the things that others have added.(Assume that a single user can delete a product with comments, but not just comments)Then, after modifying the document, the operator implies several changes:1. Added some products2. modified some items3. Some items have been deleted. One direct SQL implementation: -- Update if it exists. If it does not exist, insert it.If exists (select 1 from morder with (nolock) Where morderid = @ ID and PK

Label:Create functionTest@Num varchar( -))--@Num Parametersreturns varchar( -)--return value type asbeginDeclare @MSG varchar( -)if(@Num =1) Select @MSG ='correct'Else Select @MSG ='Error'return @MSGEnd--Call functionSelect Dbo.test (2)--Pass parameter 2Return Result: Error--Create a function that returns a table typeCreate function gettabledetails () returns TABLE as return (Select* from sales)-

# include "iostream. H " # include" stdio. H " # import" C:/program files/common files/system/ADO/msado15.dll "no_namespace Rename (" EOF "," adoeof ") int main (INT argc, char * argv []) { _ connectionptr m_pconnection; m_pconnection.createinstance ("ADODB. connection "); try { // M_pconnection-> open ("Server = 192.168.0.150, 1433; uid = sa; Pwd = 123456; database = temp; provider = sqloledb ;","","", -1); // connect to