none
Must declare the scalar variable "@IS_NULLABLE". RRS feed

  • Question

  • I am trying to use the following procedure to get the field type information from a table that contains said info. i am passing the definition table name, the field that i want info on and am outputting the field type info. When i execute this procedure then I get the error 'Must declare the scalar variable "@IS_NULLABLE"' on the first SET @SQL line of code. I am sure that the subsequent lines would also give this error.

     

    Does anyone have a suggestion to get rid of the error? I have tried all i can think of.

     

    Best Regards,

    Jason

     

     

    CREATE PROCEDURE [dbo].[procGetDataTableFieldType]

    -- Add the parameters for the stored procedure here

    @DefTblName VARCHAR(150),

    @DataTblFld VARCHAR(150),

    @FIELDTYPE NVARCHAR(100) OUTPUT

    AS

    DECLARE @sql NVARCHAR(4000),

    @IS_NULLABLE VARCHAR(3),

    @DATA_TYPE NVARCHAR(128),

    @CHARACTER_MAXIMUM_LENGTH VARCHAR(8),

    @NUMERIC_PRECISION VARCHAR(4),

    @NUMERIC_SCALE VARCHAR(8)

    BEGIN

    -- SET NOCOUNT ON added to prevent extra result sets from

    -- interfering with SELECT statements.

    SET NOCOUNT ON;

    -- Insert statements for procedure here

    -- GET COLUMN DEFINITION using @DataTblFld,@Origin_Table

    SET @SQL = 'SET @IS_NULLABLE = (SELECT IS_NULLABLE FROM [' + @DefTblName + ']

    WHERE [DATA TABLE FIELDS]= ''' + @DataTblFld + ''');'

    EXEC sp_executesql @sql

    SET @SQL = 'SET @DATA_TYPE = (SELECT DATA_TYPE FROM [' + @DefTblName + ']

    WHERE [DATA TABLE FIELDS]= ''' + @DataTblFld + ''');'

    EXEC sp_executesql @sql

    SET @SQL = 'SET @CHARACTER_MAXIMUM_LENGTH = (SELECT CHARACTER_MAXIMUM_LENGTH FROM [' + @DefTblName + ']

    WHERE [DATA TABLE FIELDS]= ''' + @DataTblFld + ''');'

    EXEC sp_executesql @sql

    SET @SQL = 'SET @NUMERIC_PRECISION = (SELECT NUMERIC_PRECISION FROM [' + @DefTblName + ']

    WHERE [DATA TABLE FIELDS]= ''' + @DataTblFld + ''');'

    EXEC sp_executesql @sql

    SET @SQL = 'SET @NUMERIC_SCALE = (SELECT NUMERIC_SCALE FROM [' + @DefTblName + ']

    WHERE [DATA TABLE FIELDS]= ''' + @DataTblFld + ''');'

    EXEC sp_executesql @sql

     

    -- SET FIELD TYPE BASED ON ABOVE

    IF (@DATA_TYPE = 'DECIMAL' OR @DATA_TYPE = 'NUMERIC')

    BEGIN

    IF @IS_NULLABLE='YES'

    BEGIN

    SET @FIELDTYPE=@DATA_TYPE + ' (' + @NUMERIC_PRECISION + ',' +

    @NUMERIC_SCALE + ') NULL'

    END

    ELSE

    BEGIN

    SET @FIELDTYPE=@DATA_TYPE + ' (' + @NUMERIC_PRECISION + ',' +

    @NUMERIC_SCALE + ') NOT NULL'

    END

    END

    ELSE

    BEGIN

    IF (@DATA_TYPE = 'BIGINT' OR @DATA_TYPE = 'INT' OR @DATA_TYPE = 'SMALLINT' OR @DATA_TYPE = 'TINYINT' OR

    @DATA_TYPE = 'BIT' OR @DATA_TYPE = 'MONEY' OR @DATA_TYPE = 'SMALLMONEY' OR @DATA_TYPE = 'DATETIME'

    OR @DATA_TYPE = 'SMALLDATETIME')

    BEGIN

    IF @IS_NULLABLE='YES'

    BEGIN

    SET @FIELDTYPE=@DATA_TYPE + ' NULL'

    END

    ELSE

    BEGIN

    SET @FIELDTYPE=@DATA_TYPE + ' NOT NULL'

    END

    END

    ELSE

    BEGIN

    IF (@DATA_TYPE = 'VARCHAR' OR @DATA_TYPE = 'NVARCHAR' OR @DATA_TYPE = 'CHAR' OR @DATA_TYPE = 'NCHAR' OR

    @DATA_TYPE = 'VARBINARY')

    BEGIN

    IF @IS_NULLABLE='YES'

    BEGIN

    SET @FIELDTYPE=@DATA_TYPE + '(' + @CHARACTER_MAXIMUM_LENGTH + ') NULL'

    END

    ELSE

    BEGIN

    SET @FIELDTYPE=@DATA_TYPE + '(' + @CHARACTER_MAXIMUM_LENGTH + ') NOT NULL'

    END

    END

    ELSE

    BEGIN

    SET @FIELDTYPE='VARCHAR(500) NULL'

    END

    END

    END

    END

    Tuesday, July 15, 2008 7:00 PM

Answers

  • I figured out a way to make this work. by creating a separate proc for retreiving the info. I was able to pass the query string that returned the result and the @IS_NULLABLE var. This new query executed a cursor fetch into an OUTPUT that then returned to the @IS_NULLABLE. It may be ugly but it works.

     

    See code below for solution I used. If anyone can shed light on a more efficient way please post to help others.

     

    PREVIOUS PROCEDURE NOW BECOMES ...

     

    CREATE PROCEDURE [procGetDataTableFieldType]

    -- Add the parameters for the stored procedure here

    @DefTblName VARCHAR(150),

    @DataTblFld VARCHAR(150),

    @FIELDTYPE NVARCHAR(100) OUTPUT

    AS

    DECLARE @sql NVARCHAR(4000),

    @IS_NULLABLE VARCHAR(3),

    @DATA_TYPE NVARCHAR(128),

    @CHARACTER_MAXIMUM_LENGTH INT,

    @NUMERIC_PRECISION TINYINT,

    @NUMERIC_SCALE INT

    BEGIN

    -- SET NOCOUNT ON added to prevent extra result sets from

    -- interfering with SELECT statements.

    SET NOCOUNT ON;

    -- Insert statements for procedure here

    -- GET COLUMN DEFINITION using @DataTblFld,@Origin_Table

    SET @SQL = 'SELECT IS_NULLABLE FROM [Data Management Center].dbo.[' + @DefTblName + ']

    WHERE [DATA TABLE FIELDS]= ''' + @DataTblFld + ''';'

    EXEC procGetFieldTypeComponent @sql,@ReturnVal=@IS_NULLABLE OUTPUT

    SET @SQL = 'SELECT DATA_TYPE FROM [Data Management Center].dbo.[' + @DefTblName + ']

    WHERE [DATA TABLE FIELDS]= ''' + @DataTblFld + ''';'

    EXEC procGetFieldTypeComponent @sql,@ReturnVal=@DATA_TYPE OUTPUT

    SET @SQL = 'SELECT CHARACTER_MAXIMUM_LENGTH FROM [Data Management Center].dbo.[' + @DefTblName + ']

    WHERE [DATA TABLE FIELDS]= ''' + @DataTblFld + ''';'

    EXEC procGetFieldTypeComponent @sql,@ReturnVal=@CHARACTER_MAXIMUM_LENGTH OUTPUT

    SET @SQL = 'SELECT NUMERIC_PRECISION FROM [Data Management Center].dbo.[' + @DefTblName + ']

    WHERE [DATA TABLE FIELDS]= ''' + @DataTblFld + ''';'

    EXEC procGetFieldTypeComponent @sql,@ReturnVal=@NUMERIC_PRECISION OUTPUT

    SET @SQL = 'SELECT NUMERIC_SCALE FROM [Data Management Center].dbo.[' + @DefTblName + ']

    WHERE [DATA TABLE FIELDS]= ''' + @DataTblFld + ''';'

    EXEC procGetFieldTypeComponent @sql,@ReturnVal=@NUMERIC_SCALE OUTPUT

     

     

    -- SET FIELD TYPE BASED ON ABOVE

    IF (@DATA_TYPE = 'DECIMAL' OR @DATA_TYPE = 'NUMERIC')

    BEGIN

    IF @IS_NULLABLE='YES'

    BEGIN

    SET @FIELDTYPE=@DATA_TYPE + ' (' + CAST(@NUMERIC_PRECISION AS NVARCHAR(50)) + ',' +

    CAST(@NUMERIC_SCALE AS NVARCHAR(50)) + ') NULL'

    END

    ELSE

    BEGIN

    SET @FIELDTYPE=@DATA_TYPE + ' (' + CAST(@NUMERIC_PRECISION AS NVARCHAR(50)) + ',' +

    CAST(@NUMERIC_SCALE AS NVARCHAR(50)) + ') NOT NULL'

    END

    END

    ELSE

    BEGIN

    IF (@DATA_TYPE = 'BIGINT' OR @DATA_TYPE = 'INT' OR @DATA_TYPE = 'SMALLINT' OR @DATA_TYPE = 'TINYINT' OR

    @DATA_TYPE = 'BIT' OR @DATA_TYPE = 'MONEY' OR @DATA_TYPE = 'SMALLMONEY' OR @DATA_TYPE = 'DATETIME'

    OR @DATA_TYPE = 'SMALLDATETIME')

    BEGIN

    IF @IS_NULLABLE='YES'

    BEGIN

    SET @FIELDTYPE=@DATA_TYPE + ' NULL'

    END

    ELSE

    BEGIN

    SET @FIELDTYPE=@DATA_TYPE + ' NOT NULL'

    END

    END

    ELSE

    BEGIN

    IF (@DATA_TYPE = 'VARCHAR' OR @DATA_TYPE = 'NVARCHAR' OR @DATA_TYPE = 'CHAR' OR @DATA_TYPE = 'NCHAR' OR

    @DATA_TYPE = 'VARBINARY')

    BEGIN

    IF @IS_NULLABLE='YES'

    BEGIN

    SET @FIELDTYPE=@DATA_TYPE + '(' + CAST(@CHARACTER_MAXIMUM_LENGTH AS NVARCHAR(50)) + ') NULL'

    END

    ELSE

    BEGIN

    SET @FIELDTYPE=@DATA_TYPE + '(' + CAST(@CHARACTER_MAXIMUM_LENGTH AS NVARCHAR(50)) + ') NOT NULL'

    END

    END

    ELSE

    BEGIN

    SET @FIELDTYPE='VARCHAR(500) NULL'

    END

    END

    END

    END

     

     

    NEW QUERY TO GET INFO AND RETURN IN OUTPUT VAR ...

     

    CREATE PROCEDURE procGetFieldTypeComponent

    -- Add the parameters for the stored procedure here

    @SQL NVARCHAR(4000) ,

    @ReturnVal VARCHAR(50)OUTPUT

    AS

    DECLARE @Query AS NVARCHAR(4000)

    SET @Query='DECLARE curGFTC CURSOR SCROLL SCROLL_LOCKS FOR ' + @SQL

    EXEC sp_executesql @Query

    BEGIN

    -- SET NOCOUNT ON added to prevent extra result sets from

    -- interfering with SELECT statements.

    SET NOCOUNT ON;

    -- open cursor

    OPEN curGFTC;

    -- GET NEXT ROW

    FETCH NEXT FROM curGFTC INTO @ReturnVal;

    -- CLOSE CURSOR

    CLOSE curGFTC

    DEALLOCATE curGFTC

    END

     

    Wednesday, July 16, 2008 3:58 PM

All replies

  • It is because when you call EXEC sp_executesql @sql statement, your SQL string executed in different scope from your local variable and it is not visible to your query. You will have same error for subsequent calls.

     

    Wednesday, July 16, 2008 10:01 AM
    Moderator
  • OK, I'll buy that. Any suggestions on how to fix it? What should I do different?

     

    I have tried putting the variable outside the quotes like the @DataTblFld variable. SQL server allows me to do that without the scalar variable error, but does not store the value from the table in the @IS_NULLABLE variable when I execute the stored procedure.

     

    Thx,

    JW

     

    Wednesday, July 16, 2008 2:15 PM
  • I figured out a way to make this work. by creating a separate proc for retreiving the info. I was able to pass the query string that returned the result and the @IS_NULLABLE var. This new query executed a cursor fetch into an OUTPUT that then returned to the @IS_NULLABLE. It may be ugly but it works.

     

    See code below for solution I used. If anyone can shed light on a more efficient way please post to help others.

     

    PREVIOUS PROCEDURE NOW BECOMES ...

     

    CREATE PROCEDURE [procGetDataTableFieldType]

    -- Add the parameters for the stored procedure here

    @DefTblName VARCHAR(150),

    @DataTblFld VARCHAR(150),

    @FIELDTYPE NVARCHAR(100) OUTPUT

    AS

    DECLARE @sql NVARCHAR(4000),

    @IS_NULLABLE VARCHAR(3),

    @DATA_TYPE NVARCHAR(128),

    @CHARACTER_MAXIMUM_LENGTH INT,

    @NUMERIC_PRECISION TINYINT,

    @NUMERIC_SCALE INT

    BEGIN

    -- SET NOCOUNT ON added to prevent extra result sets from

    -- interfering with SELECT statements.

    SET NOCOUNT ON;

    -- Insert statements for procedure here

    -- GET COLUMN DEFINITION using @DataTblFld,@Origin_Table

    SET @SQL = 'SELECT IS_NULLABLE FROM [Data Management Center].dbo.[' + @DefTblName + ']

    WHERE [DATA TABLE FIELDS]= ''' + @DataTblFld + ''';'

    EXEC procGetFieldTypeComponent @sql,@ReturnVal=@IS_NULLABLE OUTPUT

    SET @SQL = 'SELECT DATA_TYPE FROM [Data Management Center].dbo.[' + @DefTblName + ']

    WHERE [DATA TABLE FIELDS]= ''' + @DataTblFld + ''';'

    EXEC procGetFieldTypeComponent @sql,@ReturnVal=@DATA_TYPE OUTPUT

    SET @SQL = 'SELECT CHARACTER_MAXIMUM_LENGTH FROM [Data Management Center].dbo.[' + @DefTblName + ']

    WHERE [DATA TABLE FIELDS]= ''' + @DataTblFld + ''';'

    EXEC procGetFieldTypeComponent @sql,@ReturnVal=@CHARACTER_MAXIMUM_LENGTH OUTPUT

    SET @SQL = 'SELECT NUMERIC_PRECISION FROM [Data Management Center].dbo.[' + @DefTblName + ']

    WHERE [DATA TABLE FIELDS]= ''' + @DataTblFld + ''';'

    EXEC procGetFieldTypeComponent @sql,@ReturnVal=@NUMERIC_PRECISION OUTPUT

    SET @SQL = 'SELECT NUMERIC_SCALE FROM [Data Management Center].dbo.[' + @DefTblName + ']

    WHERE [DATA TABLE FIELDS]= ''' + @DataTblFld + ''';'

    EXEC procGetFieldTypeComponent @sql,@ReturnVal=@NUMERIC_SCALE OUTPUT

     

     

    -- SET FIELD TYPE BASED ON ABOVE

    IF (@DATA_TYPE = 'DECIMAL' OR @DATA_TYPE = 'NUMERIC')

    BEGIN

    IF @IS_NULLABLE='YES'

    BEGIN

    SET @FIELDTYPE=@DATA_TYPE + ' (' + CAST(@NUMERIC_PRECISION AS NVARCHAR(50)) + ',' +

    CAST(@NUMERIC_SCALE AS NVARCHAR(50)) + ') NULL'

    END

    ELSE

    BEGIN

    SET @FIELDTYPE=@DATA_TYPE + ' (' + CAST(@NUMERIC_PRECISION AS NVARCHAR(50)) + ',' +

    CAST(@NUMERIC_SCALE AS NVARCHAR(50)) + ') NOT NULL'

    END

    END

    ELSE

    BEGIN

    IF (@DATA_TYPE = 'BIGINT' OR @DATA_TYPE = 'INT' OR @DATA_TYPE = 'SMALLINT' OR @DATA_TYPE = 'TINYINT' OR

    @DATA_TYPE = 'BIT' OR @DATA_TYPE = 'MONEY' OR @DATA_TYPE = 'SMALLMONEY' OR @DATA_TYPE = 'DATETIME'

    OR @DATA_TYPE = 'SMALLDATETIME')

    BEGIN

    IF @IS_NULLABLE='YES'

    BEGIN

    SET @FIELDTYPE=@DATA_TYPE + ' NULL'

    END

    ELSE

    BEGIN

    SET @FIELDTYPE=@DATA_TYPE + ' NOT NULL'

    END

    END

    ELSE

    BEGIN

    IF (@DATA_TYPE = 'VARCHAR' OR @DATA_TYPE = 'NVARCHAR' OR @DATA_TYPE = 'CHAR' OR @DATA_TYPE = 'NCHAR' OR

    @DATA_TYPE = 'VARBINARY')

    BEGIN

    IF @IS_NULLABLE='YES'

    BEGIN

    SET @FIELDTYPE=@DATA_TYPE + '(' + CAST(@CHARACTER_MAXIMUM_LENGTH AS NVARCHAR(50)) + ') NULL'

    END

    ELSE

    BEGIN

    SET @FIELDTYPE=@DATA_TYPE + '(' + CAST(@CHARACTER_MAXIMUM_LENGTH AS NVARCHAR(50)) + ') NOT NULL'

    END

    END

    ELSE

    BEGIN

    SET @FIELDTYPE='VARCHAR(500) NULL'

    END

    END

    END

    END

     

     

    NEW QUERY TO GET INFO AND RETURN IN OUTPUT VAR ...

     

    CREATE PROCEDURE procGetFieldTypeComponent

    -- Add the parameters for the stored procedure here

    @SQL NVARCHAR(4000) ,

    @ReturnVal VARCHAR(50)OUTPUT

    AS

    DECLARE @Query AS NVARCHAR(4000)

    SET @Query='DECLARE curGFTC CURSOR SCROLL SCROLL_LOCKS FOR ' + @SQL

    EXEC sp_executesql @Query

    BEGIN

    -- SET NOCOUNT ON added to prevent extra result sets from

    -- interfering with SELECT statements.

    SET NOCOUNT ON;

    -- open cursor

    OPEN curGFTC;

    -- GET NEXT ROW

    FETCH NEXT FROM curGFTC INTO @ReturnVal;

    -- CLOSE CURSOR

    CLOSE curGFTC

    DEALLOCATE curGFTC

    END

     

    Wednesday, July 16, 2008 3:58 PM