tsql

Dynamic SQL (passing table name as parameter)

I want to write a stored proc which will use a parameter, which will be the table name. E.g: @tablename << Parameter SELECT * FROM @tablename How is this possible? I wrote this: set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[GetAllInterviewQuestions] @Alias varchar = null AS BEGIN Exec('Select * FROM Table a...

TSQL difficult join issue

I struggling with a problem I have in TSQL, I need to get the top 10 results for each user from a table that might contain more than 10 results. My natural (and procedurally minded) approach is "for each user in table T select the top 10 results ordered by date". Each time I try to formulate the question in my mind in a set based appro...

Why does SQL Server require INT to be converted to NVARCHAR?

During an ordeal yesterday, I learned that you can't pass this query to EXEC(): @SQL = @SQL + 'WHERE ID = ' + @SomeID EXCEC(@SQL) Where @SomeID is an INT and @SQL is NVARCHAR. This will complain about not being able to convert NVARCHAR back to INT during execution. I realized you have to do it like @SQL = @SQL + 'WHERE ID = ' + CONV...

Single or multiple INSERTs based on values SELECTed

We're extracting booking information for our tennis courts from our SQL database into a simple results table to help us build a picture of court usage. It's pretty straight-forward except when it comes to bookings that last more than an hour. At present, each booking results in a row in our results table. Each row contains a start-time,...

Subquery returns more than one row SQL

Hi there, I have executed a code SELECT CASE b.ON_LOAN when 'Y' then 'In Lib' when 'N' then (SELECT c.duedate from book_copy a, book b, loan c where b.isbn = 123456 and a.isbn = b.isbn and a.book_no = c.book_no) END AS Availability, a.isbn, a.class_number FROM book_copy...

Primary Key when storing a lot of data for a short period of time

Hi, i'm having doubts on how to create a table for temporary storing error messages. A lot of inserts and deletes A GUID as foreign key, likely to be left outer joined A simple nvarchar field for the ErrorMessage Which primary key, if any, and indexes should I use for storing a lot of data for a short period of time? Thanks ...

Query Optimization. Why did TOAD do this?

SQL Server 2008. I have this very large query which has a high cost associated with it. TOAD has Query Tuning functionality in it and the only change made was the following: Before: LEFT OUTER JOIN (SELECT RIN_EXT.rejected, RIN_EXT.scar, RIN.fcreceiver, ...

SQL Two Tables, One Result with extras

I have two tables. The first Holds a list of classes with basically an ClassID and a ClassName. The second Holds a list of classes that a pupil does attend using ClassID and PupilID (PupilID refers to a third table). Is it possible to return for a given pupil a list of ALL Classes irrespective of attendance yet indicate the classes they...

How to construct this query in T-SQL

I have a table: ID A B C D 1 10 20 30 5 2 332 80 32 12 3 41 20 82 42 . . . I want to query that gives me A B C D Where A contains the average of column A for the top 30 rows of the table, sorted by ID ascending, B contains the average of column B for the top 30 rows of the table, sorted by ID asce...

Swap values between two rows of data

The following T-SQL code segment works but i'm wondering if there's a cleverer and/or less verbose way to accomplish the swapping of field values between two different rows. (The code has hard-coded pkey values for simplicity.) BEGIN TRAN; declare @swapFormSeqA int; declare @swapFormSeqB int; SELECT @swapFormSeqA = DisplaySeq FROM Cus...

Updating table based on Select Query in StoredProc / ColdFusion

I am using ColdFusion for for a project and I have a written a query which I think can be faster using a Stored Proc, but I not a T-SQL person, so I am not sure how to do it to compare. I am running an initial query which selects a number of fields from a table based on a dynamically built cfquery. I think I know how to convert this qu...

T-SQL: How to update just date part of datetime field?

In SQL Server 2008 I need to update just the date part of a datetime field. In my stored procedure I receive the new date in datetime format. From this parameter I have to extract the date (not interested in time) and update the existing values date part. How can I do this? ...

OpenRowSet command in TSQL is returning NULLS

Been investigating for a while now and keep hitting a brick wall. I am importing from xls files into temp tables via the OpenRowset command. Now I have a problem where I’m trying to import a certain column has a range values but the most common are the following. Columns structured as long numbers i.e. 15598 and the some columns as stri...

STORED PROCEDURE Calculations & performance improvements

Hi, I currently have the following stored procedure; CREATE PROCEDURE web.insertNewCampaign ( @tmp_Id BIGINT, @tmp_Title VARCHAR(100), @tmp_Content VARCHAR(8000), @tmp_Pledge DECIMAL(7,2), --@tmp_Recipients BIGINT, @tmp_Date DATETIME, @tmp_Private BIT, @tmp_Template BIGINT, @tmp_AddyBook BIGINT ) AS ...

SSMS in SQLCMD mode - Incorrect Syntax

It is my day for weird errors. I use a database project and as a part of Post Deployment, we use SQL Scripts to populate data in our tables. alter authorization on database::atlas to sa; go ALTER DATABASE ATLAS SET MULTI_USER WITH ROLLBACK IMMEDIATE; GO :r C:\Scripts\Script.DisableTriggers.sql :r C:\Scripts\dbo\Script.dbo.PaymentMeth...

SQL Server 2005 - Order of Inner Joins

I have a query containing three inner join statements in the Where clause. The query takes roughly 2 minutes to execute. If I simply change the order of two of the inner joins, performance drops to 40 seconds. How can doing nothing but changing the order of the inner joins have such a drastic impact of query performance? I would have th...

Order a query based on a field pointing to the same table

Hi all, I have a table called "Sentence" that has the following fields: ID <--- OK NextID <--- FK To ID Text So if I had the following records: *ID* *NextID* *Text* 1 12 The quick 3 40 jumps over 5 null lazy dog. 12 ...

4-4-5 Accounting Periods are the Bane of my Existence

We use 4-4-5 Accounting Periods. In case you aren't familiar with this, you can find info at Wikipedia. To accomplish this, I created a date table with all the days for the next 10 years. I used the script (albeit modified) found here. The creation script for my table is: USE [RedFridayDates]; GO SET ANSI_NULLS ON; GO SET QUOTED_...

SQL Server 2000 TSQL : Stored proc results into table

I would like to get the out put of a stored proc call and create a permanent table with out specifying columns. Because the stored proc return lots of columns... So.. how can I do this?? SELECT * INTO MYTABLE FROM MYSTOREDPROC @PARAM1 = 1, @PARAM2 = 'HELLO' is it possible?? Example would help me alot ...

T-SQL trigger effect a change before calling other code

I have a table, let's call is [MYTABLE], with an FOR INSERT, UPDATE trigger. The trigger needs to execute a stored procedure, which will do some work based on the changes made to [MYTABLE]. I can't move the stored procedure's code into the trigger. So far, so good... since triggers execute after the changes are made, the stored procedu...