SqlDbx Forum

TimD

Member

Parameterized Queries version 6.17

As an FYI, we are currently running version 6.02 Professional on MS SQL Server 17 and we're looking to upgrade to ver. 6.17 Professional.

In regards to Parameterized Queries in ver. 6.17, I've noticed a few things when defining variable data types:

CHAR    = shows as CHAR in the prompt, but data type VARCHAR in the results
VARCHAR    = shows as CHAR in the prompt, but data type VARCHAR in the results
DECIMAL    = shows nothing in the prompt, but data type NUMERIC in the results        --> if a comma is present, the input is split into multiple columns. Note: A decimal point must be present to result in a data type of 'numeric', otherwise it's an INT
NUMERIC    = shows nothing in the prompt, but data type NUMERIC in the results        --> if a comma is present, the input is split into multiple columns. Note: A decimal point must be present to result in a data type of 'numeric', otherwise it's an INT
DATE    = shows as DATE in the prompt, but data type INT in the results            --> if a comma is present, the input is split into multiple columns (no one really puts comma in dates, but it still gets split up).
INT        = shows as INT in the prompt, AND data type INT in results (success)    --> However, if a comma is present, the input is split into multiple columns

Also, if you do specify a variable data type, then how do you test for 'nothing' when nothing is inputted?

Example:
$Define My_Prompt_Value CHAR
SELECT IIF($(My_Prompt_Value) is NULL, 'Nothing', $(My_Prompt_Value)) as My_Prompt_Value    --> this fails if nothing is inputted into My_Prompt_Value

It seems that all variables with a specified data type would require input, and nothing is optional. Is this correct?

geo

Member

Re: Parameterized Queries version 6.17

I'm still on v6.02 myself, but I would suggest that you check the expanded SQL statement (put select in comments to ignore errors):
$define d  5,1  char
--  select $(d), '$(d)'
dbcc inputbuffer(@@spid)

This shows how 5,1 is being interpreted as 2 INTs.

Empty values seem to return character value NULL, so text comparison is needed:
$define x char
if '$(x)'='NULL'  print 'x is null (sort of) '
else print 'x is not null, the value is "$(x)" '

Remember that the meaning of a defined value is determined by the final statement, e.g. a value could be a list of column names to be used on a group-by.

TimD

Member

Re: Parameterized Queries version 6.17

Right, but version 6.17 behaves a little differently.

Using your example with no value defined (Thank You for the dbcc inputbuffer statement), you get this:

Example:
$Define d  char
--Select $(d), '$(d)'
dbcc inputbuffer(@@spid)

Version 6.02 = --Select NULL, 'NULL'dbcc inputbuffer(@@spid)
Version 6.17 = --Select , ''dbcc inputbuffer(@@spid)

So with 6.17, I have nothing to test...not even a NULL value if the user doesn't input some sort of value in the input prompt.

And of course the other issue is the variable data types aren't always retained from inception (query prompt) to the final results (with some exceptions).

Long story short, if I upgrade to 6.17, my current parameterized queries (i have a lot of them) won't work the same way & I'll need to do some heavy modifications to forego having to type NULL in every input prompt I'm not interested in using. Not a HUGE showstopper, but simply an inconvenient one.

Last edited by TimD (2024-02-28 11:06:23)

geo

Member

Re: Parameterized Queries version 6.17

Indeed, not compatible :-(

Not ideal, but adding single quotes in the select should work even for empty values (for char and int at least):
   select '$(d)' * 100

adapted earlier test:
   if '$(x)'=''  print 'x is empty '
   else print 'x is not empty, the value is "$(x)" '