Question: What are some other strategies on avoiding magic numbers or hard-coded values in your SQL scripts or stored procedures? Consider a stored procedure whose job is to check/update a value of a record based on its StatusID or some other FK lookup table or range of values. Consider a Status table where the ID is most important, as...
If we start with the following simple SQL statement which works. SELECT sor.FPARTNO, sum(sor.FUNETPRICE) FROM sorels sor GROUP BY sor.FPARTNO FPartNo is the part number and the Funetprice is obviously the net price. The user also wants the description and this causes a problem. If I follow up with this: SELECT sor.FPARTNO, sor.fdes...
Here’s the simplest repro case possible. Create a brand new database. (I'm using SQL 2005.) Create a login, a SQL user, and a table in the new database (see sample code below). Launch SSMS and open Object Explorer, logging in as the newly-created user. Attempt to open the "Tables" folder in the Object Explorer. The Problem Fails wit...
My code reads via one connection and writes via another. I dont want to accidentally write with the read connection. How can i make the connection readonly? I am using SQLite ATM and will convert sections of code to tsql when the prototype is over. ...
I want to filter records on 'Email' my query is like this. SELECT * FROM #temp WHERE email NOT IN (SELECT email FROM Customer) It gives me following error Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation. I can use collate if there is equal op...
Why doesn't SQL Server support TRY-CATCH blocks inside UDFs? If we're talking about scalar UDFs, which are mostly used for calculations and conversations, this block should be heavily used, but we don't have it. Also, what workarounds do you use for this? ...
Im trying to use case to vary the value im checking in a where clause but I'm getting the error: incorrect syntax near the keyword 'CASE' SQL Server 2005 select * from table where ((CASE when adsl_order_id like '95037%' then select '000000'+substring(adsl_order_id,6,6) ELSE select adsl_order_id END) ...
I have a rather large linq-to-nhibernate query. I now need to add a filter based on a user-defined function written in t-sql that i have to pass parameters into. In my case, I need to pass in a zipcode that the user types in and pass it to the t-sql function to filter by distance from this zip. Is this possible, or do i need to rewri...
I wrote a trigger that needs to do some different work on a table based on which columns in a row actually updated. I accomplished this using IF UPDATE(column-name) That part works fine. It turns out, however, that there are other parts of the code that update rows by setting every single value whether the value actually changed or n...
I'm trying to use the Resource Governor in SQL Server 2008 but I find it hard to debug the classification function and figure out what the input variables will have, i.e. does SUSER_NAME() contain the domain name? What does the APP_NAME() string look like? It's also hard to verify that it's working correctly. What group did the functi...
I have an old table with a few rows that has a datetime column. I want to switch this to datetimeoffset but i want to be able to transfer the data that already exists. So i'm doing something like [1]. This works but the offset set is 0 when i do the dattime to datetimeoffset assignment. Fortunately the offset that i want to set it to i...
In a table there are like 113 columns. and there are two default records in the table, one is for unknown and another is for inapplicable. So, each column has its own default value to represent unknown and inapplicable. I dont wanna write regular insert statement to get those two records. so, I tried to insert each column using a cu...
For some reason I feel like I'm going about this the painful way. I have an INSERT xml list. I'm using XML because I need to pass in more than one dynamic value. Since we're saving the event, I already have that ID. What I won't know is the Group or Room ID, so I'm using XML to essentially pass in a datatable. Here is an example INSERT...
I have the following select statement that finishes almost instantly. declare @weekending varchar(6) set @weekending = 100103 select InvoicesCharges.orderaccnumber, Accountnumbersorders.accountnumber from Accountnumbersorders, storeinformation, routeselecttable,InvoicesCharges, invoice where InvoicesCharges.pubid = Accountnumber...
I've read up on this on MSDN forums and here and I'm still not clear. I think this is correct: Varchar(max) will be stored as a text datatype, so that has drawbacks. So lets say your field will reliably be under 8000 characters. Like a BusinessName field in my database table. In reality, a business name will probably always be under (pul...
I'm trying to set the value in one table to the sum of the values in another table. Something along these lines: UPDATE table1 SET field1 = SUM(table2.field2) FROM table1 INNER JOIN table2 ON table1.field3 = table2.field3 GROUP BY table1.field3 Of course, as this stands, it won't work - SET doesn't support SUM and it doesn't support ...
I have a piece of code i recently expanded but now must use a transaction. It looks like it can only be access while in a transaction but looks like doesnt make me feel comfortable. Using SQLite and ADO.NET i wrote if(cmd.Transaction==null) but it is null and in a transaction. How should i check? NOTE: I will be using this code with TSQ...
When I'm looking round a SQL Server database I often want to interactively retrieve a simple list of column names for a database table (no data type information etc., just the names) using sqlcmd. I can do this: EXEC sp_columns @table_name = 'tablename' which gives me much more than I want and wraps in a command prompt to the extent ...
I have a table that has an ntext column defined as [value1] [ntext] NOT NULL. I want to add another ntext column to this table that is basically a copy of this column's values (they don't need to stay in sync). To do this, I am using the following SQL: ALTER TABLE [table] ADD [value2] [ntext] NULL UPDATE [table] SET [value2] = [value1] ...
i see a query like select * from Table1 group by Step having count(*) % 2 = 1 what is the trick about "having count(*) % 2 = 1" can anyone explain? edit: what are the common usage areas? ...