Tag: tsql

Logon Trigger for auditing

Using SQL Server 2008R2 I am working on a LOGON trigger to capture users who have logged in. I have created a trigger, but it is capturing the SQL Agent account and is inserting it like mad. I would like to exclude it from the list, but I’m not sure how to modify the trigger […]

Insert into 2 tables with cursor, then use returned scope_identity to insert into another table

Basically I want to use an existing table, lets call it T1. I have to take that table, row by row and insert different columns into 2 separate tables. For example, C1, C2 into T2 and C3,C4 into T3. During both of these inserts I need to make sure that the values that I am […]

Using dynamic DB name in a view

Create view xxx as Select * from DBName.dbo.table1 I have an above mentioned view where DBName is dynamic. How can I create this view? I know we can’t use dynamic variables in a view. I wanted to know how it can be done with the help of a function.

ROUND T-SQL of values 0.0

I am working on a Select statement where I have to show the average of genres but the result must show the 0 values as 0.0 My code is SELECT genreName AS ‘Genre’ , CAST(CASE WHEN AVG(rateValue) IS NULL THEN 0 ELSE ROUND(FORMAT(AVG(rateValue),2),0.0) END AS FLOAT) AS ‘Average Rating’ FROM [IMDB].[FilmGenre] flmgnr LEFT JOIN [IMDB].[FilmGenreAllocation] […]

Delete all files in child tables

I have the following SQL tables: create table dbo.Media ( Id int identity not null constraint PK_Media_Id primary key clustered (Id), Created datetime not null, ) create table dbo.MediaFile ( MediaId int not null, FileId int not null ) create table dbo.[File] ( Id int identity not null constraint PK_File_Id primary key clustered (Id), Content […]

SQL – Row size issue in SQL Server 2012 but not in 2014 version onwards

I am facing an issue while altering a column datatype. This issue is related to maximum allowable table row size. I have gone through lot of posts around this and tried to understand the row size concept and related things. As I understand, RowOverflow issue is fixed post SQL 2005 for variable length datatypes. But […]

Ternary Relation – How to code it in SQL Server ? +derived attribute

i have 3 tables wich are in a tenary relation (1:1:N) in my ER-Modell. to transfer it into SQL-Server, i need to add another table to it, right? (found this solution at least via google). But I’m not so sure how to do it. code: create table[buyer] –1 in the relation (id int primary key) […]

Stored procedure giving “discount” based on X number of months from todays date

So my problem is that I have to make a procedure that will multiply all prices in my product table with 0.8 if the product haven’t sold for X number of months. At the moment I can’t seem to get any further than this: GO CREATE PROC newprice(@numberofmonth int) AS BEGIN DECLARE @today datetime SET […]

Combining a query displays incorrect results

Apologies for the big snippets of code but I need to display them to show the relevant investigation I have compiled. Below I have two separate queries, one checking for crew members in a particular flight from the staging schema (Staging.SabreAssignedCrew) table and the other is a comparison on the data schema tables where after […]

SQL Server Dynamic Search Based on Stored Search Parameters

I have to create a dynamic search. The search criteria is stored in tables and there is a main table for the stored records. Here is the structure: –Main Table. This table stores records of a user. Basically we store files in this table. Each file is associated with a single city. DECLARE @Records TABLE( […]

MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.