Provera ispravnosti JMBG-a

Provera ispravnosti JMBG-a na SQL server strani :

CREATE FUNCTION fnCheckJMBG
(
@JMBG nvarchar(20)
)
RETURNS bit
AS
BEGIN

DECLARE @sum int
DECLARE @index int
DECLARE @numbers TABLE(ID int, Value int);

SET @index=1

WHILE @index <= 13
BEGIN
  INSERT INTO @numbers (ID, Value)
VALUES(@index, CAST(SUBSTRING (@JMBG, @index, 1) AS int))
           SET @index = @index + 1
END

SET @sum = (SELECT Value FROM @numbers WHERE ID=13)+
(SELECT Value FROM @numbers WHERE ID=1)*7 +
(SELECT Value FROM @numbers WHERE ID=2)*6 +
(SELECT Value FROM @numbers WHERE ID=3)*5 +
(SELECT Value FROM @numbers WHERE ID=4)*4 +
(SELECT Value FROM @numbers WHERE ID=5)*3 +
(SELECT Value FROM @numbers WHERE ID=6)*2 +
(SELECT Value FROM @numbers WHERE ID=7)*7 +
(SELECT Value FROM @numbers WHERE ID=8)*6 +
(SELECT Value FROM @numbers WHERE ID=9)*5 +
(SELECT Value FROM @numbers WHERE ID=10)*4 +
(SELECT Value FROM @numbers WHERE ID=11)*3 +
(SELECT Value FROM @numbers WHERE ID=12)*2

RETURN CASE WHEN (@sum % 11)= 0 THEN 1 ELSE 0 END

END
GO

Brisanje tabela ponekad nije lak posao

Brisanje tabela, posebno onih koje imaju puno relacija ka drugim tabelama, nije lak posao. Ovo je moje rešenje za brisanje tabele @TableName zajedno sa njenim foreign key-evima:


DECLARE @sqlCommand nvarchar(MAX), @sqlString nvarchar(MAX)

DECLARE cur CURSOR LOCAL FOR
SELECT
'ALTER TABLE ' +  OBJECT_SCHEMA_NAME(parent_object_id) +
'.[' + OBJECT_NAME(parent_object_id) +
'] DROP CONSTRAINT ' + name AS sqlString
FROM sys.foreign_keys
WHERE referenced_object_id = OBJECT_ID(@TableName)

OPEN cur

FETCH NEXT FROM cur INTO @sqlString

WHILE @@FETCH_STATUS = 0 BEGIN

EXEC (@sqlString)
FETCH NEXT FROM cur INTO @sqlString
END

CLOSE cur
DEALLOCATE cur

SET @sqlCommand = 'DROP TABLE dbo.[' + @TableName + ']'
EXEC (@sqlCommand)