Programming SQL

Select Case Statement
Can someone tell me why this simple Select Case is wrong. This is my first attempt at this. I'm doing this on SQL 2005. Works fine until I try to do a CASE. SELECT Case Limit When 'MKT' THEN 'MKT' ELSE 'LMT' FROM MoxyOrders This is the error message. ... 18 Jul 2008 15:29

Stored Proc problem
I have a new store procedure that is giving me 2 problems. First, it is returning a value of 1 instead of the actual new identity. Second, the TRAN is rolling back and I'm not sure why. Any help is appreciated. Below is the sp: SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[fd_up... 18 Jul 2008 18:35

Trap when SELECT returns >1 row
In my sproc, have a SELECT which should always return ONE row. However, I'd like to trap if returns zero rows or >1 row. How could I do this using TRY...CATCH ? Thanks ... 18 Jul 2008 15:29

join data from multiple tables with no keys
In the database I am working out of there is one table (treatment_events) that collect the ids from other tables that are linked throught the application users use to enter the data. I cannot manually join these tables through using joins because they do not have related keys. The table that I can link them to that... 18 Jul 2008 15:29

Am I guaranteed that TRUNCATE TABLE always can be rolled back within a transaction?
When TRUNCATE is used inside a transaction, if the transaction is rolled back the TRUNCATE is rolled back too. So TRUNCATE is safe within a transaction. I took a quick look at your code and it appears to be fine, but I did not test it. Roy Harvey Beacon Falls, CT On Fri, 18 Jul 2008 10:05:35 -0700 (PDT), ... 18 Jul 2008 14:26

Am I guaranteed that TRUNCATE TABLE always can be rolled back within a transaction?
Is the statement below always true? I can use TRUNCATE TABLE as in the example below and I am guaranteed that if INSERT fails for some reason, table Result will be restored to its initial (before the transaction began) state? In other words, can I rely on the fact that the transaction scope prevents the pages... 18 Jul 2008 15:29

"Not Null Default" column behavior change in SQL 2K and SQL 2005
I happened to migrate my database from SQL 2K to SQL 2005. Pretty much everything went well when I simply did backup in SQL 2K and restored it into a pre-created database in SQL Server 2005. Here is the issue I am trying to address I had a table example Create TableA ( ColA Varchar(10), ColB datetime N... 30 Jul 2008 14:37

CAST question
I have a column that has batch numbers, but it's a varchar column "WHERE batch between 1500 and 1625" I need to find out if that batch numbers are between 1500 and 1625, however I'm getting an error: The conversion of the varchar value '2393337112' overflowed an int column. Maximum integer value exceeded. H... 18 Jul 2008 13:24

Select returns no rows within TRY... END TRY
I have this: BEGIN TRY SELECT @BankName = BankName, @BankRouting = BankRouting, @BankAccount = BankAccount, @BankAccountHolder = BankAccountHolder, @AccountType = AccountType FROM dbo.BankAccount WHERE ParentId = @ParentID AND (AccountActiveDate <= @RunDate) AND (AccountInactiveDate > @RunDate)) ... 18 Jul 2008 15:29

dynamic SQL and temp tables
I'm doing that I thought would work but maybe not: trying to use dynamic SQL to create a temp table via SELECT / INTO. Here's a stripped-down version of my code: DECLARE @sql NVARCHAR(4000) SET @sql = 'SELECT * ' + 'INTO #mytemp ' + 'FROM any_other_table o ' + 'WHERE o.subm_no = 118731' EXEC sp_execut... 18 Jul 2008 13:24