Tips for SQL, T-SQL, Query Analyzer and Enterprise Manager

Tips for SQL, T-SQL, Query Analyzer and Enterprise Manager
T-SQL

ISNUMERIC function does not evaluate to true/false, which can cause
error "Incorrect syntax near the keyword...". This code does not work:
IF ISNUMERIC('1') BEGIN
PRINT 'true'
END
but this works:
IF ISNUMERIC('1')=1 BEGIN
PRINT 'true'
END
Example of search-n-replace:
UPDATE sometable SET somecolumn = REPLACE(somecolumn, CHAR(9), '')
Message "The select list for the INSERT statement contains fewer items than the insert list"
may show up when a comma is missing between field names. The
COMMIT TRANSACTION may fail when there are open recordsets.
sp_depends {proc name} - shows who is calling that procedure
Use RETURN to quit a procedure at any time
Faster way to do some OR conditions:
select * from items where id in (112,214)
One of the reasons for "incorrect syntax near..." error could be missing comma
in the line before
"The column prefix ... does not match with a table name or alias name used in the query" -
could happen when you use wrong table name inside the Join, such as
Select codes cd
Join respondents resp
On cd.value = 1
Another reason for this error could be that you are using the table prefix before it
has been joined:
select data dt
join codes cd
on cd.code = ab.text <---
join abcd ab
on ab.value =1
where dt.id = 1
Common error is to omit the variable type in declare statement - easy to do after
programming in ASP, where all you do is DIM {variable_name}.
You can feed the output of one command into the other:
select * from ABC where id in (select id from XYZ x where x.NUM=13)
This will retrieve IDs of all records in XYZ where value of field NUM is 13
and then display records from ABC with matching IDs.
You can get better lookng output in Query Analyzer by setting an option Query/Results in Grid.
Also, in Grid mode you horizontal scrolling is faster when you have a lot of columns.
You cannot copy values from the output window using the standard
Ctrl-INS or Ctrl-C keys. Instead you will need to Right Click/Copy.
When debugging a stored procedure with multiple joins in Query Analyzer, replace the intial
SELECT field list with SELECT * - this way you won't have to change it when you comment out pieces
of code.
SQL moves in mysterious ways. For example, you cannot name a description field 'DESC' - apparently
because it's a keyword for 'descending'.
nvarchar stands for 'national varchar' - every character takes 2 bytes.
CHARINDEX(needle, haystack, start) - returns position of needle in haystack or 0 if not found
WHILE..BEGIN..END will be rejected by Query Analyzer if there's nothing between BEGIN and END.
When you use ODBC, it's permissible to have stuff like debug PRINT statements in stored
procedures. With OLEDB, this will break the ASP page.
Getting difference between 2 dates:
DATEDIFF(day, @testdate, getdate())
You can read the output of SELECT command sequentially:
DECLARE
@s varchar(50)

DECLARE fil SCROLL CURSOR FOR
SELECT Testdata FROM interactionhistory WHERE alarmdate is not NULL

OPEN fil

FETCH NEXT FROM fil INTO @s
-- Need to do FETCH before the loop - because value of @@fetch_status can be still set from previous time
WHILE @@fetch_status = 0 BEGIN
print @s
FETCH NEXT FROM fil INTO @s
END

CLOSE fil
DEALLOCATE fil
There are statements for breaking out of loop - BREAK and starting next iteration - CONTINUE
Single statement IF/THEN/ELSE clause:
if 1=1 print "true"
else print "false"
Counting number of records:
ELECT COUNT(*) AS 'count' FROM url_link
SQL allows you to declare variable as varchar (without specifying the size), but then it may
not work right in comparisons.
Error message "Syntax error converting the varchar value 'abc' to a column of data type int"
really means "You are trying to concatenate string with a number". To fix this, use convert(varchar, X).
Query Analyzer - after a long query finished you can see the number of rows displayed -
it's in status line, bottom right side of window.
When joining several tables, you can use 'tablename.*' instead of '*' if you want to see all
fields from one table only:
SELECT
p.name,
i.*
FROM Inter i
JOIN pro p on (p.id=i.id)
When specifying parameters for stored procedure, you need to set max length for strings, i.e. varchar(100),
otherwise length of 1 is assumed.
The following func will return value of x, or space if x is null:
IsNull(x, '')
If you want to have DROP PROCEDURE and CREATE PROCEDURE in the same file,
put 'Go' before CREATE.
You can output stuff inside stored procedure:
print "Hello, world!"
"(x row(s) affected)" is shown when query contains INSERT statement.
Message "Incorrect syntax near the keyword ..." usually means a missing colon at the
end of multi-line SELECT or DECLARE.
Cryptic error message " 'char' is not a recognized CURSOR option " can
occur when you forget to preface variable name with '@', for example:
declare X As Numeric
When generating code dynamically, enclose it in double quotes - so Query
Analyze will preserve syntax hiliting. If you use single quotes, everything inside
will show in red - as a string constant.
When 'End' in a procedure is missing, Query Analyzer will tell you
"incorrect syntax" till it's blue in a face (never admitting that it wants to
see 'End'). Sounds like bad case of denial.
Building SQL code on the fly:
declare @s nvarchar(60)
declare @x nvarchar(60)
select @x=" table1"
select @s="select *
from" + @x
Execute sp_ExecuteSQL @s
Branching:
IF ... BEGIN
..
END
ELSE .. BEGIN
..
END
If you only have ine statement per clause, you can use simplified form:
IF ..
stmt1
ELSE IF ..
stmt2
ELSE
stmt3
Variable assignment:
SELECT
@X=1,
@S='XX'

Enterprise Manager:

Ctrl-Tab switches between open E-Man windows. Same key works for QU-Zer.
Sometimes E-Man installation gets corrupted - happened to me twice in one month.
Easy way to safeguard against this - make a backup copy of C:\MSSQL7\Binn\SQL Server Enterprise Manager.MSC
and restore it when it gets lost or garbled.
After database restore you may need to close and re-open the Enterprise Man,
before you see updated data.
You can create a "diagram" - it will graphically show the relation between
tables (foreign keys and stuff). To create one, right click on database name and
pick New/Database Diagram.
The picture you get will not show what keys are related, but you can get this info
by right clicking on the key name and picking Properties.
Sometimes you have to close current query for the changes to be saved.
When creating a table, you can type several characters to select a field type
from the pulldown list.
When scripting out tables, E-Man can sit at 22% for a long time, before it
starts moving again.
When you load a table, the Page Down and other navigation keys don't work.
The quick way to get them working is to click on any column title.
Quick search allows you to enter several characters to find the table name.
However, you have to type them fast - if you pause, then E-Man will start over
and try to match next character with first letter of the table.
You can test changes in stored procedure without leaving it - click on
'Apply' and then reaload the webpage.
To quickly get name of stored procedure into the clipboard,
double click on it's name in Enterprise Manager. Once inside the proc,
double click on name again, right click and 'Copy'. This sure beats retyping
something like sp_report_list_people_recruited_on_first_monday_of_this_year.

Query Analyzer:

You can connect to different SQL servers in separate QA windows - just do File/Connect -
this will automatically open a new window.
You can have different databases open in diff windows.
Alt-D is the shortcut for "database selection" list.
Use F6 to toggle between Command and Results windows.
Bug - when you do Edit/Advanced/Make Selection Uppercase, the change cannot
be reversed with Undo.
When you do sp_help to see table structure, the field intfo is spread
out, so it may be hard to follow what's in the same row. Workaround -
scroll, so that the row you are looking at is the last one in the window.
Ctrl-O does not open file - in fact there's no shortcut for opening,
you must pick it from the menu... Alt-F-O is semi-shortcut.
Cryptic message "A SELECT statement that assigns a value to a variable
must not be combined with data-retrieval operations" will show up if you accidentally
specify data type in the assignment statement:
SELECT
@X = 1,
@Y Varchar(20)
You can have multiple windows - press Ctrl-N to start a new one.
Query Analyzer has multiple Undo levels - apply them by repeatedly press Ctrl-Z.
'execute' command is optional - you can simply type
the name of stored procedure to run it.
Use sp_help <tablename> to display table structure in Query Analyzer.
It displays field types too - scroll right to see them.