SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
Enhancement Suggestion - Code First

 
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant View previous topic
View next topic
Enhancement Suggestion - Code First
Author Message
judahr



Joined: 09 Mar 2007
Posts: 314
Country: United States

Post Enhancement Suggestion - Code First Reply with quote
Imagine you write a query like:

Code:
Select * from dbo.Table1 where ID = @ID


Wouldn't it be awesome to hit Ctrl-Enter and your code changes to this:

Code:

Declare @ID int
Select * from dbo.Table1 where ID = @ID


Code first parameters.

Or this:

Code:
Insert @tbl
Select *  from dbo.Table1


to this:

Code:

Declare @tbl table (ID int, Column1 varchar(255))
Insert @tbl
Select *  from dbo.Table1

Thu Jan 08, 2015 3:40 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6499

Post Reply with quote
Both of that can be easily achieved using code snippets and even made more efficient. I will combine your 2 examples into 1


1. Open SQL Assistant Options dialog, click Code Snippets tab
2. Right-click Code Snippets for T-SQL Snippets box
3. Enter itbl for the snippet name (or anything else you want to use)
4. For the right side, paste the following code, don't change any other options

Code:
DECLARE @$COLUMNS(vertical,types,keys)$
DECLARE @$OBJECT(ins_object, table)$ TABLE (
   $COLUMNS(vertical)$
)

INSERT INTO @$OBJECT(ins_object, table)$
SELECT $COLUMNS(vertical)$
FROM $OBJECT(ins_schema, table)$.$OBJECT(ins_object, table)$
WHERE "AND "$COLUMNS(vertical,keys)$" = @"$COLUMNS(vertical,keys)$



Click OK

4. In the editor type itbl and then press Ctrl+Enter. That will display a prompt to select table name and then generate specific code for the selected table and its specific primary key columns.

5. Enjoy :-)
Fri Jan 09, 2015 1:07 am View user's profile Send private message
judahr



Joined: 09 Mar 2007
Posts: 314
Country: United States

Post Reply with quote
Snippets works fine if you are operating on a single table. However if you are working on a query, being able to extract the metadata and generate a table or parameters would be very useful. In fact, you could expand snippets to use a query in the window and act on it. Imagine if you had a select query and hit some key combination and it would reveal snippets that would allow you to write output based on the columns and data types from the query. You could generate temporary tables, parameter lists, even generate .net code (if someone wrote a case statement to convert data types).[/code]
Wed Jan 21, 2015 11:20 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6499

Post Reply with quote
Quote:
However if you are working on a query, being able to extract the metadata and generate a table or parameters would be very useful


you can do that too. Please see the $$..$$ macro and examples in the User's Guide. Very good examples are also available in the Tips ... forum.

Also, there is Code Generator feature that can be used for a lot of stuff.
Wed Jan 21, 2015 12:07 pm View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 1394

Post Reply with quote
judahr wrote:
Snippets works fine if you are operating on a single table. However if you are working on a query, being able to extract the metadata and generate a table or parameters would be very useful. In fact, you could expand snippets to use a query in the window and act on it. Imagine if you had a select query and hit some key combination and it would reveal snippets that would allow you to write output based on the columns and data types from the query. You could generate temporary tables, parameter lists, even generate .net code (if someone wrote a case statement to convert data types).


I posted my attempts at SELECT query to table var/temp table/CTE (SQL Server 2k8) quite a long ago. It only transforms a single select (though whatever complex it might be) into a CTE, table variable or temporary table under SQL Server. It's still very far from being complete but with some effort, it can be good starting point for developing the features you mentioned. The parameter list should be easy. .net code? No idea :)
Fri Jan 23, 2015 9:38 am View user's profile Send private message
Display posts from previous:    
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant All times are GMT - 4 Hours
Page 1 of 1

 
Jump to: 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


 

 

Powered by phpBB © 2001, 2005 phpBB Group
Design by Freestyle XL / Flowers Online.