Excellently Execute Store Procedure With String Query

In this tutorial post, we want to discuss how to execute store procedure with string query in Microsoft SQL Server 2008.

Dynamic SQL lets you create a query string based on user input. SQL Server allows you to create dynamic SQL statements. The statements use a SQL string varchar data type, then you execute the command. Even though the SQL value is a string, SQL Server executes the string as if it is SQL code. Transact SQL, or T-SQL provides you with the language to create the dynamic code. It retrieves data from a database and adds data into your server’s tables.


CREATE PROCEDURE [dbo].[SP_GetBillDetails]
     @WorkshopID NVARCHAR(50)=NULL,
     @Type NVARCHAR(50)=NULL,
     @SDate NVARCHAR(50)=NULL,
     @EDate NVARCHAR(50)=NULL
AS
BEGIN
SET NOCOUNT ON;
    DECLARE @ErrorState INT, -- Error State
    @ErrorMessage VARCHAR(100), -- Error message text.
    @ErrorSeverity VARCHAR(32), -- Severity of error.
    @Condition VARCHAR(100)

BEGIN TRY

    IF @TYPE=5
        SET @Condition=' J.JobCardType = 5'
    ELSE
        SET @Condition=' J.JobCardType != 5'

    EXEC('SELECT B.BillNo,B.JCNo,
          (CASE When J.JobCardType=''5'' THEN ''Counter Sale'' ELSE ''Workshop Sale'' END) AS JobCardType,
          B.LabourAmountFinal,B.PartsAmountFinal,B.FinalBillAmount,B.EntryDate,B.WorkshopID
          FROM tblCustCareBill B
          INNER JOIN tblJobCardDetail J ON B.JCNo=J.JCNo AND B.WorkshopID=J.WorkshopID
          WHERE B.WorkshopID='+@WorkshopID+' AND
          (B.EntryDate BETWEEN Convert(Date,'''+@SDate+''') AND Convert(Date,'''+@EDate+''' ))
          AND '+@Condition+' ORDER BY B.BillNo')

END TRY
BEGIN CATCH
      SET @ErrorState = ERROR_STATE();
      SET @ErrorSeverity = ERROR_SEVERITY();
      SET @ErrorMessage = CAST(ERROR_MESSAGE() as varchar(100));
RAISERROR (@ErrorMessage,@ErrorSeverity,@ErrorState );
END CATCH
END