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!
...
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...
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.
...
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...
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...
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...
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...
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...
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...
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...
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 in SSMS 2008? Needs to be done programmatically.
...
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...
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...
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)
...
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...
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...
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",
...
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...
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...