.

Dynamic SQL

simple example of using dynamic SQL:

USE [RRS]
GO
/****** Object:  StoredProcedure [dbo].[GetSerologicalAssaysResultsEmpty]    Script Date: 09/17/2009 13:44:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        pete celliers
-- Create date: 17/9/09
-- Description:    
-- =============================================
alter PROCEDURE [dbo].[GetSerologicalAssayResultDetails]
@LabID int = null,
@ReagentID int = null,
@assaytypeid int = null,
@assayid int = null,
@assaylotid int = null,
@Date datetime = null
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

--determins if we need to add the and logical operator
DECLARE @andbit    bit
select @andbit = 0

DECLARE @sql    nvarchar(4000)
SELECT @sql =   N' SELECT SerologicalAssayResultDetails.*
FROM  LaboratoryReagents INNER JOIN
               Laboratories ON LaboratoryReagents.LaboratoryID = Laboratories.LaboratoryID INNER JOIN
               Reagents ON LaboratoryReagents.ReagentID = Reagents.ReagentID INNER JOIN
               SerologicalAssayResults ON Laboratories.LaboratoryID = SerologicalAssayResults.LaboratoryID INNER JOIN
               SerologicalAssayResultDetails ON SerologicalAssayResults.SerologicalAssayResultID = SerologicalAssayResultDetails.SerologicalAssayResultID INNER JOIN
               SerologicalAssayLots ON SerologicalAssayResults.SerologicalAssayLotID = SerologicalAssayLots.SerologicalAssayLotID INNER JOIN
               SerologicalAssays ON SerologicalAssayLots.SerologicalAssayID = SerologicalAssays.SerologicalAssayID INNER JOIN
               AssayTypes ON SerologicalAssays.AssayTypeID = AssayTypes.AssayTypeID '

if    @LabID is not null
begin
    if @andbit = 1
        select @sql = @sql  +   N' and '
    else
        select @sql = @sql  +   N' where '
    select @sql = @sql  +   N' Laboratories.LaboratoryID = ' + convert(varchar, @LabID)
    set @andbit = 1
end

if    @ReagentID is not null
begin
    if @andbit = 1
        select @sql = @sql  +   N' and '
    else
        select @sql = @sql  +   N' where '
    select @sql = @sql  +   N' Reagents.ReagentID = ' + convert(varchar, @ReagentID)
    set @andbit = 1
end

if    @assaytypeid is not null
begin
    if @andbit = 1
        select @sql = @sql  +   N' and '
    else
        select @sql = @sql  +   N' where '
    select @sql = @sql  +   N' AssayTypes.AssayTypeID = ' + convert(varchar, @assaytypeid)
    set @andbit = 1
end

if    @assayid is not null
begin
    if @andbit = 1
        select @sql = @sql  +   N' and '
    else
        select @sql = @sql  +   N' where '
    select @sql = @sql  +   N' SerologicalAssays.SerologicalAssayID = ' + convert(varchar, @assayid)
    set @andbit = 1
end

if    @assaylotid is not null
begin
    if @andbit = 1
        select @sql = @sql  +   N' and '
    else
        select @sql = @sql  +   N' where '
    select @sql = @sql  +   N' SerologicalAssayLots.SerologicalAssayLotID = ' + convert(varchar, @assaylotid)
    set @andbit = 1
end

if    @Date is not null
begin
    if @andbit = 1
        select @sql = @sql  +   N' and '
    else
        select @sql = @sql  +   N' where '
    select @sql = @sql  +   N' SerologicalAssayResults.DateEntered = ' + convert(varchar, @Date)
    set @andbit = 1
end





   EXEC sp_executesql @sql OUTPUT

END

————–

run as follows:

exec [GetSerologicalAssayResultDetails] @LabID=5

be aware of SQL Injection (security risk) that would normally be exposed when dealing with dynamic SQL

 

What's your thoughts on this?

*

Protected by WP Anti Spam