How to generate Table Alias with Code Snippets

The [Auto Add Aliases] feature of SQL Assistant is great when I am hand-typing code and using the auto-complete feature to get aliases such as:

SELECT * FROM dbo.ItemBOM ib
SELECT * FROM dbo.SalesOrder so
SELECT * FROM dbo.SalesOrderDetail sod

However, I would like the Code Snippets feature to generate those same aliases as well, and I can't figure out how to do so.

I am using SQL Server and SQL Server Management Studio. I am using SQL Assistant 12.4 Professional.

Here is my Code Snippet:

	Code:
	CREATE PROCEDURE [dbo].[usp$OBJECT$_Get_ByID] @$COLUMNS(vertical,types,keys)$ AS BEGIN SET NOCOUNT ON; SET ARITHABORT ON; SELECT $COLUMNS(vertical)$ FROM $OBJECT$ WHERE $COLUMNS(vertical,keys)$ = @$COLUMNS(vertical,keys)$ END

Here is the desired output (when choosing the SalesOrder table, for example), that includes the dynamic alias "so" based on the table name chosen when executing this Snippet.

Code:

CREATE PROCEDURE [dbo].[uspSalesOrder_Get_ByID]
@SalesOrderID INT
AS
BEGIN
SET NOCOUNT ON;
SET ARITHABORT ON;

SELECT so.SalesOrderID,
   so.CustomerID,
   so.CarrierID,
   so.OrderDate,
   so.OrderStatus,
   so.ShipDate,
   so.SalesRepID,
   so.SRStatusID,
   so.ShipStatus,
   so.ListID,
   so.TimeCreated,
   so.TimeModified,
   so.IsActive,
   so.CreatedHUA,
   so.CreatedBy,
   so.CreatedDate,
   so.UpdatedHUA,
   so.UpdatedBy,
   so.UpdatedDate
FROM dbo.SalesOrder so
WHERE so.SalesOrderID = @SalesOrderID
END

Is there something I am missing with a use of the $OBJECT$ or $OBJECT(...)$ macro, or maybe something that can be accomplished with the $$...$$ macro to generate the Table alias? And how would I apply that alias to the $COLUMNS(...)$ output as well to generate the above output?

Thanks,

Jericho

You 're not missing anything. The macros only replace the placeholders with the text you select or auto-retrieved column names, they don't automatically add aliases. But that doesn't mean you cannot have aliases at all. Just not dynamically calculated. For example

	Code:
	CREATE PROCEDURE [dbo].[usp$OBJECT$_Get_ByID] @$COLUMNS(vertical,types,keys)$ AS BEGIN SET NOCOUNT ON; SET ARITHABORT ON; SELECT "tab."$COLUMNS(vertical)$ FROM $OBJECT$ tab WHERE "tab."$COLUMNS(vertical,keys)$ = @$COLUMNS(vertical,keys)$ END

By the way, the procedure in your example reminds me of preinstalled CRUD procedures provided with the Code Generator. Maybe you can use them?
They are accessible with SQL Assistant's DB Explorer menus, and can be customized in the Options.

Just in case, DB Explorer pane can be opened in SSMS via SQL Assistant menu, but you may get more features from it if you use in SQL Assistant's Pro SQL Editor.

With some $$ .. $$ macro trickery you can probably produce an alias... it's not perfect but at least it's not static:

	Code:
	$$ SELECT LOWER(LEFT('$OBJECT$',1) + IIF(LEN('$OBJECT$')>10,SUBSTRING('$OBJECT$',CEILING(LEN('$OBJECT$')/2),1),'') + RIGHT('$OBJECT$',1)) $$

Tweak as you like.

If the macro accepts block code something like this will extract all the upper case letters in a table name, if your naming convention is camel cased:

	Code:
	$$ DECLARE @AliasName VARCHAR(10) = '' ,@L INT ,@I INT = 1 SET @L = LEN('$OBJECT$') WHILE @I <= @L BEGIN SET @AliasName += IIF(ASCII(SUBSTRING('$OBJECT$',@I,1)) BETWEEN 65 and 90,SUBSTRING('$OBJECT$',@I,1),'') SET @I += 1 END SELECT lower(@AliasName) $$

Assuming your table naming convention is camel cased this will grab all the upper case letters to create an alias.

For the sake of brevity INSIDE the macro it might be smart to turn this into a UDF and return the UDF value in your macro after the $OBJECT$ you want to alias.

This macro works once you create the udf. I had to prefix dbo. or the macro didn't work.. YMMV

	Code:
	CREATE PROCEDURE [dbo].[usp$OBJECT$_Get_ByID] @$COLUMNS(vertical,types,keys)$ AS BEGIN SET NOCOUNT ON; SET ARITHABORT ON; SELECT $$ SELECT dbo.udf_ObjectAliaser('$OBJECT$') $$"."$COLUMNS(vertical)$ FROM $OBJECT$ $$ SELECT dbo.udf_ObjectAliaser('$OBJECT$') $$ WHERE $$ SELECT dbo.udf_ObjectAliaser('$OBJECT$') $$"."$COLUMNS(vertical,keys)$ = @$COLUMNS(vertical,keys)$ END

*Edit: It doesn't repeat the custom macro after the first insertion of the select or where clauses... hrmmm

Ok until SysOp or someone corrects me, this fixes the non-repeating of the custom macro to make an alias:

Code:

$$
DECLARE @SQL NVARCHAR(MAX) = '',
@MyAlias VARCHAR(10) = (SELECT dbo.udf_ObjectAliaser('$OBJECT$'))

SET @SQL += N'
CREATE PROCEDURE [dbo].[usp$OBJECT$_Get_ByID]
@$COLUMNS(vertical,types,keys)$
AS
BEGIN
SET NOCOUNT ON;
SET ARITHABORT ON;

SELECT "ZZZ."$COLUMNS(vertical)$
FROM $OBJECT$ ZZZ
WHERE "ZZZ."$COLUMNS(vertical,keys)$ = @$COLUMNS(vertical,keys)$
END'

SELECT Replace(@SQL, 'ZZZ', @MyAlias)
$$

Great input. Thank you so much for your examples and suggestions, I hope others will find them useful too.

	SysOp wrote:
	Great input. Thank you so much for your examples and suggestions, I hope others will find them useful too.

Thanks. Just trying to help.

I'm not even sure I went down the right path there. Is there a reason behind the $$ .. $$ macros not repeating themselves when concatenating with $COLUMNS(...)$?

Object and column macros are optimized for execution and executed by a single function. For example, you can have five $OBJECT$ macros but get just a single prompt and can select a single object. That's by design to minimize the number of interactive prompts. In comparison $$...$$ macros are executed individually, they are non interactive and they may or may not have the same code, or results

	SysOp wrote:
	Object and column macros are optimized for execution and executed by a single function. For example, you can have five $OBJECT$ macros but get just a single prompt and can select a single object. That's by design to minimize the number of interactive prompts. In comparison $$...$$ macros are executed individually, they are non interactive and they may or may not have the same code, or results

Alright, fair enough.

Hopefully the OP comes back and finds the solution provided helpful, or has found one they can share with us.