Archive

Archive for the ‘SQL Server Questions’ Category

Bug with ISNUMERIC() – MSDN TSQL forum

July 8, 2015 2 comments

–> Question:

Has anyone seen this SQL bug before?

If you use the IsNumeric function with 12345 and replace number 3 with an “e” and no other letter, SQL still thinks it’s numeric.

If ISNUMERIC('12e45') = 1
print 'Is Numeric'
else 
Print 'No'

 

–> My Answer:

Yes, “12e45” is still numeric, notice the small ‘e’ which is a symbol for exponent for representing a big number.

But yes, NUMERIC() function does results incorrect results for some values, like:

SELECT
     ISNUMERIC('123') as '123'
    ,ISNUMERIC('.') as '.' --Period
    ,ISNUMERIC(',') as ',' --Comma

SELECT
     ISNUMERIC('123') as '123'
    ,ISNUMERIC('-') as '-'
    ,ISNUMERIC('+') as '+'
    ,ISNUMERIC('$') as '$'
    ,ISNUMERIC('\') as '\'

… gives you 1 for all these non-numeric values.

After release of SQL Server 2012 and ahead you must be using TRY_PARSE() instead of ISNUMERIC().

Check my blog post on how to use this – Use new TRY_PARSE() instead of ISNUMERIC() | SQL Server 2012
 

–> Answer by CELKO:

The use of E or e for floating point numbers started in the 1950’s with FORTRAN and later in Algol. You wrote “twelve times ten to the forty-fifth power” in the standard notation. The only difference is that some languages have to start with a digit and some can start with the E.

Please look up how floating numbers are displayed. This has been true for over 60 years! The E or e is for “exponent” in the notation that goes back to FORTRAN I.
 

–> Answer by Erland Sommarskog:

In addition to other posts, on SQL2012 or later, you can use try_convert to check if the number is convertible to a specific data type:

SELECT 
CASE WHEN try_convert(int, @str) IS NOT NULL 
       THEN 'Converts' 
       ELSE 'Not convertible' 
END

 

–> Answer by Dan Guzman:

ISNUMERIC isn’t particularly useful for the common use case of checking for a string containing only the digits 0 through 9. Consider a CASE expression instead to get the desired behavior:

CASE WHEN @value LIKE '%[^0-9]%' OR @value = '' THEN 0 ELSE 1 END

 

Ref link.


Using GO statement within a Stored Procedure – MSDN TSQL forum

June 19, 2015 Leave a comment

–> Question:

I want to include GO statements to execute a large stored procedure in batches, how can I do that?

I have an SQL statement that is broken down into various sections, as it drops a temp table, then populates it, then generates another temp table based on the first one, then drops the first temp table and so on…….it enables me to execute all the code in one go.

I end each statement/block with a GO clause, to ensure it finishes running before the next peice of code.

However, when I try to incorprate this into a stored procedure, I cannot use the GO command. If I remove all the GO lines, the sp will run, but can I be confident that it’s running how it should, and that the lines of code near the end will not begin to execute before the lines above it have?

I’ve used a semicolon at the end of each statement in the sp?
 

–> My Answer:

First of all “GO” is not a SQL statement or command, this is just a keyword that your SSMS understands, and you can even change it to anything, check here.

Secondly “GO” is a batch separator that you use in SQL Queries, and Stored Procedure is itself a batch. If you put GO within a Stored Procedure, it will be an incomplete SP, and the rest part after GO will not be a part of the SP. More on MSDN about GO, link.

Now, why do you want to use GO within an SP?

If the SP is large, then try creating multiple SP and execute them in sequence.
 

–> Another Answer by Erland:

You cannot call script files from stored procedures. As I said two years ago, SQL Server is a server application – and SQLCMD is a client.

It’s a little confusing because you seem to use DML to mean DDL. (DML is Data Manipulation Language, that is INSERT, UPDATE, DELETE and also SELECT according to some, while DDL is Data Definition Language, that is CREATE/ALTER TABLE etc.)

Yes, if you want to interleave DDL and DML, you will need to make sure that the DML is not compiled until the DDL it depends on has been executed. Three solutions:

1) Separate batches. (Don’t forget to check @@trancount in the beginning of each batch.)=
2) But most commands in EXEC().
3) Have a program (in C# or whatever) to run the whole thing.

My preference is for the latter.
 

Ref Link and this.


Reading XML data in SQL Server – MSDN TSQL forum

May 26, 2015 Leave a comment

–> Question:

I have a table with a column with data type XML.

I don’t know the contents of the XML structure etc and I need to extract it

Please suggest.
 

–> My Answer:

Check these blog posts on how to query and work with XML data in SQL:

Convert a table to XML and back to tabular format

Query XML data in tabulat format

Query Nested XML

Using APPLY operator

Read XML from a file

XML with Namespaces

Export XML to a file

All XML posts
 

Ref Link.


DDL and DML in Single Script with GO (batch separator) – MSDN TSQL forum

April 16, 2015 Leave a comment

–> Question:

Running SQL Server 2008 R2. We currently apply schema and data changes to databases (for software upgrades) using separate scripts. Sometimes, based on the changes required this results in several scripts for a single upgrade. I’d like to (if possible) be able to encapsulate all changes, DDL and DML in a single script with transaction handling, so the whole thing rolls back if there are any issues. Problem is that with DDL changes we use GO as a batch separator, which screws up the standard error handling we implement in scripts using transactions (GO results in the script unable to find the error handler).

I’m aware of the fact that we can strip out these batch separators, but what I’m not clear on is what, if any, negative impact that would have. Are there certain commands in T-SQL (executing procedures, altering objects, etc) that are required to be executed in their own batch?

Lastly, as part of our DDL and DML changes, we perform a check to verify that the impending change actually needs to be made (so for a new column we check to see whether the column exists first). If, for example we were to make a DDL change that added a column, set a default constraint on that column and updated existing rows to have a base value for that column, could we use BEGIN…END or something similar so the column existence check would only have to be performed once for that group of changes?
 

–> My Answer:

You can remove GO or batch separator if you don’t have:

1. same variables declared in more than 1 batch

2. CREATE/ALTER VIEW/PROCEDURE statements, they should be first statement in the batch otherwise will fail. (otherwise it will give you following error: CREATE/ALTER VIEW/PROCEDURE’ must be the first statement in a query batch.)

3. there could be more things, just check based upon my above 2 points.
 

Ref Link.


Table to store MS Word docs, only be accessible via SQL Queries – MSDN TSQL forum

April 15, 2015 Leave a comment

–> Question:

In Microsoft SQL Server 2012 database, I want to create a table to store Microsoft Word documents.

Documents must only be accessible via Transact-SQL queries.

How to do this?

 

–> My Answer:

As you are in SQL Server 2012 you can try using Filetables, a new feature introduced with this version. Check how to implement this in my blog post.

This features gives you an abstraction layer to store your files in File System but cannot directly navigate to the files. You will have to use SSMS to open the folders to view, add, or update the files.

Do check this link also on how to setup & do a Full Text Search on those Documents stored in the File System, link.
 

–> Another Answer by Eric:

Create a file C:\test.docx in the Server where SQL Server is hosted.

CREATE TABLE dbo.FilesTbl
(
name VARCHAR(99),
extension VARCHAR(99),
content IMAGE
)

--insert the word document into table
INSERT INTO FilesTbl 
SELECT 'test', 'docx', BULKCOLUMN 
FROM OPENROWSET (BULK N'C:\test\test.docx', SINGLE_BLOB) a;

 

Ref Link.