0
  DECLARE @B INT, @Definition NVARCHAR(MAX)
   SELECT @Definition = N'SELECT @B = ' + OBJECT_DEFINITION     (default_object_id)
   from sys.columns where name='COLUMN' AND OBJECT_ID= OBJECT_ID('TABLENAME')
   EXEC SYS.SP_EXECUTESQL
   @Definition, N'@B INT OUTPUT', @B   = @B OUTPUT 
   select  @B DF;

Running this in sql server 2008 r2 is giving me the correct answer. But any other attempts to incorporate it in stored procedure or as sql text in my queries on my pages fails miserably. The most often answer I am getting is "Item cannot be found in the collection corresponding to the requested name or ordinal". Any tips will be very much appreciated Thank you

Drafter
  • 13
  • 4
  • 1
    Show your Classic ASP code, then we will talk. – user692942 Jul 20 '16 at 13:21
  • From the code you posted there is no point in using dynamic sql here. This is just a query. However your query looks a bit suspect to me. You are setting an int variable to the result of OBJECT_DEFINITION which returns a varchar(max). What is that query even trying to do? – Sean Lange Jul 20 '16 at 13:35
  • @SeanLange it isn't being applied to `@b` it's being concatenated onto the the `@definition` variable which is a `NVARCHAR(MAX)`. `@b` will become the result of the result of `OBJECT_DEFINITION (default_object_id)`. – user692942 Jul 20 '16 at 15:59
  • @Lankymart - correct. @b will get the result of OBJECT_DEFINITION. But that function returns a varchar(max) and @B is an int. – Sean Lange Jul 20 '16 at 16:01
  • @SeanLange no `@b` will be the concatenated result of `OBJECT_DEFINITION()` which depending on the column might be something like `(0)` *(for a default of 0 in the column definition)* so the query will read `SELECT @B = (0) ...` which will be classed as an `INT`. – user692942 Jul 20 '16 at 17:53
  • Unless your OBJECT_DEFINITION is not an int. For example "(getdate())". Then is fails miserably. – Sean Lange Jul 20 '16 at 18:39
  • @SeanLange true, but the OP is specifying a specific column, table combination plus they've said already it works in SSMS. My point was the `INT`, `NVARCHAR(MAX)` assumption was incorrect. – user692942 Jul 20 '16 at 18:41
  • I use the metadata information from various tables to build dynamically Add and Edit forms for the said tables. I get the Column Name, Data Type, Null-ability, Column Length (for strings) and Precision/Scale (for numeric). For numeric columns, I build select form elements and depending on the Data type, I set the upper (select-options) limits. However, the select options/range is too generic and unnecessarily large for most items. I thought I could use the Default Value and limit those ranges: if my column values are in the 2000-3000 range I do not need the smallint upper limits of 36K. – Drafter Jul 21 '16 at 03:22
  • However, getting the Default Columns Values in/from the query turned out to be not an easy (for me task). I get all other data but those default values, which I think, are stored as NVarchars refuse to show up. There is a Stackoverflow post on this subject: http://stackoverflow.com/questions/30171414/use-default-value-of-a-column-in-stored-procedures?noredirect=1&lq=1. Next step was to try getting a single default integer value just for the fun of it. Therefore, this is what the Query in my post is doing: Getting the Default Column Value for a smallint column. – Drafter Jul 21 '16 at 03:23
  • SELECT COL.COLUMN_NAME NM, COL.DATA_TYPE DT, CASE COL.IS_NULLABLE WHEN 'YES' THEN 0 ELSE 1 END MN, COL.COLUMN_DEFAULT DF, COL.CHARACTER_MAXIMUM_LENGTH MX, COL.NUMERIC_PRECISION DG, COL.NUMERIC_SCALE DC FROM INFORMATION_SCHEMA.COLUMNS COL WHERE COL.TABLE_NAME = 'TABLE' ; --------------------------------------------- this is my original query that's gets everything else in the recordset except the default values – Drafter Jul 21 '16 at 03:50
  • Sub adTinyInt Response.Write " " & objRSF.Fields(1) & "-" & objRSF.Fields(3) & "?" End Sub >>>>>>>Finally here is the vbscript sub that is building the form select element. Notice the upper limit 255 is hard coded. My intent was to have it dynamically setup by the Default Column Value which I am trying to show in the span as objRSF.Fields(3). – Drafter Jul 21 '16 at 04:03
  • 2
    @Drafter you would be better adding this supporting information to your question rather then posting it in the comments. Comments are not designed to hold large blocks of code and can be difficult to read. – user692942 Jul 21 '16 at 07:45

0 Answers0