Sending Paramaters To Dynamic SQL [Resolved]

Posted by Sharpcnet under Sql Server on 11/12/2013 | Points: 10 | Views : 2187 | Status : [Member] | Replies : 5

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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

[Resolved]

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

0	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

0	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

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

0	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

0	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

Latest Posts