Does this query require dynamic sql? SQL Server

I want to select the parameters of a stored procedure and then check whether each parameter is null or not in a loop.

The problem I'm having is that when I want to evaluate the variable in the IF statement, it is not being evaluated correctly. I want to evaluate each parameter I stored in the table variable. I don't know the syntax for this...or maybe it isn't even possible? Does this require Dynamic SQL? If so, when executing dynamic sql, the value of the variable will be out of scope so how do I deal with that?

I verified everything up to the IF statement works.

CREATE PROCEDURE dbo.UpdateBank
(
    @BankKey [smallint] = NULL,
    @Bank [varchar] (30) = NULL,
    @BankCode [char] (4) = NULL,
    @MasterBankCode [char] = NULL,
    @Bin [char] (6) = NULL,
    @WebSite [varchar] (50) = NULL,
    @isActive [bit] = NULL,
    @CreateDate [smalldatetime] = NULL
)
AS
SET NOCOUNT ON
SET ANSI_NULLS OFF

DECLARE @MaxRow TINYINT, @Count TINYINT
DECLARE @SPName VARCHAR (128), @CurrentRow TINYINT
SET @SPName = OBJECT_NAME(@@PROCID) -- SP self-reference to find its current name

DECLARE @SPParametersList TABLE (ID INT Identity(1,1) Primary Key,
                                 ParameterName NVARCHAR (128), 
                                 DataType NVARCHAR (128),
                                 ParameterMode NVARCHAR (10))

CREATE TABLE #TempExec(ID INT Identity(1,1) Primary Key,
                       Num BIT)


    INSERT INTO @SPParametersList (ParameterName, Datatype, ParameterMode)
        SELECT PARAMETER_NAME,DATA_TYPE,PARAMETER_MODE
        FROM INFORMATION_SCHEMA.PARAMETERS
    开发者_开发知识库    WHERE SPECIFIC_NAME = @SPName


    SET @CurrentRow = 1
    SELECT @MaxRow = ISNULL(MAX(ID),0) FROM @SPParametersList

    WHILE @CurrentRow <= @MaxRow
    BEGIN
        IF ((SELECT ParameterName FROM @SPParametersList WHERE ID = @CurrentRow) <> NULL)
        BEGIN
            SELECT 'Success' 
            SET @Count = @Count + 1 
        END
            SELECT 'Fail' 
        SET @CurrentRow = @CurrentRow + 1 

    END 

    SELECT @Count 

I always get 'Fail' when I run this stored proc

---

Change your line:

IF ((SELECT ParameterName FROM @SPParametersList WHERE ID = @CurrentRow) <> NULL)

to

IF ((SELECT ParameterName FROM @SPParametersList WHERE ID = @CurrentRow) IS NOT NULL)

You also need to initialize the @Count variable to 0:

    SET @Count = 0
    SET @CurrentRow = 1
    SELECT @MaxRow = ISNULL(MAX(ID),0) FROM @SPParametersList

---

Probably the issue is in the <> NULL which should

 IF EXISTS(SELECT ParameterName FROM @SPParametersList WHERE ID = @CurrentRow) 

but I'm not sure what you want to achieve with that piece of code...

---

I suspect that this is an example of a query that can be rewritten without the use of loops/cursors (most sql is in my experience...)

Does the query below give you your desired results?

with temp as
(
    SELECT '@BankKey' as ParamName
        UNION
        SELECT '@Bank'
)

SELECT COUNT(*) as myCount
        FROM INFORMATION_SCHEMA.PARAMETERS as isp
        LEFT JOIN temp as t
        ON t.ParamName = isp.PARAMETER_NAME
        WHERE SPECIFIC_NAME = @SPName AND t.ParamName is null

You should try to avoid using Loops/Cursors as much as possible. SQL Server (and most DBMSs in general) are excellent at performing Set based operations and terrible at performing row based operations (loops/cursors).

---

(1) "I always get 'Fail' when I run this stored proc": you have forget the ELSE branch

IF ((SELECT ParameterName FROM @SPParametersList WHERE ID = @CurrentRow) /*<>*/ IS NOT NULL)
BEGIN
    SELECT 'Success' 
    SET @Count = @Count + 1 
END
ELSE -- << here
BEGIN
    SELECT 'Fail' 
END
SET @CurrentRow = @CurrentRow + 1

(2) To count all not null parameters:

SELECT  @Count=COUNT(*)
FROM    @SPParametersList a
WHERE   a.ParameterName IS NOT NULL 

To count all null parameters:

SELECT  @Count=COUNT(*)
FROM    @SPParametersList a
WHERE   a.ParameterName IS NULL 

Note: if you want to test for NULL / NOT NULL you should use column/@variable IS [NOT] NULL operator and SET ANSI_NULLS must be ON: SET ANSI_NULLS ON.