Error in while concatenating in sql query

Posted by Klbaiju under Sql Server on 1/15/2015 | Points: 10 | Views : 401 | Status : [Member] | Replies : 3
hi
following is a working sql query

declare @dte as datetime ='2015-01-01'
declare @StDt as Datetime = DATEADD(mm,DATEDIFF(mm,0,@dte), 0)
declare @EnDt as datetime = DATEADD( DD, -1, DATEADD(mm,DATEDIFF(mm,0,@dte) + 1, 0));
DECLARE @query AS NVARCHAR(MAX);

;WITH Dates AS(
SELECT DATEADD( DD, ROW_NUMBER() OVER( ORDER BY (SELECT NULL)) - 1, @StDt) Month_date
FROM (VALUES(0),(0),(0),(0),(0),(0))E(N),
(VALUES(0),(0),(0),(0),(0),(0))E2(N)
)
SELECT @Query = 'SELECT m.empno ' + CHAR(13)
+ (SELECT CHAR(9) + ',MAX( CASE WHEN sdate = ''' + CONVERT( CHAR(8), Month_date, 112) + ''' THEN status END) AS ' + QUOTENAME( CONVERT( char(2), Month_date, 103)) + CHAR(13)
FROM Dates
WHERE Month_date BETWEEN '2015-01-01' AND '2015-01-15'
ORDER BY Month_date
FOR XML PATH(''),TYPE).value('.','varchar(max)')
+ '
FROM employee m
LEFT
JOIN shiftdetails b ON m.empno = b.empno
GROUP BY m.empno '


EXEC sp_executesql @Query


my requirement is i want to make a change in the following line

WHERE Month_date BETWEEN '2015-01-01' AND '2015-01-15'

i want to change the above line as

WHERE Month_date BETWEEN '"+stdate+"' AND '"+endate+"'

when i try this code it showed error.
next requirement is
FROM employee m
LEFT
JOIN shiftdetails b ON m.empno = b.empno
GROUP BY m.empno '
here i want to add one where condition as
FROM employee m
LEFT
JOIN shiftdetails b ON m.empno = b.empno where b.empno='"+empno+"' and b.shifttype='MS'
GROUP BY m.empno '

how it is possible




Responses

Posted by: Bandi on: 1/16/2015 [Member] [MVP] Platinum | Points: 25

Up
0
Down
Hi,

You have to use single quotes twice instead of double quotes...

WHERE Month_date BETWEEN '''+@StDt+''' AND '''+@EnDt+'''


Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Klbaiju, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Klbaiju on: 1/16/2015 [Member] Starter | Points: 25

Up
0
Down
Hi bandi,
your code will work if it is in storedprocedure.
following is complete query
string endate = Request.QueryString["stdate"];
string stdate=Request.QueryString["endate"];
SqlConnection con = new SqlConnection(@"Data Source=.\sqlserver;Initial Catalog=TechStreet;Integrated Security=True");

string strMsg = @"declare @dte as datetime ='2015-01-01'
declare @StDt as Datetime = DATEADD(mm,DATEDIFF(mm,0,@dte), 0)
declare @EnDt as datetime = DATEADD( DD, -1, DATEADD(mm,DATEDIFF(mm,0,@dte) + 1, 0));
DECLARE @query AS NVARCHAR(MAX);

;WITH Dates AS(
SELECT DATEADD( DD, ROW_NUMBER() OVER( ORDER BY (SELECT NULL)) - 1, @StDt) Month_date
FROM (VALUES(0),(0),(0),(0),(0),(0))E(N),
(VALUES(0),(0),(0),(0),(0),(0))E2(N)
)
SELECT @Query = 'SELECT m.empno ' + CHAR(13)
+ (SELECT CHAR(9) + ',MAX( CASE WHEN sdate = ''' + CONVERT( CHAR(8), Month_date, 112) + ''' THEN status END) AS ' + QUOTENAME( CONVERT( char(2), Month_date, 103)) + CHAR(13)
FROM Dates
WHERE Month_date BETWEEN '''+stdate+''' AND '''+endate+'''
ORDER BY Month_date
FOR XML PATH(''),TYPE).value('.','varchar(max)')
+ '
FROM employee m
LEFT
JOIN shiftdetails b ON m.empno = b.empno
GROUP BY m.empno'


EXEC sp_executesql @Query";

Klbaiju, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 1/16/2015 [Member] [MVP] Platinum | Points: 25

Up
0
Down
Can you post the exact error message which you got ?




Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Klbaiju, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response