this script cleans all views, SPS, functions PKs, FKs and tables. /* Drop all non-system stored procs */ DECLARE @name VARCHAR(128) DECLARE @SQL VARCHAR(254) SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = ‘P’ AND category = 0 ORDER BY [name]) WHILE @name is not null BEGIN SELECT @SQL = ‘DROP PROCEDURE … Read more
Quote, which summarizes from this article: SET is the ANSI standard for variable assignment, SELECT is not. SET can only assign one variable at a time, SELECT can make multiple assignments at once. If assigning from a query, SET can only assign a scalar value. If the query returns multiple values/rows then SET will raise … Read more
For SQL Server 2008: Convert(date, getdate()) Please refer to https://docs.microsoft.com/en-us/sql/t-sql/functions/getdate-transact-sql
LEFT(colName, 1) will also do this, also. It’s equivalent to SUBSTRING(colName, 1, 1). I like LEFT, since I find it a bit cleaner, but really, there’s no difference either way.
SELECT TOP (@count) * FROM SomeTable This will only work with SQL 2005+
No REAL easy way to do this. Lots of ideas out there, though. Best one I’ve found: SELECT table_name, LEFT(column_names , LEN(column_names )-1) AS column_names FROM information_schema.columns AS extern CROSS APPLY ( SELECT column_name + ‘,’ FROM information_schema.columns AS intern WHERE extern.table_name = intern.table_name FOR XML PATH(”) ) pre_trimmed (column_names) GROUP BY table_name, column_names; Or … Read more
float Or if you want to go old-school: real You can also use float(53), but it means the same thing as float. (“real” is equivalent to float(24), not float/float(53).) The decimal(x,y) SQL Server type is for when you want exact decimal numbers rather than floating point (which can be approximations). This is in contrast to … Read more
Same question was asked on MSDN Forums: Varchar(max) vs Varchar(255) From the original post (much more information there): When you store data to a VARCHAR(N) column, the values are physically stored in the same way. But when you store it to a VARCHAR(MAX) column, behind the screen the data is handled as a TEXT value. … Read more
you should put those two dates between single quotes like.. select Date, TotalAllowance from Calculation where EmployeeId = 1 and Date between ‘2011/02/25’ and ‘2011/02/27’ or can use select Date, TotalAllowance from Calculation where EmployeeId = 1 and Date >= ‘2011/02/25’ and Date <= ‘2011/02/27’ keep in mind that the first date is inclusive, but … Read more
You can create a table variable to hold the result set from the stored proc and then insert the output of the stored proc into the table variable, and then use the table variable exactly as you would any other table… … sql …. Declare @T Table ([column definitions here]) Insert @T Exec storedProcname params … Read more