sql server if then else example

server| Sample | data -- =====================================================--application example of direct query-- ===================================================== --1. FullName for each leaf node with STB ([Id],[fullname],[pid],[flag]) as ( SELECT [Id],cast (RTRIM ([name]) as nvarchar (4000)), [pid],1 from [TB] A WHERE not EXISTS ( SELECT 1 from [TB] WHERE [Pid]=a.[id]) UNION all

Stored Procedure format:CREATE PROCEDURE yourprocedure asbegin SET NOCOUNT on; BEGIN TRY---------------------start catching exception BEIN TRAN------------------start transaction UPDATE A SET a.names= B.names from T1 as A INNER joins T2 as B on a.id =b.id UPDATE A SET a.names= B.names from T1 as A INNER joins T2 as B on a.test =b.test COMMIT TRAN-------COMMIT TRANSACTION END TRY-----------End catch Exception BEGIN catch------------An exception was arrested IF @ @TRANCOUNT>0---------------judge I

1. To confirm that no other person is connected to the current database, use sp_who to view the connection, and then use kill @ spid to forcibly close the connection. 2. Run the SQL statement to modify the database's collate attribute. Code highlighting produced by Actipro CodeHighlighter (freeware)http://www.CodeHighlighter.com/--> Use [ Master ] Go Alter Database [ My_db ] Collate finnish_swedish_cs_as Go

@ Ftech Is Not Null Begin If @ Sqlstr Is Not Null Set @ Sqlstr = @ sqlstr + 'And ftech =' + '' '' + @ Ftech + '' '' Else Set @ Sqlstr = 'Where ftech =' + '' '' + @ Ftech + '' '' End If @ Fsubject Is Not Null Begin If @ Sqlstr Is Not Null Set @ Sqlstr = @ sqlstr + 'And fsubject =' +'' '' + @ Fsubject + '' '' Else Set @ Sqlstr = 'Where fsubject =' + '' '' + @ Fsubject + '' '' End If @ Fjif Is Not Null Begin If @ Sqlstr Is Not

★First of all, I would like to thank the Netizens [from the SQL server technology space group] for their simple support... Note the difference between null and ''. Select * from table where fdate> comparison between '2007-6-7' or between time .......... ------------ Table field --------------- Create Table [DBO]. [stuinfo] ( [Fnumber] [int] identity (1, 1) not null, [Fname] [nvarchar] (30) Collate chinese_p

)5. Search for redundant duplicate records (multiple fields) in the table, excluding records with the smallest rowidSelect * from vitaeWhere (a. peopleId, a. seq) in (select peopleId, seq from vitae group by peopleId, seq having count (*)> 1)And rowid not in (select min (rowid) from vitae group by peopleId, seq having count (*)> 1)Method 3: when registering a user, you can remotely query the existence of the user name using AJAX. If the user name exists, no form is submitted, which greatly impro

first, describe the steps to create a FULL-TEXT index using system stored procedures: 1To start the Full-text processing of the database (sp_fulltext_database)2) to establish a Full-text catalog (sp_fulltext_catalog)3Registers the table that requires Full-text indexing in the Full-text catalog (sp_fulltext_table)4Indicates the column name in the table that requires a Full-text index (sp_fulltext_column)5To create a Full-text index for the table (sp_fulltext_table)6) Populate Full-text catalogs (

Small, with parametersif (exists (SELECT * from sys.objects where name = ' proc_get_purchasing2 '))drop proc Proc_get_studentGoALTER proc PROC_GET_PURCHASING2 (@today int)As--declare @today intDeclaration variablesdeclare @weeks nvarchar (3)Set Variable value set @weeksSet @weeks =case when @today = 1 Then ' Monday ' When @today = 2 Then ' Tuesday 'Else ' ERROR 'EndPrint @weeksEXEC proc_get_purchasing2 @today =2With loopsif (exists (SELECT * from sys.objects where name = ' Proc_get_purchasing3 '

() Turn capital Lower () Turn lowercase Vt-sql1. Declaring variable syntax: DECLARE @ variable name data typeAssigning values to variables SET @ variable name = value SELECT @ Variable name = value Output variable SELECT @ variable namePS: If you want to output variables, declare variables, variable assignments, and variable output three statements need to be executed together2. Programming statements Begin...end If...else Back to Topv View1. Create

. Insert:Connection conn = CreateConnection (); String sql = "INSERT into TMap (mapserviceid,mapid,mapname) VALUES (?,?,?)"; PreparedStatement pstmt=conn.preparestatement (SQL);p stmt.setstring (1, "1");p stmt.setstring (2, "7"); Pstmt.setstring (3, "Test1");p stmt.executeupdate ();p stmt.close (); Conn.close ();2. Enquiry String sql = "SELE

Isolation levels in SQL Server control the locking works between transactions.SQL Server supports the following isolation levels Read UNCOMMITTED Read Committed (the default) Repeatable Read Serializable Snapshot Before I run through each of the these in detail if want to create a new database to run the examples, run the followingSc

shown in the following example: Copy Code code as follows: DECLARE Custcursor CURSOR For SELECT * from Customers WHERE Cust_email is NULL Using cursors Use the Open CURSOR statement to open the cursor and use the FETCH statement to access the cursor data. The fetch indicates which rows to retrieve, where to retrieve them, and where to place them (such as variable names), and here is an

Please check that the DBTYPE node database type is correct in dnt.config, for example, SQL Server, Access, MYSQL Note: An unhandled exception occurred during the execution of the current Web request. Check the stack trace for more information about the error and where the error occurred in the code. Exception Details: System.Exception: Check that the DBTYPE node

Tags: dex http insert line ble lis search SPL imaThe recent online system suddenly appears to have more than 10 notes, looking at the situation over time, after a few searches. Discover the cause of the problem, through the end and the end of the two sides of the same time, the efficiency of the inquiry to raise more than a few hundred times First of all, the original application of the business logically edit for each page number is fixed to 10 pen, so users can exchange up to 10 of the informa

server| Sample | data Example of tree data processing in SQL Server 2005--Create test dataif exists (SELECT * from dbo.sysobjects WHERE id = object_id (n ' [TB] ') and OBJECTPROPERTY (ID, n ' isusertable ') = 1)drop table [TB]Go --Sample dataCREATE TABLE [TB] ([id] int PRIMARY key,[pid] int,name nvarchar (20))INSERT [T

This article illustrates the function and usage of SQL Server to implement Split function split string. Share to everyone for your reference, specific as follows: /* function Name: F_splittonvarchar function : To realize the function of Split function update record: design idea: A string that combines nvarchar type characters, Separate into a list of nvarchar types/ CREATE FUNCTION [dbo].[ F_splittonva

-expression 2)A string that intercepts an integer expression 2 length from an integer expression in a string of 2 bitsExample: Select substring (' Tracyleebaihe ', 3,2)--return:ac12, lower (string expression) and upper (string expression)The former converts the uppercase letters in the string to lowercase, the latter vice versa. If the string does not containLetter, blame return to original stringExample: Select lower (' Pinball tttt Pinball ')--return: Bomb TTTT Pinball HallExample: Select Uppe

Try this tool to configure yourself today. First, a database is established in the database, named TestDB, and a new SQL Server login name (TESTDB) is created and the default database TestDB TestDB is specified. And then it starts to be configured. The configuration process is referred to theaspnet_regsql.exe tool registration database , but when you select the server