I have worked with several big databases and the names of stored procedures were very different: SP_PrefixXXX PrefixYyyXxx Prefix: Rep, Act What's the best practice of naming? How can I organize them in a proper way? ...
It seems like a no brainer to me. I've heard countless stories about people forgetting the WHERE clause in an UPDATE or DELETE and trashing an entire table. I know that careless people shouldn't be issuing queries directly and all that... and that there are legitimate cases where you want to affect all rows, but wouldn't it make sense to...
I have a string which is a output from a function eg: "1,3,16,..,..". I used the following sql query and ran it in the query builder in VS and it didn't give me any syntax errors. SELECT ItemID, Name, RelDate, Price, Status FROM item_k WHERE (ItemID = cast(charindex(',',@itemIDs) as int)) I gave 3,16 as the @itemID parameter values, b...
I have a table with two indices; one is a multi-column clustered index, on a 3 columns: ( symbolid int16, bartime int32, typeid int8 ) The second is non clustered on ( bartime int16 ) The select statement i'm trying to run is: SELECT symbolID, vTrdBuy FROM mvTrdHidUhd WHERE typeID = 1 AND barDateTime...
Duplicate of Dynamic SQL Comma Delimited Value Query Parameterized Queries with Like and In I have a SQL Server Stored procedure where I would like to pass a varchar full of comma delimited values to an in function. Example Declare @Ids varchar(50) Set @Ids = ‘1,2,3,5,4,6,7,98,234’ Select * from sometable where tableid in(@Ids) Thi...
I'm trying to join multiple tables, but one of the tables has multiple records for a partid with different dates. I want to get the record with the most recent date. Here are some example tables: Table: MyParts Partid Partnumber Description 1 ABC-123 Pipe 2 ABC-124 Handle 3 ABC-125 Light Table:...
One table has "John Doe <[email protected]>" while another has "[email protected]". Is there a UDF or alternative method that'll match the email address from the first field against the second field? This isn't going to be production code, I just need it for running an ad-hoc analysis. It's a shame the DB doesn't store both friendly and non-fri...
Suppose we have the following table data: ID parent stage submitted 1 1 1 1 2 1 2 1 3 1 3 0 4 1 4 0 5 5 1 1 6 5 2 1 7 5 3 1 8 5 4 1 As you can ...
I have this query: SELECT (SUM(tblTransaction.AmountPaid) - SUM(tblTransaction.AmountCharged)) AS TenantBalance, tblTransaction.TenantID FROM tblTransaction GROUP BY tblTransaction.TenantID But there's a problem with it; there are other TenantID's that don't have transactions and I want to get those too. For example, the transactio...
I have a statement that looks something like this: MERGE INTO someTable st USING ( SELECT id,field1,field2,etc FROM otherTable ) ot on st.field1=ot.field1 WHEN NOT MATCHED THEN INSERT (field1,field2,etc) VALUES (ot.field1,ot.field2,ot.etc) where otherTable has an autoincrementing id field. I would like the insertion into ...
I am trying to put a try-catch statement inside a trigger using Microsoft Server 2005. BEGIN TRANSACTION BEGIN TRY --Some More SQL COMMIT TRANSACTION END TRY BEGIN CATCH IF (XACT_STATE()) = -1 BEGIN ROLLBACK TRANSACTION; END; END CATCH The problem is that I don't want the trigger to fail if something is caught by the try-catch ...
Every once and a while I have a scenario like this, and can never come up with the most efficient query to pull in the information: Let's say we have a table with three columns (A int, B int, C int). My query needs to answer a question like this: "Tell me what the value of column C is for the largest value of column B where A = 5." A ...
I have a Microsoft SQL Server 2008 with many databases and most of them have a Logs table. I would like to be able to schedule a script to run and truncate the Logs table in every one of these databases (dynamically). I imagine I have to get the name of every user database then truncate the table Logs in the databases that contain a Logs...
I am building a dynamic reporting feature for a client. They want to create new stored procedures, and have them correspond to new reports. We are using T-SQL and each cell in a grid/report can have its own formatting and/or functionality. I'm looking for a format specification to identify presentation, color and conditionals for data.....
I want to find all instances in a table where a string field cannot be cast as a number. Is there a way to do like a "try" in t-sql so that I can get all the id's where the cast failed and delete the value? ...
If I issue SELECT username FROM Users I get this result: username -------- Paul John Mary but what I really need is one row with all the values separated by comma, like this: Paul, John, Mary How do I do this? ...
I am using the .NET 2.0/3.5 framework for my application. I need to run several SQL commands across multiple connections and each connection is on a different server (Oracle, SQL Server). I need to make sure these commands are transactional. For example: I need to perform an INSERT in a table on both Oracle and SQL Server databases, t...
I have a SqlServer customer table customer (first_name, last_name, home_phone, cell_phone) and a text file list of phone numbers like 9876543210, 4564561234, 1231231234, 1234567890, The phone numbers in the customer table are stored in the format +1dddddddddd: where dddddddddd is the phone number. How can I find all the customer ...
Hi I have a table say T in SQL Server 2005 database and it has two columns say A and B, which more often than not won't have any values in them. How to check whether A and B are empty (has all zero length strings) or not? I have this naive way of doing it - select count(*) as A_count from T where A <> '' Let's assume A has data typ...
I have a stored proc containing an SQL statement which is something like: CREATE PROCEDURE SaveUser @UserName nvarchar(10), @FirstName nvarchar(150), @LastName nvarchar(150) AS BEGIN INSERT INTO Users (UserName, FirstName, LastName) VALUES (@UserName, @FirstName, @LastName) SELECT SCOPE_IDENTITY() END Some users cannot...