tsql

Stored procedure - Passing a parameter as xml and reading the data

I have this stored procedure, that reads data from xml and make some inserts ALTER procedure [dbo].[SP_InsertIOs] @iosxml xml AS DECLARE @currRecord int -- parse the records from the XML EXECUTE sp_xml_preparedocument @currRecord OUTPUT, @iosxml BEGIN TRY INSERT INTO SN_IO ( [C1] ,[C2] ,[C3] ) SELECT [C...

SQL function returns wrong value

I have a function that takes 2 parameters: @iEmployeeID and @dDate. It's purpose is to find a budget rate for the given parameters. In other words, it should find the largest date being smaller or equal to the @dDate argument, and return the rate that corresponds. Budget rates are: Start Rate ------- ----- 01-01-2008 6...

How to delete records from Child table and Parent table in a single query?

Hi all, I have a master table and a details table. On delete cascade is not specified. I want delete child record as well as master record in a single query. Suppose I want to delete EmployeeDetails and Employee record where EmpID=20 using a single query. Is it possible? Please help. ...

Fetch record from a table on date difference

I have data like this. Following is the sample data. I want to get all those CustomerIDs which has date diff of 2 hours in DateCreated of Enquiry. For example CustomerId 10602 has 3 enquiries . If the time difference is 2 hours in any of these three enquiries then this CustomerId should be in my result set. Same for the other Custome...

SQL Inserting multilingual data - loses diacritic marks etc

Inserting multilingual data into a SQL 2008 database (nvarchar field) I notice that it seems to lose some special character marks. e.g. INSERT INTO [dbName].[dbo].[tbl_Question_i18n] ([QuestionId] ,[LanguageId] ,[QuestionText]) VALUES (@lastinsertedquestionid ...

Creating T-SQL Summary Rows

I have data in a table that looks like the following sample data: varchar(20) | DateTime | varchar(20) |varchar(255) _Serial_Number__|_Date_Time______________|_System_ID___|_Test_Result________________ C035993 0703 05 |2005-08-18 13:43:33.717 |VTI-Chamber1 | (BLUE) TEST ABORTED, LEAKFP SPUN DOWN C035993 0702 05 |2005-0...

Is it possible to group by a combination of rows?

I have a result that gives me a range of values to query from my database: Start End ----- --- 1 3 5 6 137 139 From those, I need to query the database for the records in that range, which might return something like: Id Name ----- ------ 1 foo 2 bar 3 baz Id N...

For what reason doesn’t SQL allow a variable to be assigned a value using syntax @i =100;?

Unlike programming languages, SQL doesn’t allow variable to be assigned a value using the following syntax ( instead we must use SET or SELECT ): @i=100; //error Is there particular reason why SQL doesn’t allow this? thank you ...

T-SQL query practice/exams/tuning examples

Can the community suggest some T-SQL practice/sample exams, and tips on increasing performance/tuning? This is for preparing interview. Thanks. ...

2 Right Outer Join in SQL Statement

Hi, I have an issue in understanding a very simple yet interesting query concerning 2 right outer joins with 'non-sequential' on-expressions. Here is the query: select * from C right outer join A on A.F1 = C.F1 right outer join B on B.F1 = C.F1; Here are the tables: create table A ( F1 varchar(200)); create table B ( F1 varchar(...

SQL: Single select query from 2 Non-Joining tables

I have 2 tables which doesn't have any references to each other and I'm trying to create a third table (for reference lookup) by selecting from fields from both tables. TableA has an A_ID TableB has a B_ID I want to create Table C which has a 1 to 1 reference between A_ID to B_ID where A_ID = FirstID and B_ID = SecondID, I can't join ...

How to prevent null values for table-valued function parameters?

I have a TSQL Table-Valued Function and it is complex. I want to ensure that one of the parameters cannot be null. Yet, when I specify NOT NULL after my parameter declaration I am presented with SQL errors. Is it possible to prevent a parameter of a Table-Valued Function to be assigned null by the calling SQL? ...

Implementing IF Condition Within a T-SQL UPDATE Statement

Using T-SQL, I would like to execute an UPDATE statement that will SET columns only if the corresponding variables are defined. Here's a simple pseudo-tsql example of what I'm trying to accomplish: --Declaring vars @ID int, @Name nvarchar(20), @Password nvarchar(10) --Run the update UPDATE User SET IF LEN(@NAME) > 0 Name = @Name, ...

how to show price changes in sales report using t-sql

I have two tables Products and SalesLog, SalesLog Table CREATE TABLE [dbo].[SalesLog]( [SalesID] [int] IDENTITY(1,1) NOT NULL, [MemoNo] [int] NULL, [ProductCode] [int] NULL, [Quantity] [int] NULL, [Price] [decimal](10, 2) NULL, [pGroup] [int] NULL, [pName] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [pSize] [int] NULL, [B...

tsql row to column (pivot)

i have a table with the following information CREATE TABLE [dbo].[HR_DEPENDENTS]( [PARENT_ID] [bigint] NOT NULL, [DEPENDENT_ID] [bigint] NOT NULL, [LAST_NAME] [varchar](100) NOT NULL, [FIRST_NAME] [varchar](100) NULL, [DATE_OF_BIRTH] [date]...

Stored procedure SQL SELECT statement issue

Hello everyone, I am using SQL Server 2008 Enterprise on Windows Server 2008 Enterprise. In a stored procedure, we can execute a SELECT statement directly. And it could also be executed in this new way, I am wondering which method is better, and why? New method, declare @teststatement varchar(500) set @teststatement = 'SELECT * from ...

How to pass parameter to stored procedure

Hello everyone, I am using SQL Server 2008 Enterprise on Windows Server 2008 Enterprise. In the stored procedure, I need to pass parameter to a select-where-like statement. For example, @department is store procedure input parameter, its type is varchar(20), select * from sometable where somecolumn LIKE '%@department%' but seems m...

update one table using data from another table

I am trying to update my current table by drawing data from another table. My database (dbo_finance) column - test The other database is assestsc and I am going to pull the data from column issuename1, however I only want to pull the issuename1 when the field [MinSecClass] is = 9. This is what I wrote UPDATE dbo_finance SET [dbo_finan...

T-SQL - Return custom error message and end query

I have a lengthy stored procedure in which I would like to do something like the following: IF @SubPageDirectory IS NULL BEGIN RAISERROR('@SubPageDirectory cannot be NULL', 10, 1) EXIT STORED PROCEDURE END Basically I wish to check whether my variable is NULL, and if it is, return an error message to my .NET Web Application, a...

CASE statement to switch WHERE conditions

I would love to have a t-sql statement like the following... SELECT [field] FROM [table] WHERE CASE @flag WHEN 1 THEN col1 = @value ELSE col2 = @different_value END The point being I want to put a flag in my function to use different where clauses in the same query. I just can't get it to work. Is this possible? ...