tsql

How to select column values which throw an error if cast to a new type.

Hi there, I need an sql query to give me the following: All the values in a column of type varchar(50) which will NOT convert or will throw an error if cast / converted to an int. E.g. row 1: '1' row 2: '2' row 3: '3a' row 4: '4.5' I need row 3....however there are tens of thousands of rows. Thanks! ...

For Nvarchar(Max) I am only getting 4000 characters in TSQL?

Hi, This is for SS 2005. Why I am i only getting 4000 characters and not 8000? It truncates the string @SQL1 at 4000. ALTER PROCEDURE sp_AlloctionReport( @where NVARCHAR(1000), @alldate NVARCHAR(200), @alldateprevweek NVARCHAR(200)) AS DECLARE @SQL1 NVARCHAR(Max) SET @SQL1 = 'SELECT DISTINCT VenueInfo.VenueID, Ve...

How about the performance between PATINDEX and CHARINDEX?

At all times I use CHARINDEX in stored procedure to check NVARCHAR(MAX) type of variable, but today I find that the CHARINDEX has an 8,000 byte limit! I find this article SQL CHARINDEX() Has Data Size Limitations. So I replace CHARINDEX with PATINDEX, but I do not know the performance between PATINDEX and CHARINDEX. ...

How do I 'subtract' sql tables?

Its not really a subtraction I'm looking for. And I know its not a union or intersection... I have been given a long and complex stored procedure that returns a table of active and inactive documents. I have also been given a similar stored procedure that returns another table that contains only the active documents. How could I ge...

Internal SQL Server Error when referencing a named instance of SQL

I am trying to script some inserts from a source database to a target database, using linked servers. For the first time we are using a second named instance of SQL, and it is giving me an "Internal SQL Server Error" when I run the following code. If I remove the brackets around the named instance, I no longer get the Internal SQL Serv...

Is this stored procedure safe from sql injection?

This stored proc executes sql with parameters using sp_executesql. Is it safe from sql injection? create procedure ExecutePeopleFilter (@lastNameFilter varchar(20), @companyNameFilter varchar(20), @ageFilter int, @dateFilter datetime) as begin declare @sql varchar(4000) declare @params varchar(1000) decla...

SQL Query Where Clause for Null OR Match (only return 1)?

I have a table that has records with a structure similar to this.. ID RoleID 1 NULL 2 15 3 16 I wrote a where clause to get records like the following SELECT * from TableX WHERE (RoleID = 2 OR RoleID IS NULL) This gets me the record of "1,NULL" But if i query SELECT * from TableX WHERE (RoleID = 15 OR RoleID I...

Complex WHERE expression using CASE-THEN in SQL 2008

I have a query: SELECT ID FROM requests WHERE date <operator> <expression> I need to change <expression> relying on my stored procedure's parameter. If flag is set: WHERE date BETWEEN GETDATE() - 7 AND GETDATE() If is not: WHERE date = date I tried to use CASE-THEN but it forces using concrete operator but I have to have few: SELE...

How can I do multiple inserts without a cursor

Lets say I have two tables tblA ( tableAID INT IDENTITY(1,1), foo VARCHAR(100)) tblB ( tableBID INTIDENTITY(1,1), tableAID INT, bar varchar(100)) tblB.tableAID is a FK to tblA. I want to insert a bunch of records (pulled from some other table in the system) into this pair of tables. I need to know what the ID from in...

SQL Server - What are best practices for maintaining indexes?

I'm reading up on indexes for optimizing database performance and would like to gather best practices for various database situations in terms of applying indexes. Take a totally non-indexed database with non-trivial amount of tables and rows, what rules do you use in determining what index is applied to what column of what type to achi...

Returning Data without Accesing a Table

The Situation (Ignore this it is boring): I have reports that I created using reporting services. Some of these reports take the parameter, "Month". They enter in an integer for the month they want. Example: December = 12. In order to view the report, I am simply using the Report Viewer in visual studio. I need the month field to be a dr...

How to save query result using T-SQL in a csv file?

How to save query result using T-SQL in a csv file in SSMS 2008? Needs to be done programmatically. ...

SQL Server, where field is int?

how can I accomplish: select * from table where column_value is int I know I can probably inner join to the system tables and type tables but I'm wondering if there's a more elegant way. Note that column_value is a varchar that "could" have an int, but not necessarily. Maybe I can just cast it and trap the error? But again, that se...

Database design preference: Using a DateTime and a BIT in SQL 2000

I need to explain this by example: Is there a best practice or preference for specifying a DateTime and BIT in a database table? In my database I have a Widget table. I need to know if a widget is "Closed" and it's "Closed Date" Business rules say that if a widget is closed, it must have a closed date. If a widget is not closed, i...

How to stop SQLServer writing extraneous stuff eg (1 rows affected)

Is there a command I can run inside my SQL script so that it stops outputing information about each operation that gets run? ie I don't want to see this: (1 row(s) affected) (1 row(s) affected) (1 row(s) affected) (Apparently this is in Experts-Exchange, but it's not accessible) ...

TSQL Stored proc argument error

If I have this stored proc definition minus the body ALTER PROCEDURE sp_AlloctionReport(@where NVARCHAR(1000), @alldate NVARCHAR(200), @alldateprevweek NVARCHAR(200)) AS And I call like this. sp_AllocationReport "ProductCode = 'FA' AND AllocationDate = '20090112' AND VenueInfo.VenueID In(SELECT vf.VenueID FROM VenueFilters vf INNER J...

SQL Query to return an item within range or nearest range

I have a table of ranges that looks like CREATE TABLE [dbo].[WeightRange]( [ID] [int] IDENTITY(1,1) NOT NULL, [Description] [nvarchar](50) NULL, [LowerBound] [decimal](18, 2) NULL, [UpperBound] [decimal](18, 2) NULL, [GroupID] [int] NULL ) Given a weight and group id I need to find the matching (or nearest) range i...

My slow T-SQL query needs a complete rethink.

Good afternoon all. I'm going to post the stored procedure in it's entire glory. Feel free to rip it to shreds. The author won't mind. DECLARE @itemTypeID INT SELECT @itemTypeID=ItemTypeID FROM dbo.ItemTypes WHERE ItemTypeName = 'Advert' BEGIN SELECT a.Active, a.ParentClass, a.Classification, a.Variant, FV."Full Views", ...

SQL Update trigger column name

Hi there, can't seem to find a way to do this anywhere. I have an SQL trigger that fires on an update. The trigger copies some details about the update to another table that someone elses program uses. Among the details passed is the name of the updated column. There a many, many, potential columns that have been updated (although only o...

Multiple Row_Number() Calls in a Single SQL Query

Hi Everyone, I'm trying to setup some data to calculate multiple medians in SQL Server 2008, but I'm having a performance problem. Right now, I'm using this pattern ([another example bottom). Yes, I'm not using a CTE, but using one won't fix the problem I'm having anyways and the performance is poor because the row_number sub-queries r...