Create a role add this role to users, and then you can grant execute to all the routines in one shot to this role. CREATE ROLE <abc> GRANT EXECUTE TO <abc> EDIT This works in SQL Server 2005, I’m not sure about backward compatibility of this feature, I’m sure anything later than 2005 should be … Read more
SELECT DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0) AS StartOfYear, DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) + 1, -1) AS EndOfYear The above query gives a datetime value for midnight at the beginning of December 31. This is about 24 hours short of the last moment of the year. If you want to include time that might occur on … Read more
First of all, it’s just bad practice. Input validation is always necessary, but it’s also always iffy. Worse yet, blacklist validation is always problematic, it’s much better to explicitly and strictly define what values/formats you accept. Admittedly, this is not always possible – but to some extent it must always be done. Some research papers … Read more
Straightforward way? No, but I’ve used the reverse. Literally. In prior routines, to find the last occurence of a given string, I used the REVERSE() function, followed CHARINDEX, followed again by REVERSE to restore the original order. For instance: SELECT mf.name ,mf.physical_name ,reverse(left(reverse(physical_name), charindex(‘\’, reverse(physical_name)) -1)) from sys.master_files mf shows how to extract the actual … Read more
Take a look at SQL Server – Set based random numbers which has a very detailed explanation. To summarize, the following code generates a random number between 0 and 13 inclusive with a uniform distribution: ABS(CHECKSUM(NewId())) % 14 To change your range, just change the number at the end of the expression. Be extra careful … Read more
SELECT Orders.OrderNumber, LineItems.Quantity, LineItems.Description FROM Orders JOIN LineItems ON LineItems.LineItemGUID = ( SELECT TOP 1 LineItemGUID FROM LineItems WHERE OrderID = Orders.OrderID ) In SQL Server 2005 and above, you could just replace INNER JOIN with CROSS APPLY: SELECT Orders.OrderNumber, LineItems2.Quantity, LineItems2.Description FROM Orders CROSS APPLY ( SELECT TOP 1 LineItems.Quantity, LineItems.Description FROM LineItems WHERE … Read more
For queries like this it is always best to use an INFORMATION_SCHEMA view. These views are (mostly) standard across many different databases and rarely change from version to version. To check if a table exists use: IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ‘TheSchema’ AND TABLE_NAME = ‘TheTable’)) BEGIN –Do Stuff END
Syntax: ALTER TABLE {TABLENAME} ADD {COLUMNNAME} {TYPE} {NULL|NOT NULL} CONSTRAINT {CONSTRAINT_NAME} DEFAULT {DEFAULT_VALUE} WITH VALUES Example: ALTER TABLE SomeTable ADD SomeCol Bit NULL –Or NOT NULL. CONSTRAINT D_SomeTable_SomeCol –When Omitted a Default-Constraint Name is autogenerated. DEFAULT (0)–Optional Default-Constraint. WITH VALUES –Add if Column is Nullable and you want the Default Value for Existing Records. Notes: … Read more