Structured Query Language (SQL) is a language for managing data in relational database management systems. This tag is for general SQL programming questions; it is not for Microsoft SQL Server, nor does it refer to specific dialects of SQL on its own.
1
vote
1answer
25 views
Storing Hierarchical Data in a Database
in a extension tho this post Pages system PHP/SQL
I've created this class/script to handle Multi-dimensional Menus wich data it's stored in a DB... I need some feedback and new ideas... (all of this ...
1
vote
4answers
28 views
How to refactor the following sql statement?
Declare @IsExistRoleAR As bit
Declare @IsExistRoleEN As bit
Declare @IsExist AS bit
set @IsExistRoleAR=(SELECT CASE WHEN COUNT(RoleID) > 0 THEN 1 ELSE 0 END AS isExists
FROM ...
0
votes
0answers
23 views
Difference Between SP with BEGIN/END and without BEGIN/END [migrated]
I have found some SP(stored procedure) written as,
CREATE PROCEDURE [dbo].[XXX]
(
-- Parameters
)
BEGIN
--- Actual Work
END
and some as
CREATE PROCEDURE [dbo].[XXX]
(
-- Parameters
)
--- ...
0
votes
1answer
43 views
Is this a safe way of using HTTP query parameters to build a SQL query?
Is it? And maybe theres a better way to do this?
$allowed = array("name_desc", "name_asc", "level_desc", "level_asc", "vocation_desc", "vocation_asc");
$order = isset($_GET['order']) ? $_GET['order'] ...
1
vote
1answer
24 views
Summing different tables values limiting the results
I have two tables (a and b) which have both one date field of type TIMESTAMP. They represent two different type of actions (records): table a's actions are $200 worth each, while table b's ones worth ...
0
votes
1answer
73 views
One to one record mapping without any common column between two tables in sql server
I'm trying to join two tables based on one to one mapping without any common column.
For example, I have 3 records in first table and 2 records in second table then the mapping should be as follows
...
3
votes
1answer
57 views
Performance on PHP sql queries
I'm barely learning PHP and MYSQL so I don't know much about performance, I've wrote this script to fetch and format content from my DB, it also counts how many result are and separate them in to ...
4
votes
2answers
52 views
SQL - query syntax
I need advise whether my database structure is correct and what is the appropriate query syntax that can be used.
Table1
...
1
vote
1answer
21 views
Archiving data while running update statement
I'm using sql server and I'm trying to update certain columns in a table but before it updates the data, I want to insert the data into another table.
This is my code
declare @table1 table (Id int, ...
0
votes
1answer
25 views
Optimizing the Query, As it seems to be slow
SELECT BR.IDObject AS IDObject,
B.imgName AS imgName,
BK.imgPath AS imgPath,
BR.imgType AS imgType
FROM ImageReferences BR
INNER ...
2
votes
1answer
53 views
Performance and design question
I'm working on a web application using bottle, and it is, at this point, functional.
The gist of it is that i have a database of drinks, with associated IDs, and associated sets of ingredients. the ...
2
votes
2answers
91 views
Optimise this mysql query
This query is serving me a home page for users where a user can see all posts by him/her and his/her friends. This is an excellent query without any errors but i only wish to make it shorter if ...
4
votes
1answer
68 views
Code Reiview for an PHP PDO Queries? is there a better way to do it?
so this is my code for a equipping an item in my game dev't:
try {
$db = getConnection();
$db->beginTransaction();
$sql_chara_gold = $db->query("SELECT chara_gold ...
1
vote
1answer
36 views
VB.NET | SQL Code Cleanup
I don't know how else to form this question. I am trying to teach myself a new language in three weeks. Can you please give me some advice on how to clean my code and make it faster or more reliable? ...
2
votes
2answers
65 views
Building Session-Variables from a sql stored procedure
I have been advised to submit my code here by a fellow Stack contributer as it was suggested the code could be further improved;
Calling Code and attempt at building session variables:
DataTable ...
2
votes
1answer
45 views
Is there a more optimized way for this MySql Query
with below query i'm getting the results that i want but it's quite slow taking nearly 0.2 seconds (on an i5 machine). Is there a more optimized way to get the same results.
Basically this query ...
1
vote
2answers
75 views
How to optimize this code?
So I have this piece of code in Java(Android) to add a list of brokers to a local SQLite database as one single sql instruction.
public void Add(List<Broker> brokers)
{
if(brokers == null ...
5
votes
2answers
204 views
Strategy to create audit trails for a SQL database
I wish to create audit trails for specific tables and columns in my database, and document who made the change, when it was made, and what the change was.
To do so, I will create the following ...
0
votes
0answers
44 views
Looking to optimize SQL Server merge statement
I have a merge statement that takes around 10 minutes to process 5 million or more records.
The merge statement is part of a stored procedure that takes my newly bulk loaded staging table then runs ...
-1
votes
2answers
136 views
Insert to datagridview when SELECT query has WHERE condition [closed]
I use this code to Load and Insert data to a table using a DataGridView in a C# windows application.
SqlCommand sCommand;
SqlDataAdapter sAdapter;
SqlCommandBuilder sBuilder;
...
1
vote
2answers
70 views
Is there a simpler way to write a row from one table to another?
I have the following code:
const String sqlSelect = "SELECT * FROM UserPasswords WHERE username='System Administrator';";
const String sqlInsert = "INSERT INTO UserPasswords VALUES ...
2
votes
1answer
79 views
How can I improve the following stored procedure?
I have created the following stored procedure which duplicates a record in a table and also all its related records in other tables however since I am a newbie to SQL I would appreciate it if someone ...
0
votes
1answer
32 views
SQL Query based on results of preceding query
I am bashing my head against how to solve this puzzle. Is there any super SQL expert out there who can lend some help
I have a database with the following structure.
adjlevel | scheme | holder | ...
-1
votes
2answers
114 views
Is my PHP code free of SQL injection vulnerabilities?
Could you have a quick look over my code to see if its safe from SQL injection etc.. and suggest any amendments?
<html>
<head><title>Retrieve Your Login Code</title>
...
1
vote
2answers
124 views
Database calls with Python Flask and SQLAlchemy
I am creating a Python Flask webservice and this is basically how I am doing all of my database calls if a webservice needs to interact with the database:
@event.route("/somepath/<value>", ...
4
votes
3answers
161 views
Handling optimistic concurrency violations
I'm trying to establish a concurrency violation verification in my SQL updates using C# and raw SQL.
What I'm doing now is storing the TimeStamp value in a byte[] upon selection and before updating ...
1
vote
1answer
43 views
Request review of sql validation trigger
I have the following SQL structure (simplified for brevity):
TABLE [Posts]
[Id] INT NOT NULL IDENTITY PRIMARY KEY,
[ParentId] INT NULL,
[Type] INT NOT NULL,
FOREIGN ...
0
votes
2answers
85 views
php script to retrieve enum values from sql table
I have an sql table with multiple fields and 4 of them are enums. I wrote a script that runs thought the table and retrieve the enums and put them in a 2 dimension array.
Unfortunately this script is ...
0
votes
1answer
80 views
Query Performance too Slow
Im having performance issues with this query. If I remove the status column it runs very fast but adding the subquery in the column section delays way too much the query 1.02 min. How can I modify ...
2
votes
1answer
81 views
Self Join Exercise. Have I over-complicated it?
This my 3rd question on the same exercise, but by no means a duplicate. The two previous questions were posted on StackOverflow here and here.
Now I'm posting my Oracle solution (below) that works. ...
2
votes
2answers
218 views
Improving the way a C# application communicates with a SQL database (Via SqlConnection)
*Originally posted this on stackoverflow but was told that it would be better suited here.
So I'm looking for a better way to setup how an application talks with a database. I'm sure this question ...
2
votes
1answer
196 views
Returning Key Values from Stored Procedures
I wonder which is better practice when I need to return the primary key value of a newly inserted record from a SQL stored procedure. Consider the following implementations:
As Return Value
CREATE ...
1
vote
0answers
103 views
PostgreSQL: Get all recursive dependencies of a single database object
I've written a SELECT statement that creates a List of all objects that depend on a single object, so that if I wanted to DROP that object I could DROP all referenced objects first without using ...
1
vote
1answer
264 views
SQL stored procedure that returns a boolean value?
CREATE PROCEDURE dbo.foo
AS
BEGIN
DECLARE @true BIT, @false BIT;
SET @true = 1;
SET @false = 0;
IF (some condition)
Select @true;
ELSE
Select @false;
END
SQL is not the language that ...
-1
votes
1answer
59 views
Using xp_cmdshell
Am I doing it fine?? Its only for setup not executed repeatedly
CREATE TABLE #temp
(
id INT IDENTITY(1, 1),
name_file VARCHAR(500),
depth_tree ...
0
votes
1answer
192 views
Hot Observable of Change Tracking Events from SQL Server 2008 R2
After I found the Change Tracking feature in SQL Server, I thought that I would love to have this information in a stream. That lead me to RX, and Hot Observables. Did some reading, and came up with ...
1
vote
2answers
96 views
First Database Schema - How did I do?
I would really like some advice from any DB gurus who have a few minutes free. After doing some reading and playing with sqlfiddle over the weekend I have constructed this postgresql schema and it is ...
0
votes
1answer
46 views
Daily, Weekly, Monthly Individual Tech time in task Repoert
I'm trying to create daily, monthly and weekly SQL Query report to our services time we spent int task and total billing time just want to see if I'm on right track
GO
--Daily
SELECT ...
3
votes
2answers
83 views
PostgreSQL simple query with repeated function calls
I have PostgreSQL 9.1.1 running in a shared hosting environment and I'm running this query:
SELECT a.id,
CASE WHEN a.idpai IS NULL THEN a.nome
ELSE concat(a.nome, ' (', b.nome, ')')
END AS nome, ...
2
votes
1answer
87 views
SQL PreparedStatement; Am I doing it right?
I am building a web app with a single (not pooled) full time (jdbc) connection between static classes and the database. This is expected to be a low traffic site and the static methods are ...
1
vote
1answer
76 views
Sql query to obtain totals and subtotals
I'm interested in knowing if there's a better/cleaner/more efficient way, to obtain totals and subtotals within a query, than my solution below.
The query works fine but I'm just intrigued to know if ...
0
votes
1answer
66 views
How to write a stable WCF service
I'm writing a WCF service for validating password and getting company ID for the clients that requests the services. The service runs on a server locally as a windows service, which sends queries to ...
1
vote
1answer
54 views
(More) efficient and elegant way to retrieve data from a dataset?
I have a DataSet with only two tables in it. The first table is a list of models (of products) with default parameters (settings), and the second table is a list of modules. A module is a product ...
2
votes
2answers
129 views
Subquery v/s inner join in sql server
I have following queries
First one using inner join
SELECT item_ID,item_Code,item_Name
FROM [Pharmacy].[tblitemHdr] I INNER JOIN EMR.tblFavourites F ON I.item_ID=F.itemID
WHERE F.doctorID = ...
0
votes
2answers
103 views
Backup a database over an SQL connection
Developing some industrial WinForms application for some industrial setting, I wanted to provide users of our software with a convenient way to back up the database the software uses (to send it to ...
4
votes
1answer
142 views
SQL Query Tuning
I have a mammoth SQL statement, however it's taking a long time to load (27 secs on the server). I think the issue lies with the IN statements towards the bottom (the IN statement is repeated too, can ...
0
votes
1answer
86 views
Are these tables concepted correctly?
I want to store image (details) in my database, and want for each image the possibility to have from 1 to N versions.
I want a single file (src) to be associated with a single version. There must not ...
0
votes
1answer
105 views
Is this a good way to limit the occurrence of OutOfMemoryException?
I have a class that uses the SqlBulkCopy to bulk insert data into a SQL Server database. My original implementation just passed the m_buffer (which is a reference to a class that implements ...
0
votes
1answer
90 views
Products table or Products + attributes when using EF + MVC 4 [closed]
What I need to build is a web application that maintains and shows products. A product has a lot of attributes, some that will be changed or added during the next year. I have 2 options for designing ...
0
votes
0answers
66 views
Building database from file C# Mysql
well as the question reads I am building a database with 3 tables. Now these tables are going to be used to store names in.
Table 1 will store First Names Table 2 will store Last Names Table 3 will ...
