Hot answers tagged t-sql
6
No, there is no way to tell SQL Server to treat all NULL float values as zero. You will have to surround these expressions with ISNULL() or, better yet IMHO, COALESCE(). You can do this in a view so you don't have to repeat it in every query.
6
This can be done using the PIVOT function, but since it sounds like you want to change the query based on the schemaId, then you will want to use dynamic SQL.
If you had a known number of values or knew the columns for a specific schemaID, then you could hard-coded the query. A static query would be:
select loannumber,
[First Name],
[Middle Name],
...
4
I skipped the code example, and jumped to what seems to be the real question here
On top of that, a manual inspection of the index in question shows no text, ntext, image, xml or varchar(MAX), nvarchar(MAX) or varbinary(MAX). Could there be something I'm missing here?
For the record, this is a clustered index.
You certainly are missing something, ...
4
All output of the case expression must be of the same datatype.
If maintaining the XML datatype for the valid XML data is worth a little manipulation of the invalid data, then one workaround could be to wrap the invalid data up like a declaration before casting it to XML:
cast('<?invalid ' + RunLog + ' ?>' as xml)
Also, you shouldn't need to use ...
3
This might not be the best approach to dealing for your problem, but it will certainly achieve the stated goal.
ALTER TRIGGER [dbo].[Trg_ProjectCreation] ON [dbo].[Projects]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
declare @TableName sysname
select @tablename = object_schema_name(parent_id) + '.' + object_name(parent_id)
from ...
3
I have a similar query showing a TON of encapsulating ISNULL statements. I need the null values to represent items that have not been touched (as many columns required distinct identifiers for alterations). I tried a million different things to get around it and in the end just ended up with an annoyingly long code laced with a bunch of ISNULLs. The ...
3
The most efficient way according to my own testing is:
*Remark: only valid for NON-NULLABLE columns, as commented by Aaron.
CREATE FUNCTION [dbo].[DateRange]
(
@StartDate date,
@EndDate date,
@Location varchar(25),
@Device varchar(25)
)
RETURNS TABLE
AS
RETURN
(
SELECT *
FROM MyTable
WHERE Date < @EndDate
AND Date ...
3
Like so :
CREATE FUNCTION [dbo].[DateRange]
(
@StartDate date,
@EndDate date,
@Location varchar(25) = NULL,
@Device varchar(25) = NULL
)
RETURNS TABLE
AS
RETURN
(
SELECT *
FROM MyTable
WHERE
Date < @EndDate
AND Date > @StartDate
AND ( ...
3
You can use this pattern. For more schema fields, just expand on the 2nd to last line with all the schema field names.
select *
from (
select l.LoanNumber, s.FieldName, f.FieldValue
from Loans l
join Schemafields s on s.SchemaId = l.SchemaId
join LoanFields f on f.LoanId = l.Id and f.SchemaFieldId = s.Id) p
pivot (
...
2
Use an OR to check if either the parameter matches the value, or both the parameter and the value are NULL, like this:
WHERE
(
[TBL_OUTBOUND_REVIEW].[ERROR_FREE] = @Status
OR
([TBL_OUTBOUND_REVIEW].[ERROR_FREE] IS NULL AND @Status IS NULL)
)
You could also use a CASE statement as your IF if you really wanted to, but I think it's easier to ...
1
There are many free tools - native and opensource available for comparing schema between 2 databases:
Open DBDiff ==> This is on Codeplex and works great.
SQL Admin Studio ==> This is now a free tool.
Hidden Gem from SQL 2005 and up : tablediff.exe (you can find this in the COM directory of your SQL Server install folder) Compare schemas: Regular or ...
1
The way you're doing it is about the fastest, easiest and most comfortable way you're going to find to pass a TVP to a stored procedure, sorry. You can't pass expressions to stored procedure parameters, even simple things like:
EXEC sp_who2 N'act'+'ive';
Fails with:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '+'.
In order to use an ...
1
Alright, found a way to do the same with Table Valued Types
CREATE FUNCTION [dbo].[DateRange]
(
@StartDate date,
@EndDate date,
@Location TVT.Location ReadOnly,
@Device TVT.Device ReadOnly
)
RETURNS TABLE
AS
RETURN
(
SELECT *
FROM MyTable
WHERE Date < @EndDate
...
Only top voted, non community-wiki answers of a minimum length are eligible
