tsql

What is the comparative speed of temporary tables to physical tables in SQL?

I have a script that needs to extract data temporarily to do extra operations on it, but then doesn't need to store it any further after the script has run. I currently have the data in question in a series of temporary local tables (CREATE TABLE #table), which are then dropped as their use is completed. I was considering switching to ...

CLR UDF Exception In SQL Server 2005

When I try my CLR UDF, I am getting this error: Msg 6522, Level 16, State 1, Line 1 A .NET Framework error occurred during execution of user-defined routine or aggregate "getFileSize": System.Security.SecurityException: Request for the permission of type 'System.Security.Permissions.FileIOPermission, mscorlib, Version=2.0.0.0, Culture...

How can I compare two datetime fields but ignore the year?

I get to dust off my VBScript hat and write some classic ASP to query a SQL Server 2000 database. Here's the scenario: I have two datetime fields called fieldA and fieldB. fieldB will never have a year value that's greater than the year of fieldA It is possible the that two fields will have the same year. What I want is all records ...

Is it possible to write a stored procedure with dynamic parameters like sp_executesql?

Is it possible to write a stored procedure (in tsql or other variants) with dynamic parameters like sp_executesql? i.e. the number and type of parameters in not known in advance and should be built dynamically on client side. just the way you can pass any number of parameters with any type to sp_executesql. ...

Raiseerror and Concat for the Message

I'd like to do something like this raiserror(concat('Error in case @isFishy =', @isFishy, ' @isSmarmy=', @isSmarmy, ' @isTasty = ', @isTasty), 10, 1) --or raiserror('Error in case @isFishy =' + @isFishy + ' @isSmarmy=' + @isSmarmy + ' @isTasty = ' + @isTasty, 10, 1) But it just isn't working. How do I accomplish this? I'm in SQL S...

Using SQL Server DTS Package to Conditionally Insert / Update Rows in Destination Table

I want to create a DTS Package to pull data from an Oracle table into a SQL2K table. How can I insert rows that are not already in the SQL2K table and update rows that already exist in the SQL2K table? I guess I could truncate and repopulate the entire table or create a temporary table and then do updates/inserts from the temp table...

Creating stored procedure on the fly. What are the risks/problems?

I am thinking about creating stored procedures on the fly. ie running CREATE PROCEDURE... when the (web) application is running. What are the risks or problems that it can cause? I know that the database account needs to have the extra privileges. It does NOT happen everyday. Only from time to time. I am using sql server and inter...

TSQL Email Validation (without regex)

Ok, there are a million regexes out there for validating an email address, but how about some basic email validation that can be integrated into a TSQL query for Sql Server 2005? I don't want to use a CLR procedure or function. Just straight TSQL. Has anybody tackled this already? ...

Is there a clean T-SQL query I can use to verify an index has the right columns?

I am writing a DB upgrade script that will check to see if an index has the right two columns defined. If it doesn't, or if it only has one of them, then I will DROP it (is there a way to ALTER an index?) and then recreate it with both. ...

Calling a Stored Proc from within a Stored Proc and returning a recordset.

I have a Stored Procedure that rolls-back a series of operations. I want to call this from within another SP. The problem is that the inner SP returns a record set with a single value that indicates the degree of success. This approach worked well and has some advantages in our context, but in retrospect, I would have done it the con...

Insert default value when parameter is null

I have a table that has a column with a default value: create table t ( value varchar(50) default ('something') ) I'm using a stored procedure to insert values into this table: create procedure t_insert ( @value varchar(50) = null ) as insert into t (value) values (@value) The question is, how do I get it to use the defaul...

In SQL, how can you "group by" in ranges?

Suppose I have a table with a numeric column (lets call it "score"). I'd like to generate a table of counts, that shows how many times scores appeared in each range. For example: score range | number of occurrences ------------------------------------- 0-9 | 11 10-19 | 14 20-29 | 3 ......

Confused with aggregates in T-SQL

Ok: This is some of my table structure that matters here CaseStudyID int Title nvarchar(50) OverrideTitle nvarchar(50) Part of my procedure Declare @Temp table(CaseStudyID int, Title nvarchar(50)) Insert Into @Temp SELECT CaseStudyID,Title FROM CaseStudy WHERE Visible = 1 AND DisplayOnHomePage = 1 ORDER BY Title Update...

TSQL: How to fill a variable using a generated sql command string without a cursor

I'm trying to get the value of generated sql inside a stored procedure. Executing this DECLARE @sSQL varchar(5000), @retval int DECLARE @tablename varchar(50) SELECT @tablename = 'products' SELECT @sSQL = 'SELECT @retval = MAX(ID)' SELECT @sSQL = @sSQL + ' FROM ' + @tablename EXEC (@sSQL) SELECT @retval I get ...

INSERT vs INSERT INTO

I have been working with TSQL in MSSQL for some time now and somehow whenever I have to insert data into a table I tend to use syntax INSERT INTO myTable <something here> I understand that keyword INTO is optional here and I do not have to use it but somehow it grew into habit in my case. My question is: Are there any implications...

Aggregate adjacent only records with T-SQL

Hi, I have (simplified for the example) a table with the following data Row Start Finish ID Amount --- --------- ---------- -- ------ 1 2008-10-01 2008-10-02 01 10 2 2008-10-02 2008-10-03 02 20 3 2008-10-03 2008-10-04 01 38 4 2008-10-04 2008-10-05 01 23 5 2008-10-05 2008-10-06...

what's the best way to implement logging during transactions in T-SQL?

I want to implement a simple debug log, which consists of a table into which I insert insightful messages about the current state of a transaction. How do I stop inserts to this table being affected by rollbacks on the transaction that I'm trying to debug? In Oracle I can use PRAGMA AUTONOMOUS_TRANSACTION to ensure that the inserts are d...

How to combine variable assignment with data-retrieval operations in T-SQL

Just to clarify, I'm running Sybase 12.5.3, but I am lead to believe that this holds true for SQL Server 2005 too. Basically, I'm trying to write a query that looks a little like this, I've simplified it as much as possible to highlight the problem: DECLARE @a int, @b int, @c int SELECT @a = huzzah.a ,@b = huzzah.b ,@c = ...

SQL Date Formulas

I need a date formula in Oracle SQL or T-SQL that will return a date of the previous week (eg Last Monday's date). I have reports with parameters that are run each week usually with parameter dates mon-friday or sunday-saturday of the previous week. I'd like to not have to type in the dates when i run the reports each week. The data ...

Aggregate GREATEST in T-SQL

My SQL is rusty -- I have a simple requirement to calculate the sum of the greater of two column values: CREATE TABLE [dbo].[Test] ( column1 int NOT NULL, column2 int NOT NULL ); insert into Test (column1, column2) values (2,3) insert into Test (column1, column2) values (6,3) insert into Test (column1, column2) values (4,6) in...