T-Sql @@Identity
Does someone know how can I return the @@Identity when using T-Sql? Something like this: set @Sql = "insert into table....values()..." exec @sql return @@Identity ...
tsql
sql query (potentially solvable with pivot / unpivot?)
Hi, I'm not sure how best to approach this - I think a pivot / unpivot should be used, but not sure how to make it work (as pivoting column is a non-numeric) I have the following table (based on a query I can't modify): CREATE TABLE #data (donor_id NVARCHAR(50) ,last_gift DATETIME ,[2005] NVARCHAR(50) ,numgifts05 INT ,value_05 MONEY ,...
Parse XML To build Dynamic Query
Hi All, I am trying to figure out the best way to handle a scenario where I will be passed XML that will contain criteria for a search. If the user has selected specific filters then those will be sent in the XML and if there is a section that they left unfiltered then it will not be present in the XML (Which would mean everything for ...
T-SQL on XML (using XQuery)
I have the below XML <myroot> <scene> <sceneId>983247</sceneId> <item> <coordinates> <coordinate>0</coordinate> <coordinate>1</coordinate> <coordinate>2</coordinate> <coordinate>3</coordinate> </coordinates> <Values> <Value>34</Value> <Value>541</Value> <Value>255</Value> <Value>332</Value> </Values> </item> </scene> </myroot> How can...
need help understanding UPDATE...FROM...
hello, please help me to understand how the T-SQL's UPDATE FROM query works. the sample query below results in value-inc, value-dec value-inc, value-dec value-inc, value-dec value-inc, value-dec value-inc, value-dec value-inc, value-dec i expected this: null, 'value-dec' null, 'value-dec' null, 'value-inc' nul...
What SQL Server DataType should I use for a Text field which will be JOINED, "LIKE'd" and Queried against alot?
The title really says it all, a bit more info though for those who bothered to click. Strings will be of variable length typically between 2-5 characters in length, could occasionalyl exceed 5 characters and be upwards of 10. Never more than 10. Will be queried on like such: SELECT ... WHERE ... = 'abcd'; SELECT ... WHERE ... LIKE 'ab...
Select rows from SQL where column doesn't match something in a string array?
Let's say I have a table, Product, with a column called ProductName, with values like: Lawnmower Weedwacker Backhoe Gas Can Batmobile Now, I have a list, in Notepad, of products that should be excluded from the result set, i.e.: Lawnmower Weedwacker Batmobile In my real-life problem, there are tens of thousands of records, and thou...
Query help - self referential parent table
I have three tables: Type --------------------- TypeID (primary key) ParentTypeID (foreign key) TypeDescription (...) Action --------------------- ActionID (primary key) TypeID (foreign key) ReferenceID (foreign key) Reference --------------------- ReferenceID (primary key) ReferenceDescription (...) Type is self referential. Actio...
Help with TSQL join query
Based on below 2 tables declare @t1 table ( Id int, Title varchar(100), RelatedId int ) insert into @t1 values(1,'A',2) insert into @t1 values(1,'A',3) declare @t2 table ( Id int, Title varchar(100) ) insert into @t2 values (2,'B'), (3,'C') I am trying to get the below output Id Title RelatedItems -------...
Create a sql foreign key constraint
I wish to create a constraint that state as below Code.CodeTable ( CodeID smallint, CategoryID smallint,....) --> Parent Table Admin.Document( DocumentTypeID smallint,.....) --> Child Table The FK will be Admin.Document.DocumentTypeID map with Code.CodeTable.CodeID I wish to have the constraint that only check Code.CodeTable.CodeID...
T-SQL: Stop processing rest of the query.
I wonder if I could stop processing the rest of the query on a specific condition. Scenario IF NOT EXISTS (SELECT * FROM [dbo].[Updates] WHERE RevisionNumber='12.2457.2') BEGIN IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TestProcedure]') AND type in (N'P', N'P...
Update multiple tables in a single update statement with left join
I realise what I'm asking for may be impossible. I want to perform an UPDATE on two separate tables based on a LEFT JOIN and a WHERE clause. This is my attempt: UPDATE PERIODDATES as pd, periods2 as p2 SET pd.[PERIODCODE] = @PERIODCODE, p2.[USERCODE] = @USERCODE left join periods2 AS p2 ON pdates.schemeid =...
sql defining a row by number of times / how it appears as other rows
I'm looking to recategorise some relatively simple information in the most efficient way possible: Using a limited selection of sample data: CREATE TABLE #data (id varchar(30) ,payent_type varchar(30) ,payment_date DATETIME) INSERT INTO #data values ('001','single gift',DATEADD(MM,-12,GETDATE())) INSERT INTO #data values ('001','regula...
SqlException.Message duplicated when calling sqlserver stored proc
I have a stored procedure that gives a friendly enough error that I want to show to the users but when I call it from .net it comes out twice. When I call the proc from sql server management studio it only comes out once. Here is a cutdown version of the stored proc: ALTER PROC [Production].[spDoSomething] ( @PassedID int) ...
What is a good approach in MS SQL Server 2008 to join on a "best" match?
In essence I want to pick the best match of a prefix from the "Rate" table based on the TelephoneNumber field in the "Call" table. Given the example data below, '0123456789' would best match the prefix '012' whilst '0100000000' would best match the prefix '01'. I've included some DML with some more examples of correct matches in the SQL...
SQL with clause dynamic where parameter
I have a tree-style database with the following structure: Table fields: NodeID int ParentID int Name varchar(40) TreeLevel int I would like to use a variable @NodeID in the first part of the with clause to don't get all the table just start from the piece I'm interested in (see where Parent=@ParentID and comment). with RecursionTe...
SQL Server sp_ExecuteSQL and Execution Plans
I have a query which is superfast in SQL Server Management STudio and super slow when run under sp_ExecuteSQL. Is this to do with caching of execution plans not happening when run under spExecuteSQL? ...
problem with multi statement table valued function, what am I doing wrong?
Hi guys, I having problems with this function, seems like @idUsuario and @passCorrecto aren't getting any value, so, when I use this variables in the where clause I'm not getting any result data. ALTER FUNCTION [dbo].[login](@usuario varchar(20), @password varchar(20)) RETURNS @info TABLE (nombre varchar(70) not null, tipo varchar(30) ...
How can we set tables relations in SQL Server Express?
I have SQL Server Express 2008. How can I set a table's relations in it? ...
T-SQL UNION query to return items with highest and lowest rating from the same table
I want write a stored proc in T-SQL to return the top 5 most highly rated and the bottom 5 most lowly rated articles from an Articles table, determined by the 'rating' column. I was thinking of using a union on two selects but I'm not sure how to write it. ...