Migrate SQLPrompt Snippets to VSCode

 I love snippets; love em. And I have a whole bunch in RedGate SQL Prompt. Now I want to be able to use those in VSCode as well, but boy do I dread having to retype all of them. Solution? Python! First arg is the path where your SQLPrompt snippets are Second arg is the directory where you want it to spit out a "sql.json" file with all your snippets. """ A script to translate sqlprompt snippet files to vscode formatted snippets """ import os import json import glob import io import argparse class SQLPromptPlaceholder :     """Represents the values of a SQLPrompt placeholder"""     def __init__ ( self , name , default_value ):         self . name = name         self . default_value = default_value class SQLPromptSnippet :     """Represents the content of a SQLPrompt snippet"""     @ staticmethod     def from_file ( filename ):         """Generates an instance fr

Ambiguous IN functionality

Here is an interesting item that ended up biting me last week. This is definitely unexpected behavior from my understanding, but I wanted to make sure others were aware to avoid the problems. This code assumes you have a tally table called dbo.numbers with a single integer column named [Num].
--Set up a Temp Table so that we can limit the information we are getting out
If OBJECT_ID('tempdb..#NUMCheck') IS NOT NULL DROP TABLE #NUMCheck
Create Table #NUMCheck
(Number Int)

--a Simple IN using a Subquery
Select top 100 * from dbo.Numbers
where Num in (select Num From #NUMCheck )

--Oops, i forgot to load anything into the Table
Insert Into #NUMCheck
(Number)
Select 2
Union all Select 3
Union all Select 5
Union all select 7
Union all Select 11


--OK, try it again
Select top 100 * from dbo.Numbers
where Num in (select Num From #NUMCheck )

--?? Oh wait, the Temp Table uses Number not Num
--and how it should be written anyway
Select top 100 * from dbo.Numbers
where Num in (select Number From #NUMCheck)

--So it looks like if the Column does not exist in the Temp table, it will just pull from the FROM table
--No Comment on that the Column does not Exist
--No Error at Run time stating that the Column does not exist in the Temp table in the Sub Query

--But what if it did have the Same Name
If OBJECT_ID('tempdb..#NUMCheck2') IS NOT NULL DROP TABLE #NUMCheck2
Create Table #NUMCheck2
(Num Int)

Insert Into #NUMCheck2
(Num)
select 7
Union all Select 11

--and try it again
Select top 100 * from dbo.Numbers
where Num in (select Num From #NUMCheck2 )
--Why would that work, shouldn't it state that it was an Ambiguous column

/*
Select top 100 * 
from dbo.Numbers a
Inner Join #NUMCheck2 b
    on Num = Num
    
--well At least that Still works    
*/    


--so a slip up of the Column name in a Sub query can run without telling you it is an Issue
--Imagine this in a Delete Statement…

Comments

Popular posts from this blog

Migrate SQLPrompt Snippets to VSCode

Left-Padding Zeroes

Fixing Git "Unexpected Disconnect while reading sideband packet"