Use of Default keyword as the parameter default - what value is it ?

Question

Use of Default keyword as the parameter default - what value is it ?

mar.ko

Default port

Points: 1451
More actions
August 11, 2015 at 12:59 pm

#320449

@pvColumnName VARCHAR(100) = Default,
However, I am unable to determine what is the value for Default.
Is it '' ?
Default is not permitted as a constant - below fails to parse:
WHERE t2.TABLE_TYPE = 'BASE TABLE'
AND (@pvColumnName = Default OR t1.[COLUMN_NAME] Like @vColumnName)

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

sgmunson SSC Guru Points: 110640 More actions · Answer 1

mar.ko (8/11/2015)
@pvColumnName??VARCHAR(100) = Default,??
However, I am unable to determine what is the value for Default.
Is it '' ?
Default is not permitted as a constant - below fails to parse:
WHERE t2.TABLE_TYPE = 'BASE TABLE'
AND (@pvColumnName = Default OR t1.[COLUMN_NAME] Like @vColumnName)

That's because a column's default value, assuming it does indeed have one, is NOT exposed and available to choose in a query by using the word "Default". To find out what the current value of the default constraint is for a given column, you could script out the table in SSMS, and then see, but... if the default value changes; however unlikely that may be; your query isn't going to keep up. I'm not aware of any easy way, outside of dynamic SQL, to get that info and insert it into the query. If there's someone out there that knows SQL 2012 better than I do (an easy task, probably), please let me know and we can all learn something new.

If you were thinking about the default value specified for a Sproc's parameter list, that's a whole different ball of wax, but I'm pretty sure it's still not something that's exposed and available to use in a query. I think it would be a neat feature, but I would worry about the kind of bad design decisions such a feature might enable or encourage.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 2

mar.ko (8/11/2015)
@pvColumnName??VARCHAR(100) = Default,??
However, I am unable to determine what is the value for Default.
Is it '' ?
Default is not permitted as a constant - below fails to parse:
WHERE t2.TABLE_TYPE = 'BASE TABLE'
AND (@pvColumnName = Default OR t1.[COLUMN_NAME] Like @vColumnName)

Quick question, is this sniped coming from a stored procedure? Different rules apply for defaults as parameters or column values. If it is a stored procedure then skip the default keyword and just state the value

😎

GO

CREATE PROC DBO.XXX (@PVCOLUMNNAME??VARCHAR(100) = 'XX')

AS

SELECT @PVCOLUMNNAME AS X

GO

EXEC DBO.XXX;

GO

DROP PROCEDURE DBO.XXX;

sgmunson SSC Guru Points: 110640 More actions · Answer 3

mar.ko (8/11/2015)
@pvColumnName??VARCHAR(100) = Default,??
However, I am unable to determine what is the value for Default.
Is it '' ?
Default is not permitted as a constant - below fails to parse:
WHERE t2.TABLE_TYPE = 'BASE TABLE'
AND (@pvColumnName = Default OR t1.[COLUMN_NAME] Like @vColumnName)

Almost forgot: If you can script out the relevant stored procedure, you can see what the default value is, but again, just using that value doesn't get you a programmatic way to add it to your query. If you have .NET dev skills and really must have the default values, there's a link I just found that appears to have C# code that will get you what you are looking for, and could be modified to only go after one specific stored procedure. You can find it here:

http://www.codeproject.com/Articles/12939/Figure-Out-the-Default-Values-of-Stored-Procedure

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

mar.ko Default port Points: 1451 More actions · Answer 4

Thanks Guys....and yes it was a stored procedure.

I think I'll refrain from using it.....and make the default VARCHAR as NullString