Sending Paramaters To Dynamic SQL [Resolved]

Posted by Sharpcnet under Sql Server on 11/12/2013 | Points: 10 | Views : 1422 | Status : [Member] | Replies : 5
I'm trying to create a stored procedure for the query in http://sqlfiddle.com/#!3/9aaa3/1
The parameters are EmpId and EmpName. The query works fine when all parameters are null and also when EmpId is not null.
The problem is, when sending the EmpName. I get no results when I say
EXEC GetEmployeeLeaves null, 's'

ALTER PROCEDURE GetEmployeeLeaves
(
@EmpId uniqueidentifier = null,
@EmpName nvarchar(50) = null
)
AS
BEGIN
IF @EmpId ='+'00000000-0000-0000-0000-000000000000'+'
SET @EmpId = NULL
IF @EmpName = ''
SET @EmpName = NULL

DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX)

SELECT @cols = STUFF((SELECT DISTINCT ',' + QUOTENAME(LeaveName)
from tblLeaveType
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'')

SET @query = 'SELECT EmpId, EmpName,' + @cols + '
FROM
(
SELECT E.EmpId, E.EmpName, T.LeaveName, L.Days
FROM tblEmp E
LEFT OUTER JOIN tblEmpLeaves L ON E.EmpId = L.EmpId
LEFT OUTER JOIN tblLeaveType T ON L.TypeId = T.TypeId
WHERE
(E.EmpId = @EmpId OR @EmpId IS NULL)AND
(E.EmpName LIKE ''%@EmpName%'' OR @EmpName IS NULL)
) x
PIVOT
(
MAX(Days) FOR LeaveName IN (' + @cols + ')
) p
ORDER BY EmpId'

SET @paramDefinition = '@EmpId uniqueidentifier = null,
@EmpName nvarchar(50) = null'

execute sp_executesql @query, @paramDefinition ,@EmpId ,@EmpName
END





Responses

Posted by: Bandi on: 11/12/2013 [Member] [MVP] Platinum | Points: 50

Up
0
Down

Resolved
Refer
http://www.codeproject.com/Articles/20815/Building-Dynamic-SQL-In-a-Stored-Procedure

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

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

Posted by: vishalneeraj-24503 on: 11/12/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
Hi, Please refer below links :-

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/1b56a73b-fb0b-49c6-9fc8-44df566a13af/dynamic-sql-stored-procedure
http://www.sqlteam.com/article/using-dynamic-sql-in-stored-procedures
http://www.4guysfromrolla.com/webtech/020600-1.shtml

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

Posted by: Bandi on: 11/12/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
body of the procedure should be as follows...

--Procedure Creation
CREATE PROCEDURE GetEmployeeLeaves
(
@P_EmpId AS NVARCHAR(100) = NULL,
@P_EmpName nvarchar(50) = NULL --'%s%'
)
AS
BEGIN
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX),
@EmpId AS NVARCHAR(100) = @P_EmpId,
@EmpName nvarchar(50) = @P_EmpName

SELECT @cols = STUFF((SELECT DISTINCT ',' + QUOTENAME(LeaveName)
from tblLeaveType
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')

SET @query = 'SELECT EmpId, EmpName,' + @cols + '
FROM
(
SELECT E.EmpId, E.EmpName, T.LeaveName, L.Days
FROM tblEmp E
LEFT OUTER JOIN tblEmpLeaves L ON E.EmpId = L.EmpId
LEFT OUTER JOIN tblLeaveType T ON L.TypeId = T.TypeId
WHERE (E.EmpId = @EmpId OR @EmpId IS NULL)AND
(E.EmpName LIKE @EmpName OR @EmpName IS NULL)

) x
pivot
(
MAX(Days)
FOR LeaveName IN (' + @cols + ')
) p
ORDER BY EmpId'

--PRINT @query

DECLARE @ParameterDefinition NVARCHAR(2000) = '@EmpID NVARCHAR(100), @EmpName nvarchar(50)'
/* Execute Transact-SQL String */
EXECUTE sp_executesql @query, @ParameterDefinition, @EmpID, @EmpName
END
GO

--Execution of procedure GetEmployeeLeaves

EXEC GetEmployeeLeaves 1, '%j%'
EXEC GetEmployeeLeaves NULL, NULL
EXEC GetEmployeeLeaves 1, NULL
EXEC GetEmployeeLeaves NULL, '%s%'




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

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

Posted by: Sharpcnet on: 11/12/2013 [Member] Starter | Points: 25

Up
0
Down
Thanks a Lot. That Link worked for me and also this for sql LIKE - http://stackoverflow.com/questions/5383634/how-to-use-like-in-a-t-sql-dynamic-statement-in-a-stored-procedure

SET @search = '%' + @search + '%'

SET @SQLQuery = 'SELECT * FROM [tblApps] WHERE [firstName] LIKE @search'


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

Posted by: Bandi on: 11/12/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
Good....

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

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

Login to post response