SQL update query using joins sql server
UPDATE A SET foo = B . bar FROM TableA A JOIN TableB B ON A . col1 = B . colx WHERE ...
Posts
Grant EXECUTE to user for stored procedures in a schema.
GRANT EXECUTE ON schema ::< schema name > TO < user name > GRANT EXECUTE ON Sp_DateWiseItemSalesReport TO storemanager
Select query to get data And Execute SQL Statement in SQL Server
public class SQLDAL { private SqlConnection connection; public SQLDAL () { connection = new SqlConnection(GlobalConnection()); } public string GlobalConnection () { string entityConnectionString = ConfigurationManager.ConnectionStrings[ "DefaultConnection" ].ConnectionString; //string providerConnectionString = new EntityConnectionStringBuilder(entityConnectionString).ProviderConnectionString; return entityConnectionString; } #region Query Execute public Result ExecuteQuery ( string SQL) { Result oResult = new Result(); SqlCommand oCmd = null ; try { if (connection != null ) { connection.Open(); oCmd = new SqlCommand(SQL, connection); oCmd.ExecuteNonQuery(); oResu...
Return only the Date from a SQL Server DateTime
On SQL Server 2008 and higher, you should CONVERT to date: SELECT CONVERT ( date , getdate ()) On older versions, you can do the following: SELECT DATEADD ( dd , 0 , DATEDIFF ( dd , 0 , @ your_date )) for example SELECT DATEADD ( dd , 0 , DATEDIFF ( dd , 0 , GETDATE ())) gives me 2008-09-22 00 : 00 : 00.000 Links: https://stackoverflow.com/questions/113045/how-to-return-only-the-date-from-a-sql-server-datetime-datatype for example select cast ( getdate () as date )
Cast to decimal -Cast to Int -Cast to varchar -Cast to datetime-Cast to date-Cast to float - SQL Server
SELECT CAST ( 25.65 AS int); SELECT CAST ( 25.65 AS varchar); SELECT CAST ( '2017-08-25' AS datetime); SELECT CAST ( GETDATE() AS date ); SELECT CAST ( 9.5 AS decimal ( 6 , 4 )); SELECT CAST ('5.50' AS float )
Add new column CUSTOMER_ID with prefix FCL in an existing table with existing data and Increment Start from FCL0001
Add New CARD_AUTO_ID Check With select Statement Add new coloum Customer_Id Update This Customer_Id with this Statement
SQL Store Procedure To Class Wrapper
DECLARE @sql NVARCHAR(MAX) = N'EXEC ShopMenu_GermanClub.dbo.SP_ReportRM_PO_Challan ww,admin,Y;'; --SELECT name, system_type_name -- FROM sys.dm_exec_describe_first_result_set(@sql, NULL, 1) AS col declare @TableName sysname = 'SP_ReportRM_PO_Challan' declare @Result varchar(max) = 'public class ' + @TableName + ' {' select @Result = @Result + ' public ' + ColumnType + NullableSign + ' ' + ColumnName + ' { get; set; } ' from ( select replace(col.name, ' ', '_') ColumnName, case --col.system_type_name WHEN (CHARINDEX('nvarchar',system_type_name)>0) then 'string' when (system_type_name='bigint') then 'long' ...
Drop all the tables, stored procedures, triggers, constraints and all the dependencies in one sql statement
PRINT 'Dropping whole database' GO ------------------------------------------ -- Drop constraints ------------------------------------------ DECLARE @ Sql NVARCHAR ( 500 ) DECLARE @ Cursor CURSOR SET @ Cursor = CURSOR FAST_FORWARD FOR SELECT DISTINCT sql = 'ALTER TABLE [' + tc2 . CONSTRAINT_SCHEMA + '].[' + tc2 . TABLE_NAME + '] DROP [' + rc1 . CONSTRAINT_NAME + ']' FROM INFORMATION_SCHEMA . REFERENTIAL_CONSTRAINTS rc1 LEFT JOIN INFORMATION_SCHEMA . TABLE_CONSTRAINTS tc2 ON tc2 . CONSTRAINT_NAME = rc1 . CONSTRAINT_NAME OPEN @ Cursor FETCH NEXT FROM @ Cursor INTO @ Sql WHILE (@@ FETCH_STATUS = 0 ) BEGIN PRINT @ Sql Exec (@ Sql ) FETCH NEXT FROM @ Cursor INTO @ Sql END CLOSE @ Cursor DEALLOCATE @ Cursor GO ------------------------------------------ -- Drop views ------------------------------------------ DECLARE @ sql VARCHAR ( MAX ) = '' , @ crlf VARCHAR ( 2 ) = ...