please tell me examples about syntax "case" &"create function" in sql language?
144888
1. Create function\n===================\n\nCreates a user-defined function, which is a saved Transact-SQL routine that returns a value. User-defined functions cannot be used to perform a set of actions that modify the global database state. User-defined functions, like system functions, can be invoked from a query. They also can be executed through an EXECUTE statement like stored procedures.\n\nUser-defined functions are modified using ALTER FUNCTION, and dropped using DROP FUNCTION.\n\nSyntax\n------\n\nCREATE  FUNCTION [ owner_name. ] function_name \n    ( [ { @parameter_name [AS] scalar_parameter_data_type [ = default ] } [ ,...n ] ] ) \n\nRETURNS scalar_return_data_type\n\n[ WITH < function_option> [ [,] ...n] ] \n\n[ AS ]\n\nBEGIN \n    function_body \n    RETURN scalar_expression\nEND\n\n\n2. CASE function\n====================\n\nAs a SQL Server DBA, I realized that SQL Server has no function for Title Case, like the "initcap" function in Oracle. So I've made one that capitalizes the first letter of every word in a given text string: \n\ncreate function initcap (@text varchar(4000))\nreturns varchar(4000)\nas\n\nbegin\ndeclare @counter int, \n@length int,\n@char char(1),\n@textnew varchar(4000)\n\nset @text= rtrim(@text)\nset @text= lower(@text)\nset @length = len(@text)\nset @counter = 1\n\nset @text = upper(left(@text, 1) ) + right(@text, @length - 1) \n\nwhile @counter <> @length --+ 1\nbegin\nselect @char = substring(@text, @counter, 1)\n\nIF @char = space(1)  or @char =  '_' or @char = ','  or @char = '.' or @char = '&#92;'\n or @char = '/' or @char = '(' or @char = ')'\nbegin\nset @textnew = left(@text, @counter)  + upper(substring(@text, \n@counter+1, 1)) + right(@text, (@length - @counter) - 1)\nset @text = @textnew\nend\n\nset @counter = @counter + 1\nend\n\nreturn @text\nend\n\n*answer ends*
828970
dim db as database\ndim rs as recordset\ndim strSQL as string\ndim strA as string\ndim strB as string\ndim strC as string\n\nset db = currentDB\n\nstrSQL = "Select fieldA, fieldB, fieldC from Table1 where FieldD = fieldvalue"\n\nset rs = db.openrecordset(strSQL, dbOpenDynaSet)\n\nif not rs.EOF then\nstrA = rs!fieldA\nstrB = rs!fieldB\nstrC = rs!fieldC\n\nend if
29979
c)\n\nExplanation:\n\n\nThe API documentation for Connection lists the following three prepareCall-methods:\n\n- prepareCall(String sql)\n- prepareCall(String sql, int resultSetType, int resultSetConcurrency)\n- prepareCall(String sql, int resultSetType, int resultSetConcurrency, int resultSetHoldability)\n\nOnly the second one matches one of the answer options.
272795
Please clearify your question.  This doesn't make much sense.\n\nSounds like you'll need to use a SELECT..CASE or an IF..THEN.
61405
Convert the parameters in your "where" clause to the same case, upper or lower.\n\nselect * from tbl \nwhere upper(fielda) = upper(searchparameter)\n\nI've not worked with Access (.mdb) dbs much, but there may also be an Access configuration to disregard Case.  (At least that is an option in MS Sql Server.)
1062183
We're going to call your row "numbers" and your table "table" for this example.  The SQL code would look like this:\n\nSELECT SUM(numbers)\n   FROM table;\n\nThere you go.
650047
What does "hit all of the control form fields and report the information back to SQL" mean? \n\nDo you mean, you want a button that will run a given stored procedure on the server, using parameters?\n\nDo you mean, you want to obtain the value of the form fields and use them to build a SQL statement that you then execute on the server?\n\nAlso, are you using SQL server, MySQL, SAP, whatever as a SQL database?\n\nUPDATE:\n\nYou still haven't told me what kind of DB server you are using, so I'll assume is SQL Server.\n\nYou don't need to create a VBA Script to do what you want. You can simply link the relevant SQL tables in your Access project file, then work with them as though they were tables in the Access DB itself.\n\nIn other words, you can build a form based on a linked table, just like you would build a form for a regular Access table; it will insert data for you into the linked table, the same way it would for a regular table.\n\nSee link below for more information.
696438
I'm missing something from your question.\n\nYou can always test your query in query analyzer.\n\nYour query looks fine but my confusion is in your where clause - if xxx is coming from the textbox then it sounds like you might generate the following query:\n\nselect * from table where bob like '%@bob'.\n\nWhen you probably want the following:\nselect * from table where COLUMNNAME like '%bob'\n\nnote:  I removed the @ symbol from your sql statement.\n\nAlthough you're better off creating a stored procedure that accepts a parameter using the field value as the parameter.\n\nIf you have any questions feel free to send me an email.
1124819
Create a new field in the property table called Address:\n\nalter table property add Address char(50);\n\nThe above assumes the data type is char(50).\n\nUpdate that new field equal to street number and street name:\n\nupdate property set Address = concat (Street_Num, ' ', Street_Name);\n\nAssumes that Street_Num and Street_Name represents street number and street name, respectively.\n\nThis assumes MySQL syntax.  The exact concatenate syntax is database-dependent.
1100890
you should use an sql connection object from the component toolbar n make a connected architecture.. and then drag n drop an sql command object . in that object's property set type = stored procedure and then proceed. it's as simple as that in visual studio..\nin query builder u'll get the type as : create procedure <> as.... GO.\nthen in ur code for d form write:\nSqlCommand cmd = new SqlCommand (<proc.name>, <connection name>);\ncmd.CommandType=CommandType.StoredProcedure;\ncmd.Parameters.Add( new SqlParameter("@<pname>",<type>,value,<col name>));\ncmd.ExecuteNonQuery();\nhope thiswill work for u?
1150128
Create an expression field in your query with the expression DateString: Format([DATE],"yyyymmdd")\n\nWhere DateString is whatever you choose to name the field and [DATE] is the name of the field you need to express as a string.\n\nIt may also be possible to create an Input mask in the format you want but I failed to hack that! The above is by far the easiest and most logical method, anyway, since you want to represent the data already in a table, not change the way it is input.
246287
The field names do not matter. It is the types that matter. For instance, you cannot copy a string (e.g. "abc") to a number field. Also, certain autogenerated fields should not be copied.\n\nI am not sure what you mean by dynamic, but the kind of query you would use looks something like this:\nINSERT INTO Table2 ( Field7, Field8, Field9 )\nSELECT Table1.Field1, Table1.Field2, Table1.Field3\nFROM Table1;
1002503
I hate the darn things. I have one in my name and it's a real pain.\n\nApplications written in SQL have a real problem with them too and usually error out if you include one.\n\n\nOther than that it's just plain lazy.
761401
lowercase -> abcdefghijklmnopqrstuvwxyz\nuppercase -> ABCDEFGHIJKLMNOPQRSTUVWXYZ\n=)
714857
It is mysql_num_rows... you forgot the second underscore. Secondly your elseif could probably be written as an else or if you are testing for null being false or not, just test against true and false. \n\nI can't say too much more about the elseif since I don't know what context this snippet is in, but I know it can be a bit cleaner.\n\nTry the change of mysql_num_rows and see what happens.\n\nHope it works for you!
760074
field: LISTID \ntype: INT \nlength: 10\nattributes: unsigned\nnull: not null\nextra: auto_increment\ncheck ON the first radio button (the one under the icon with the key picture)\n\nfield: MYDATE (do NOT use just DATE; DATE is a reserved word in MySQL)\ntype: If you are going to use this with PHP or other Unix-based programming language, you may find it easier to use Unix timestamps, in which case you would make this an INT. Otherwise, DATE\nleave all other items default / blank, unless there will be records without dates. If there will be records without dates, mark the null column NULL\n\nfield: MYWEIGHT\ntype: INT\nlength: 3\nattributes: unsigned (unless you expect negative weights)\nall other items, leave default / blank\n\nfield: MEASUREA (do not use field names that contain blanks)\ntype: FLOAT\nlength/values: 4,2 (the number before the comma is the total length of the float; the number after, the precision. This assumes a float from 00.00 to 99.99)\nattributes: UNSIGNED, unless you expect negative values\nleave all other items blank / default\n\nRepeat for 5, 6, 7, 8\n\nfield: MYBLOG (BLOG is close to BLOB, another reserved MySQL word)\ntype: LONGTEXT\nleave all other items blank / default\ncheck ON the checkbox at the end, under the icon with the T (for FULLTEXT Indexing; this will be useful if you ever want to add text searching to this field)
470920
I am assuming that the code - \nstrSQL = "SELECT * FROM tbl_Hardware WHERE Hardware_ID = 2" \n\nis in a <script language=vbscript> block....\n\nIf I am correct, then that part of the code will be available on the client side, whereas the code that you have in the <%%> block is a sever side code available on the server side.\n\nIf the value of strSql does not change on the client side then one way of doing this is\n\n<%\ndim sql\nsql = "SELECT * FROM tbl_Hardware WHERE Hardware_ID = 2" \n%>\n<script language=vbscript>\nstrSQL = <%=sql%>\n </script>\n<%\nSet rs = cnnLogin.Execute(sql)\n%>\n\nIf the Value of the strSQL changes on the client side, then you need to pass it back to the server on submit using some sort of hidden fields.\n\n-jainnys\nMind Experts Inc\nhttp://www.mindexp.com