Microsoft's SQL Server is a relational database management system (RDBMS) that runs as a server providing multi-user access to a number of databases. It originated from the Sybase SQL Server codebase, which is why both products use the extension of SQL called Transact-SQL (T-SQL).
0
votes
1answer
9 views
how to write complex stored procedure very simple way
i have one stored procedure like this:
declare @compl integer = null, @transid integer = null, @complid integer = null
select @transid = t.transactID
from Transaction_tbl t
where ...
1
vote
4answers
35 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
)
--- ...
-2
votes
1answer
33 views
advanced asp.net improve performance [closed]
asked for help on my application , that's run on windows server 2003 and iis 6.0 ,this applications gross now , users now about 60,000 per day. and that what i did to make it faster
a.SQL server 2005 ...
1
vote
1answer
22 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, ...
2
votes
1answer
32 views
SQL Server 'Execute As'/Revert pattern in a 'Try/Catch' Block
I wish to ensure I am using the "best" pattern when using an Execute As/Revert from within a Try/Catch block on SQL Server 2012. The below code "seems" to behave correctly... Am I missing anything or ...
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 ...
2
votes
1answer
80 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 ...
3
votes
2answers
84 views
Simple DBLayer class review
I have created a simple DBLayer class. But I am not sure whether this class have any bug/issue using in production.
public class DBLayer
{
private static string connectionString = ...
1
vote
1answer
45 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
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 ...
1
vote
1answer
322 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
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 ...
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
77 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 ...
1
vote
1answer
395 views
Custom Paging in ASP.Net Web Application
I have following code for doing custom paging from an asp.net web application.
Points of interest
It uses Link Buttons as suggested in
...
2
votes
2answers
132 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 ...
1
vote
1answer
66 views
Throwing a custom error in a stored procedure
I'm wanting to throw an error when reference keys are missing, however instead of failing through a integrity check, I want to list out the missing keys. I've created the below which works. However ...
4
votes
1answer
143 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
3answers
219 views
Using a Dedicated Class & Constructor to initialize Stored Procedure for SQL Transaction
as i thought it will be sutible as a Quetion for SO...
i started to describe the issue of my Problem, though as i completed Editing the post , i could see that it will better fit here, In CR.
so to ...
0
votes
1answer
109 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
73 views
Review Insert/Update stored procedure
I am not a DBA nor do I play one on TV.
My application is retrieving information from Facebook and storing it into our own database. I want to retrieve the Location Post information of Id and ...
1
vote
1answer
177 views
How to dump UCCX stored procedure results via c# to SQL Server
I am writing a simple-ish c# CLI app to improve our telephony reporting system.
At present, I have a nightly DTS package which dumps a couple of tables from the Cisco telephony box into a database ...
1
vote
2answers
99 views
Is My Data Access Leaking Connections?
Folks
I have an N-Tiered ASP.Net Web Forms application which uses Enterprise Library 5.0 for data persistence. Recently I have noticed my application has been spitting out the following error
...
3
votes
1answer
78 views
Is it necessary to replace this cursor in SQL Server 2005?
The Problem
I have a cursor that I am trying to replace (perhaps unnecessarily) in an attempt to clean up a stored procedure. Essentially what it is doing is counting each note for each member in a ...
6
votes
1answer
78 views
Auditing a complex mix of reasonably small tables
I plan to use the following approach to audit the tables that represent user-editable configuration of an automated system (over the course of system's life these will be inevitably extended in their ...
3
votes
1answer
101 views
SQL view, better way?
I there a better way to write this query? I feel sure it can be done in one statement with joins, but I couldn't get it, and I'm afraid I've made it too slow. What suggestions do you have? I'm ...
2
votes
4answers
1k views
SQL Server Split function optimized
I know there are some answers about a user function to split chars and I have one already running (found one years ago in the net and modified it to my own needs).
Since I use this function very ...
2
votes
3answers
1k views
Search using stored procedure
I want to search a DB table and display the records in a gridview below is my stored proc
create procedure search
@Firstname varchar (50),
@Lastname varchar (50),
@Gender ...
1
vote
1answer
33 views
SQL iteration-Insertion plus renaming
Base Info
Two tables: tData, tData2
Exactly the same columns
About 200,000 records
SQL Server 2008 R2
Logic
At first sight we need to insert tData rows into tData2. What else?
We need a ...
2
votes
2answers
1k views
PHP Login/User Creation Validation Function
I'm VERY new to PHP and have begun writing a basic login for a website that uses data from a Microsoft SQL server to display information on the web. Below are 3 snippets of code (two functions from ...
0
votes
1answer
606 views
Data Structure for Categories and SubCategories
I have this relationship:
Many Objects to One Category
One Category to Many SubCategories
I am looking for the best way to store this in my Relational Database? Here were some of the ways I was ...
3
votes
2answers
496 views
Which SQL statement is faster
My co-worker claims that using a parameterized Like statement is equivalent to dynamic sql and won't have its execution plan cached for reuse. He says that using sp_executesql will allow the execution ...
3
votes
1answer
132 views
Getting rows from several tables where one table doesn't include the rows from the next tables
I have some entity that is spread across three tables.
I have to get in a single result the following values:
All the values that are in the first table but not on the second nor the third.
All ...
0
votes
1answer
125 views
How to refactor this cursor used in a SQL query
So I got this query from another analyst here and I'm a little stumped as to why a cursor was used instead of just joins. Could I please have some help trying to refactor it? I am using MSSQL 2008.
...
5
votes
1answer
806 views
Conditional Create: must be the only statement in the batch
I only want to create this SQL function if the dependent Assembly exists.
I can do it using dynamic SQL, but it seems messy and I lose syntax checking (in
management studio). This function's ...
0
votes
2answers
193 views
Critique my Database design/help me simplify my C# code please
I wish to create a database for a webpage where users are able to add their own events to a timetable. Users will be able to decide if the events should be recurring (weekly) or not. Users should be ...
2
votes
1answer
108 views
Refactor my simple SQL Statement
I have the following SQL statement that I think could be improved in areas (I believe there may be a way to use where over having, but I'm not sure how and I'm sure there's a way to reference the last ...
3
votes
1answer
99 views
output parameter and exists condition
I want to return title of a record if exists or the word 'invalid' if not.
@ID int ,
@Title nvarchar(50) output
...
if exists(select * from MyTable where ID=@ID)
select @Title = ...
1
vote
1answer
90 views
SQL table normalization upto what extent
I have an asp.net form which contains like 5 tabs and each tab has at least 15 fields in it. They all belong to one record so for the sake of simplicity lets call the PK as RecordId. Now i have 2 ...
2
votes
1answer
113 views
Slow Oracle to MSSQL migration
I wrote a method that gathers data from an Oracle server, formats and encrypts the data then inserts it into a MS SQL server. The method moves about 60000 records and takes a bit long and is a little ...
2
votes
1answer
74 views
Critique my SQL
Table Structure
ApprovalOrder int
EntityCode varchar
CostCentre varchar
DelegationCode varchar
ProjectCode varchar
RoleGroup varchar
Position varchar
...
1
vote
3answers
217 views
How can I improve this sorting algorithm?
I have a table in a SQL Server database, which holds information of some images, and the relevant gallery of them. The columns are like:
ImageId, GalleryId, Order
I have a unique key on ...
1
vote
2answers
145 views
SQL - Refactoring - How can this be done better?
I've been working on a semi-awkward query in that it uses a very high number of functions given its relatively small size and scope. I was hoping to get some feedback on any ways I could format or ...
2
votes
2answers
174 views
SQL - How's my formatting?
I'm somewhat new to SQL (using it in the past but only being exposed to it heavily in my current role). Unfortunately nobody at my current company has really given me any advice on formatting. How can ...
1
vote
2answers
584 views
Database design for an autoparts store
I'm creating a design database for an autoparts store (this is a project school). For the moment, I'm not sure if the relationship model especially in MODEL, MAKE, BRAND is correct (I really have no ...
7
votes
1answer
302 views
Improve upon this SQL Server stored procedure boilerplate?
I often write little stored procedures to do this and that. Sometimes they are just for utility to return some diagnostic-level information, sometimes they are horrendously large, sometimes they are ...
3
votes
3answers
481 views
Inner join with first result
In SQL Server, there is two tables: Houses, and their images.
I need a list with 20 houses with the first of their images (only one). I tried:
SELECT top 20 h.id, h.name, im.id, im.name
FROM ...
