Snippets $COLUMN_KEYS$ bug with $COLUMNS$ in 4.8.29

Snippets $COLUMN_KEYS$ bug with $COLUMNS$ in 4.8.29

Hello,

I am trying to use the $COLUMN_KEYS$ macro along with the various $COLUMNS$ macros in a Code Snippet for T-SQL in SSMS 2005.

The following test Snippet works correctly:

	Code:
	SELECT $COLUMNS_V$ FROM $OBJECT$

The following test Snippet does not work. It will insert the Primary Key column name, but then ignores the $COLUMNS_V$ macro:

	Code:
	SELECT $COLUMN_KEYS$ $COLUMNS_V$ FROM $OBJECT$

I am using SQL Assistant 4.8.29 under Windows Vista SP1, UAC Disabled, Target is SSMS 2005.

By the way, any time I use $COLUMN_KEYS$, none of the various $COLUMNS$ macros work. So, $COLUMNS$, $COLUMNS_V$, $COLUMNS+TYPES$, $COLUMNS+TYPES_V$ are all broken.

Haven't tried the $ARGS$ set of macros.

Thanks for your help.

Hi,

Only one kind of COLUMNS_nnn macro can appear in a snippet. It can be repeated multiple times in the same snippet, but cannot be combined with other kinds of COLUMNS_nnn macro. Same for ARG_nnn.

Maybe I can help you to design the correct snippet, if I better understand what you are trying to achive.

Ok,

Thanks for the clarification. I didn't notice anything in the documentation indicating this limitation in the use of the various $COLUMN$ active macros.

Here is the snippet that I originally created:

Code:

USE [$DB$]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Created By:
-- Created Date: $DATE$
-- Description:
--
-- Modified By:
-- Modified Date:
-- Comments:
-- =============================================
CREATE PROCEDURE [dbo].[usp$OBJECT$_Update_By$COLUMN_KEYS$]
(
@$COLUMNS+TYPES_V$
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

IF @$COLUMN_KEYS$ < 1
   BEGIN
      INSERT INTO $OBJECT$
      (
         $COLUMNS_V$
      )
      VALUES
      (
         @$COLUMNS_V$
      )
      SELECT SCOPE_IDENTITY() $COLUMN_KEYS$
   END
ELSE
   BEGIN
      UPDATE $OBJECT$ SET
         $COLUMNS_V$ = @$COLUMNS_V$
      WHERE $COLUMN_KEYS$ = @$COLUMN_KEYS$

      SELECT @$COLUMN_KEYS$ $COLUMN_KEYS$
   END
END

This snippet would produce the following code when choosing a tabled named "CaseValuable" in "MyDatabase":

Code:

USE [MyDatabase]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Created By:
-- Created Date: 11/03/2009
-- Description:
--
-- Modified By:
-- Modified Date:
-- Comments:
-- =============================================
CREATE PROCEDURE [dbo].[uspCaseValuable _Update_ByValuableID]
(

@ValuableID int
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

IF @ValuableID < 1
   BEGIN
      INSERT INTO dbo.CaseValuable
      (

         ValuableID
      )
      VALUES
      (

         @ValuableID
      )
      SELECT SCOPE_IDENTITY() ValuableID
   END
ELSE
   BEGIN
      UPDATE dbo.CaseValuable SET

         ValuableID = @ValuableID
      WHERE ValuableID = @ValuableID

      SELECT @ValuableID ValuableID
   END
END

The series of tables that I created this snippet for all have a single column primary key, so what I expected was the following:

Code:

USE [MyDatabase]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Created By:
-- Created Date: 11/03/2009
-- Description:
--
-- Modified By:
-- Modified Date:
-- Comments:
-- =============================================
CREATE PROCEDURE [dbo].[uspCaseValuable_Update_ByValuableID]
(
@ClientID int,
@CaseID int,
@ValuableID int,
@ItemDesc varchar(50),
@ItemColor varchar(50),
@ItemMarkings varchar(50),
@OwnerFullName varchar(75),
@SerialNum varchar(50),
@ItemQty money,
@ItemValue money,
@TrackCustody bit,
@ReleasedTo varchar(50),
@ReleasedBy int,
@ReleasedDate datetime,
@Comment varchar(4094)
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

IF @ValuableID < 1
   BEGIN
      INSERT INTO dbo.CaseValuable
      (
         ClientID,
         CaseID,
         ValuableID,
         ItemDesc,
         ItemColor,
         ItemMarkings,
         OwnerFullName,
         SerialNum,
         ItemQty,
         ItemValue,
         TrackCustody,
         ReleasedTo,
         ReleasedBy,
         ReleasedDate,
         Comment
      )
      VALUES
      (
         @ClientID,
         @CaseID,
         @ValuableID,
         @ItemDesc,
         @ItemColor,
         @ItemMarkings,
         @OwnerFullName,
         @SerialNum,
         @ItemQty,
         @ItemValue,
         @TrackCustody,
         @ReleasedTo,
         @ReleasedBy,
         @ReleasedDate,
         @Comment
      )
      SELECT SCOPE_IDENTITY() ValuableID
   END
ELSE
   BEGIN
      UPDATE dbo.CaseValuable SET
         ClientID = @ClientID,
         CaseID = @CaseID,
         ValuableID = @ValuableID,
         ItemDesc = @ItemDesc,
         ItemColor = @ItemColor,
         ItemMarkings = @ItemMarkings,
         OwnerFullName = @OwnerFullName,
         SerialNum = @SerialNum,
         ItemQty = @ItemQty,
         ItemValue = @ItemValue,
         TrackCustody = @TrackCustody,
         ReleasedTo = @ReleasedTo,
         ReleasedBy = @ReleasedBy,
         ReleasedDate = @ReleasedDate,
         Comment = @Comment
      WHERE ValuableID = @ValuableID

      SELECT @ValuableID ValuableID
   END
END

With this output, I would only have to make a couple of minor modifications to the T-SQL for it to work.

You will also notice that the script produced an extra space after the object name:

CREATE PROCEDURE [dbo].[uspCaseValuable _Update_ByValuableID]

versus the following output when I don't use $COLUMN_KEYS$

CREATE PROCEDURE [dbo].[uspCaseValuable_Update_ByValuableID]

Thanks for any pointers you may have.

Love SQL Assistant by the way! Makes my job tons easier.

I tried several different methods and unfortunately failed to find a workaround for this limitation. From my point of view, what you are coding makes a lot of sense. Allowing and processing multiple COLUMNS_nnn macros in the same code snippet is an essential feature for automating coding of stored procedures like yours.

I submitted an enhancement request asking to review and if possible lift this limitation in future versions.

I've got a new private build with this type of limitation lifted. The build allows using multiple versions of COLUMN_nnn macro in the same code snippet. You are welcome to try it. I'm going to send you a download link from the support account.