Create View with CTE - i.e. with as clause

Normal way of creating a view is..

create[alter] view myViewWithoutCTE as (
select * from ...)


Create view with CTE ...notice brackets use.

create[alter] view myViewWithoutCTE as
with
cte1 as (select * from),
cte2 as (select * from),
cte3 as (select * from)
select * from cte's

Notice the brackets () in red causes error when using CTE

SQL - Pad a String with leading zero and pick only 2 characters

SELECT  RIGHT(CONCAT('0', '1'), 2)

Output is 01

SELECT  RIGHT(CONCAT('0', '11'), 2)

Output is 11

SELECT concat ( RIGHT(CONCAT('0', '1'), 2),':00')

Output is 01:00

Solved - Convert varchar mm dd yyyy to dd mm yyyy - TSQL - SQL Server

Lets cast the varchar to date time first so that later we can use convert method.

@yourDate is in mm/dd/yyyy format 

select cast(@yourDate as datetime) 

eg

select cast('12/13/2017 10:10' as datetime) 

Now let's convert this to the desired date format of dd/mm/yyyy


select convert(varchar,CAST(@yourDate as datetime),103)

e.g.

select convert(varchar,CAST('12/13/2017 10:10' as datetime),103)
'

Solved! -Convert INT value to String and Check for Null

@IntValueas Integer = null

cast (ISNUll(@IntValue,'0') as varchar)

Solved - incorrect syntax near '\'- cmd /c

This is an error that is coming from SQL server and not from windows batch script.


If you are passing Parameter with special characters to your procedure make sure you separate the argument list by, (comma) and enclose it with single quotes


C:\Users\>cmd /c "G:\Scripts\bat_scriptToInvokeAStoredProcedure.bat 10/10/2017  12/10/2017 1"

content of .bat script is as follows.

"C:\Program Files\Microsoft SQL Server\120\Tools\Binn\osql"  -U informatica -P password -S DWHServerName\DWHServerName -d CDW -Q "exec p_Play_Populate_Trips '%1', '%2', '%3'"

SQL Server Convert Varchar to Datetime

SELECT CONVERT(Datetime, '2017-10-26 18:01:00', 120) -- to convert it to Datetime

SELECT CONVERT( VARCHAR(30), @date ,105) -- italian format [26-10-2017 18:01:00]
+ ' ' + SELECT CONVERT( VARCHAR(30), @date ,108 ) -- full date [with time/minutes/sec]

 Datetime variable has no format. You must convert it to a varchar if you want it to display in a certain format

Or

DECLARE @date DATETIME
SET @date = '2016-10-26 18:01:00'
select convert(varchar, @date,105) + ' ' + convert(varchar, @date,108)

Click on Below Link to go to Microsoft documentation.

https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql

sql - Calculate the difference between results of two count - subtract 2 table row counts

Try as below.

 select  (select COUNT(*) from table1) - (select COUNT(*) from table1 ) 

How to print loging/Debug a stored procedure - TSQL - SQL Server

You can use print and raiserror statments to print messages on console window in Managment studio

ALTER          PROC [dbo].[p_my_debug_proc]
@Dt as varchar(12),
@EDt as varchar(12),
@ManualRun as Integer
AS


print @Dt
print @EDt
print @ManualRun
print 'before raiserror'
raiserror('this is a raised error', 18, 1)
 print 'before return'
if (@ManualRun = 1)
       begin
                print 'in If block';
--print DATEADD(d,0,@Dt);
print DATEADD(d,0,CONVERT(datetime,@Dt,105));

end
else
print 'else block'
return 
print 'after return'


--------------------------


RAISERROR ('This is the error message,,,,', 0, 1) WITH NOWAIT;