tsql

Using CASE in Order By Clause

I have the following SQL: ALTER PROCEDURE [dbo].[SP_Products_GetList] @CatID int, @CatName int, @SortBy varchar(50), @SortType varchar(50) AS SELECT Products.ProductID, ProductName, MAX(Price) Price FROM Products INNER JOIN ProductCategory on Products.ProductID = ProductCategory.ProductID INNER JOIN ( ...

How To Move an Updated Record into a History Table?

I have the following table: CREATE TABLE FE_USER ( userid int identity (321,4) CONSTRAINT userid_pk PRIMARY KEY, username varchar(40) ); Its corresponding history table is CREATE TABLE FE_USER_HIST ( userid int, username varchar(40), v_action varchar(50) ); Every time an insert or update is occurred on FE_USER t...

SQL Server - Get first date in a week, given the week number?

I've got a query (for use in bug tracker.net) that calculates the number of bugs by week by status. But the query returns the week number, what I really want is the first date of the week select datepart(wk, DateAdd(day, 0, DateDiff(day, 0, bg_reported_date))) as [week], bg_status , st_name as [status], count(*) as [count] fr...

Temporary Table Scope

I am making use of temporary tables #tempTable in my stored procedure - that I make use to run my ASP.net Reports (Reporting services) I am doing something like eg. Code SELECT * INTO #tempTable FROM Contacts WHERE ContactID < 10 Then I use something like SELECT o.* FROM #tempTable t INNER JOIN Orders o ON t.ContactID =o.ContactID ...

How Can i Let Stored Procedure Returns Varchar Value?

Here is my sample: ALTER PROCEDURE EmpFirstName @myParam int, @empFName varchar(20) output AS BEGIN SET NOCOUNT ON; SELECT @empFName = empfname FROM FE_QEMP WHERE empno = @myParam END GO myParam is the input and empFName will carry the output, so the procedure should only take 1 parameter since empFName is the...

How Can I Export a Record Set to Excel from SQL Server 2008?

I have a department table filled with records. I would like to run a query or use the management studio interface to export department records to an excel file. Format is not an issue, i just want to dump the records into excel. I know i can copy and paste, but this is not what i'm looking for. ...

SQL Server 2008 : replace string

I have a table with an erroneous symbol('�') in a number of rows in one column. The TSQL script below does not work. UPDATE S SET S.Offering_Details = REPLACE(S.Offering_Details, '�', '...') FROM tblSacrifices S The column in question has datatype of nvarchar(230) and allows null entries. The data came from a csv file converted from...

How to select even non existent values

I'm using SQL-Server 2005 I have two tables, Users and Orders. Each user can have many orders. Tables connected via userID. Orders has date column ( which is when order was made ). Users have registrationSite column ( which is who is the affiliate site behind user and all of his orders ). I want to select sum of orders per day and site...

What is the difference between INTERSECT and WHERE IN ?

I have written and passed 70-433 (SQL 2008 MCTS exam) and was reviewing when I realised that I have been doing what INTERSECT does with a WHERE IN subquery, as well as EXCEPT with a WHERE NOT IN subquery. Are there any differences between using the new commands instead of subqueries? ...

Delete from table the records that are in #temp table

Hello, I've created a #temp table in SQL containing duplicate records. I would like to remove from my primary table all of the records contained in this temp table. I see samples to do this but they seem to all invovle selects, and I already have my select in the temp table. Here's what it would look like in pseudocode: DELETE FROM ...

Performance characteristics of T-SQL CTEs

Hi guys, I've got some SQL that looks roughly like this: with InterestingObjects(ObjectID, OtherInformation, Whatever) as ( select X.ObjectID, Y.OtherInformation, Z.Whatever from X join Y join Z -- abbreviated for brevity ) -- ...long query follows, which uses InterestingObjects in several more CTEs, -- and then uses those CTE...

using inserted and deleted tables in the triggers

I want to write triggers to work with the inserted and deleted tables. I have written the trigger for inserting : CREATE TRIGGER FILL_TABLE ON Person FOR INSERT AS DECLARE @ID int SELECT @ID = p.ID FROM Person AS p INNER JOIN inserted AS i ON p.ID = i.ID DECLARE @uName char(30); SELECT @uName=SYSTEM_USER INSERT tblOperation...

Creating a unique id (pin number) for each record of a table

I want to create a pin number that is unique within a table but not incremental to make it harder for people to guess. Ideally I'd like to be able to create this within SQL Server but I can do it via ASP.Net if needed. Thanks, Josh EDIT Sorry if I wasn't clear - I'm not looking for a GUID as all I need is a unique id for that table - ...

how to check if a type of row exists in a table

Hi I have an order table and a payment table which are linked by order_num. I would like to get entries from order table who have entries in the payment table of PaymentType 'A' as well as PaymentType 'B' and their amounts match. Example order number 411 should only be returned to me if it has at least two payments and one of them is p...

T-SQL schemata to organize code

Hi, I have a ms sql server database with a growing number of stored procedures and user defined functions and I see some need to organize the code better. My idea was to split sps and functions over several schemata. The default schema would hold the the sps called from the outside. The API of the database in other words. A second schem...

Not sure how to go about joining these two tables.

I had trouble coming up with a title for this question because I'm not really sure how to do what I want, so I'm not sure if "joining" is the best terminology in this instance. I have two tables in my database, Plans and Assumptions that look like the following: Plans Table Plankey ass1 ass2 ass3 ass4 aplan 0 6 5 7 bplan...

SQL Cursor/Population Question

So here's the setup. I have two tables: CREATE TABLE dbo.TmpFeesToRules1(Name varchar, LookupId int) CREATE TABLE dbo.TempFeesToRules2(FeeId int, Name varchar) I have a third table called 'Fee' in the database that's already created. I want to populate dbo.TmpFeesToRules1 'Name' field with the DISTINCT 'Name' from 'Fee'. Would I do th...

Select a specific column based on another column's value

I have a table like this ID | Type | Val0 | Val1 1 | 0 | A | NULL 2 | 1 | NULL | B I need to select Val0 when the type is 0, and Val1 when the type is 1, and ValN when type is N... How can I do that? ...

Way to diff two SQL CE Databases

I know ways to script a SQL CE Database, but does anyone know a way to diff two databases and output a script sync one of them to the other (ie generate drops and inserts to make them the same). I am looking for a way that I can update my hand held applications without having to copy over the existing database. The first step is to be ...

Updating table based on 2 tables with RowID is as commonality

Hi there I have a table like this: RowID; ListDescription1; ListNormalisedDescription1; 1 XXXX YYYY NULL 2 ZZZZZ NULL I made a complex transformation/normalisation (removing spaces, replacing space and split) and manage to make the same data turning into: RowID; NormalisedItemDescrption1; 1 XXXX 1 ...