Using optional parameters in procs – T-sql

22Out09

Hi everyone,
If you work with development, you’ll understand me… sometimes, your procedures must have optional params. It’s very common in languages like… VB, Java, C#, Progress ABL, and so on.
In T-Sql isn’t diff, and this is an example of optional params.
It’s just use =NULL in the param…
Proc
–DROP PROC DBO.AAA_TESTE
CREATE PROC DBO.AAA_TESTE
(@NUM1 INTEGER
,@NUM2 INTEGER
,@NUM3 INTEGER = [...]

Filed under: Sql   |  Leave a Comment
Tags: code, example, microsoft, null, optional, parameters, Procedure, Server, Sql, T-Sql

T-SQL – Finding strings in procs and triggers

22Out09

Hi all,
This post is about how to find a STRING in stored procedures and triggers.
This tip is VERY useful for those who works with T-SQL.
code
select distinct(object_name(id)) from syscomments
where
text like ‘%string%’
example
select distinct(object_name(id)) from syscomments
where
text like ‘%mytable%’
see ya

Filed under: Sql   |  Leave a Comment
Tags: example, id, Like, microsoft, mssql, object_name, Procedure, Server, Sql, Stored, String, syscomments, T-Sql, Trigger

Alternative to select count(*)

11Set09

Hi all,
Long time annnh?? lol
This post is about an alternative to select count(*) in Sql Server.
Usually the developers use it to count how many rows exists in a table.
Example
Select count(*) from users
Alternative
SELECT o.NAME,
i.rowcnt
FROM sysindexes AS i
INNER JOIN sysobjects AS o ON i.id = o.id
WHERE i.indid < 2
AND o.NAME = ‘users’
To improve performance…
See u…

Filed under: Sql   |  Leave a Comment
Tags: alternative, code, count, example, mssql, Performance, Server, Sql, sysindexes, sysobjects, T-Sql, tuning

Working with XML in SQL Server 2000

12Ago09

Working with XML in SQL Server 2000
Unfortunetly sometimes we have to work with old technologies (like mssql 2000).
So, this tip is about how to generate a xml from a select statment.
The big deal is FOR XML clause.
– Basic way to generate the xml
SELECT name
,salary
FROM employe
FOR XML AUTO
– Generates with elements
SELECT name
,salary
FROM employe
FOR XML AUTO, ELEMENTS
– [...]

Filed under: Sql   |  Leave a Comment
Tags: 2000, clause, code, example, for, mssql, Server, Sql, xml

Converting seconds – SQL

16Jun09

For those works with many databases… mssql, oracle, sybase, progress sometimes has troubles with migrations.
The Date/Time types sometimes are a big problem.
This tip is how to convert seconds.
– Convert date to seconds
select   (datepart(hour,getdate()) * 3600)
+ (datepart(minute,getdate()) * 60)
+ (datepart(second,getdate()))
– Convert seconds to HH:MM:SS
select convert(varchar,44235/3600)
+ “:”
+ convert(varchar,(44235 % 3600) / 60)
+ “:”
+ convert(varchar,44235 % [...]

Filed under: Sql   |  Leave a Comment
Tags: ase, Convert, Date, datepart, example, hh:mm:ss, hour, microsoft, minute, mssql, second, Server, Sql, Sybase, T-Sql, time

Encrypting a view – SQL

16Jun09

Sometimes we need to hide some information of a database.
Here is a tip to encrypt a description of a view.
– Creating a table
create table MYTABLE
(
mycode  int
,myname  varchar(50)
,mysal   float
)
– Inserting values
insert into MYTABLE values (1,’sl4v3r’,6000)
insert into MYTABLE values (2,’james’,7000)
insert into MYTABLE values (3,’bond’,8000)
– Select the table
select *
from MYTABLE
1             sl4v3r    6000.0
2             james   7000.0
3             bond     [...]

Filed under: Sql   |  Leave a Comment
Tags: encrypt, ENCRYPTION, example, microsoft, mssql, Server, Sql, T-Sql, view, with

Errors in SQL Server

09Jun09

If you had worked with Sql certainly you saw the error message below.
Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint ‘PK_MYTABLE’. Cannot insert duplicate key in object ‘MYTABLE’.
The statement has been terminated.
The message has five components:
Msg
Each error message has a number starting with 0.
Message numbers from 50001 and up are [...]

Filed under: Sql   |  Leave a Comment
Tags: error, example, level, line, msg, mssql, Server, Sql, state, sysmessages, T-Sql

Use JOIN or WHERE to join tables?

09Jun09

There are two methods…
WHERE
SELECT MYTABLE.Name, MYTABLE2.Age
FROM MYTABLE, MYTABLE2
WHERE MYTABLE.Id = MYTABLE2.Id
JOIN
SELECT MYTABLE.Name, MYTABLE2.Age
FROM MYTABLE
INNER JOIN MYTABLE2
ON MYTABLE.Id = MYTABLE2.Id
The best performance is JOIN
SELECT MYTABLE.Name, MYTABLE2.Age
FROM MYTABLE
INNER JOIN MYTABLE2
ON MYTABLE.Id = MYTABLE2.Id
source: http://www.tek-tips.com/faqs.cfm?fid=5168
There are two methods…
WHERE
SELECT MYTABLE.Name, MYTABLE2.Age
FROM MYTABLE, MYTABLE2
WHERE MYTABLE.Id = MYTABLE2.Id
JOIN
SELECT MYTABLE.Name, MYTABLE2.Age
FROM MYTABLE
INNER JOIN MYTABLE2
ON MYTABLE.Id = MYTABLE2.Id
The best performance is [...]

Filed under: Sql   |  Leave a Comment
Tags: ase, best, example, join, mssql, on, Performance, Server, Sql, Sybase, T-Sql, Where

Using TOP with SELECT Statements

09Jun09

Hi All,
I’m back… Long time anh???… lol
The TOP option is used for limiting the output of a query result set.
– Syntax for returning n number of rows:
SELECT TOP n *
FROM table
– Example for returning 10 rows
SELECT TOP 10 *
FROM mytable
– Syntax for returning n percentage of rows:
SELECT TOP n PERCENT *
FROM table
– [...]

Filed under: Sql   |  Leave a Comment
Tags: ase, mssql, option, percent, sample, Server, Sql, Sybase, T-Sql, top

Compute clause in SQL

26Mai09

Hi,
One of my favorites languages is SQL.
Almost six years working with it… MSSQL, Oracle, Mysql, Progress doesn’t matter which.
The tip of today is how to use compute clause.
It’s very common somebody ask you the sum of a column in a select.
The compute clause might help you…
Sample
select name
,sal
from mysq
compute sum(sal)
In this sample… in the end [...]

Filed under: Sql   |  Leave a Comment
Tags: agregate, ase, clause, code, compute, sample, Server, Sql, sum, Sybase, T-Sql