Thursday, 23 January 2014

SP_EXECUTESQL in SQLSERVER

EXEC SP_EXECUTESQL

SET NOCOUNT ON
GO
CREATE PROC usp_matrimony(@CNTRYNAME VARCHAR(30),@STNAME VARCHAR(30)=NULL,@LASSS INT=NULL,@LASDTRANS VARCHAR(10)=NULL)
AS
BEGIN
DECLARE @QRY NVARCHAR(MAX)=''
SET @QRY='select TOP 10 PERCENT C.Name AS CNAME,C.CountyID,CityID,T.StateID,T.Name AS SNAME,T.LSAD,T.LSAD_TRANS from County C 
INNER JOIN City T ON C.StateID=T.StateID WHERE C.Name=@CNTNAME'

IF(@STNAME IS NOT NULL)
SET @QRY=@QRY+' AND T.Name=@SNAME '

IF(@LASSS IS NOT NULL)
SET @QRY=@QRY+' AND T.LSAD=@LASSS '

IF(@LASDTRANS IS NOT NULL)
SET @QRY=@QRY+' AND T.LSAD_TRANS=@LASDTRANS'
 
EXEC SP_EXECUTESQL @QRY,N'@CNTNAME VARCHAR(30),@SNAME varchar(30)',@CNTNAME=@CNTRYNAME,@SNAME=@STNAME
 
END


EXEC usp_matrimony 'Mobile','lester'

EMP, DEPT Sample script

/****** Object:  Table [dbo].[DEPT]    Script Date: 19-05-2016 06:58:37 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET A...