You’ll need to be a little more careful with your string manipulation. You can’t use REPLACE() for this, because that will replace multiple occurrences, corrupting your data if one element in the comma-separated list is a substring of another element. The INSERT() string function is better for this, not to be confused with the INSERT … Read more
Unless you are writing an application which you know for a fact will need to be portable or you only want quite basic information I would just default to using the proprietary SQL Server system views to begin with. The Information_Schema views only show objects that are compatible with the SQL-92 standard. This means there … Read more
Your assumption is right, this is the optimal way to do it and it’s called upsert/merge. Importance of UPSERT – from sqlservercentral.com: For every update in the case mentioned above we are removing one additional read from the table if we use the UPSERT instead of EXISTS. Unfortunately for an Insert, both the UPSERT and … Read more
When we execute a stored procedure in JDBC we get back a series of zero or more “results”. We can then process those “results” sequentially by calling CallableStatement#getMoreResults(). Each “result” can contain zero or more rows of data that we can retrieve with a ResultSet object, an update count for a DML statement (INSERT, UPDATE, … Read more
Passing parameters to SQL saves you from having to build a dynamic SQL string. Building dynamic SQL statements is a HUGE security risk because people can inject their own SQL code into your application, possibly executing undesirable commands against your data. There are some good samples of possible SQL Injection attacks at: SQL Injection Attacks … Read more
SET @cname:=’jello’; SET @vname:=’dwb’; SET @sql_text = concat(‘select concept_id,concept_name,’,@vname,’ from enc2.concept a JOIN enc2.ratings b USING(concept_id) where concept_name like (”%’,@cname,’%”) and 3 is not null order by 3 asc’); PREPARE stmt FROM @sql_text; EXECUTE stmt; DEALLOCATE PREPARE stmt;
The easy way is to right-click on the procedure in Sql Server Management Studio (SSMS), select ‘Execute stored procedure…” and add values for the input parameters as prompted. SSMS will then generate the code to run the procedure in a new query window, and execute it for you. You can study the generated code to … Read more
Did you run aspnet_regsql.exe against WinHost.com’s sql server? aspnet_regsql.exe -S DBServerName -U DBLogin -P DBPassword -A all -d DBName If you don’t know where to run above command then you can simply run ‘aspnet_regsql.exe’ executable file. In order to locate this file open your RUN Command Box by pressing Windows Key + r and put … Read more
Procedural elements like loops are not part of the SQL language and can only be used inside the body of a procedural language function, procedure (Postgres 11 or later) or a DO statement, where such additional elements are defined by the respective procedural language. The default is PL/pgSQL, but there are others. Example with plpgsql: … Read more
SQLite has had to sacrifice other characteristics that some people find useful, such as high concurrency, fine-grained access control, a rich set of built-in functions, stored procedures, esoteric SQL language features, XML and/or Java extensions, tera- or peta-byte scalability, and so forth Source : Appropriate Uses For SQLite