Tagged Questions

Transact-SQL (TSQL) is the extended SQL dialect used in Microsoft SQL Server and Sybase

learn more… | top users | synonyms

1
vote
0answers
23 views

Code review on selecting the number of working day minus weekend days and UK Bank holidays

I am fairly new to coding TSQL script so looking for some second view on my script. The goal here is to to pull in some data and amongst that data to show a field with a counting the number of working ...
1
vote
1answer
51 views

DRY cursors: preventing T-SQL's FETCH statement from being repeated

Please assume I've exhaustively tried to come up with a set-based solution to my T-SQL problem, and that I need to use a cursor. The following is the typical1 boilerplate for a T-SQL cursor: ...
2
votes
1answer
55 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
2answers
85 views

How can I make this WHERE clause more readable?

Without changing the semantics and performance, how can I make this where clause more readable? where (@Category = 'all' or (@Category = 'omitted' and Category is null) or (@Category = ...
2
votes
1answer
90 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 ...
2
votes
1answer
801 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
votes
1answer
98 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
0answers
74 views

T-SQL BFS center node of undirected graph

I am trying to answer this question by using a breadth-first search from each node of the graph represented by the tables mapSolarSystems and mapSolarSystemJumps in the data dump at ...
0
votes
1answer
67 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 ...
1
vote
1answer
93 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 ...
3
votes
1answer
123 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 ...
0
votes
0answers
756 views

Splitting an address into fields in SQL

I have the following code to split an address string in T-SQL. Excepting the unit number, which is already in its own field, it would affect too much of the application to split the address into ...
3
votes
2answers
130 views

Any way to speed up this UPDATE?

SQL: CREATE FUNCTION dbo.fnRandomForeNames () RETURNS VARCHAR(50) AS BEGIN RETURN ( SELECT TOP 1 [FirstName] FROM [tmp_ForeNames] ORDER BY (SELECT new_id from ...
1
vote
1answer
134 views

Generic PHP function for making a struct out of multiple resultsets from a stored procedure call

The stored procedures must return their first resultset in the form: ( [index] int, [id] varchar(50) ) where [index] is 0,1,2,3,... and [id] is the name of the struct. As long as ...
0
votes
1answer
878 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
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 ...
5
votes
1answer
1k 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 ...
1
vote
1answer
125 views

List of students with their classname in current year

I have 3 tables: students and classes and years. In a forth table called StudentsInClass, I'm saving students classes in each year . Now I want to show a list of students with their className in ...
2
votes
1answer
110 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
118 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
556 views

query to get number of related records in 2 child tables

I have a category table ( brands ) and 2 other tables ( Pens and pencils ) that have parent-child relation with this table through a field "CatID" . now I want to get a list of Brands and number of ...
2
votes
1answer
75 views

Critique my SQL

Table Structure ApprovalOrder int EntityCode varchar CostCentre varchar DelegationCode varchar ProjectCode varchar RoleGroup varchar Position varchar ...
1
vote
3answers
243 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
153 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 ...
4
votes
4answers
189 views

I'm looking for comments (good or bad) and suggestions (good only) on a T-SQL statement to breakdown UserAgent information

I have a table that contains the UserAgent string and a Count of how many times its seen. the T-SQL below is used to give a breakdown of what browsers are seen and how often then are seen. SELECT ...
3
votes
4answers
215 views

T-SQL transaction

Can someone help me refactor this code so in case of an error nothing gets persisted to the db? this doesn't work when an error occurs. use mydb Begin Transaction SET IDENTITY_INSERT table1 On ...
1
vote
0answers
298 views

TSQL - Insert into “TableA” from “TableB” if “TableA” doesn't have “TableB” row

I'm trying to improve a query that i'm using on a project. I'm going to show an example of the tables simplified: TBL_FORMATION: a table for formations TBL_TEAM: a table with the information for ...
2
votes
3answers
302 views

Reduce the code in a WHERE clause without using dynamic SQL

How can I write the following T-SQL query part in a shorter way without using dynamic SQL? WHERE ( (@Max IS NULL OR @Type <> 'Products') OR (@Max IS NOT NULL AND @Type = 'Products' ...
4
votes
1answer
374 views

Tame this Beast: TSQL Unpivot

Okay... here's the beast: SELECT SUBSTRING(DischDate, 7, 4) + SUBSTRING(DischDate, 1, 2) as YYYYMM ,Type ,SubType ,Diags ,Count(*) as Count ,SUM(Charges) as Charges ...