关于SQL server动态SQL的连接语法
1. 从拼接后的SQL结果角度会更容易理解如下规则
字符型字段条件值拼接后需要保留一个单引号’,需要在拼接SQL时明确指定双单引号才可以达成一个单引号的结果, 典型语法:' and ''' + @X + '''';
而数值型条件值拼接不需要保留单引号,update后的表名连接也不需要保留单引号。
语法实例:
execute ('update '+ @TempTableName +' set iCost=iQtty * b.iIACost from ' +@TempTableName +' inner join '+ ......
set @execSql = 'SELECT a.sCardNum, .....FROM fa_Cards a with(nolock) INNER JOIN #YXKP ON a.sCardID = #YXKP.sCardID
inner join fa_deptscale c with(nolock) on a.loptid = c.loptid and a.sCardNum = c.sCardNum
INNER JOIN fa_DeprTransactions_detail b with(nolock) ON a.sCardNum = b.sCardNum and c.sDeptNum = b.sDeptNum where b.iyear = ' + @iQueryYear + ' and isnuLL(bMultiDept,0) >= ' + @bOnlyMulti
IF @sDeptNum <> ''
set @execSql = @execSql + ' and b.sDeptNum = ''' + @sDeptNum + ''''