Retrieve field data types from table in a stored p

Results 1 to 2 of 2

Thread: Retrieve field data types from table in a stored p

  1. #1
    Join Date
    Dec 1969

    Default Retrieve field data types from table in a stored p

    After reading an article on 4guysfromrolla about paging through records using a stored procedure, I wanted to implement this with a slightly extended funtionality. However, I&#039;m running into a roadblock.<BR> <BR>I am trying to allow my users to dynamically specify which fields they would like to select from the database. In order to perform the paged results, the article showed me how to create a temporary table in the store procedure. I cannot figure out the best way to dynamically create the temp table with a dynamic set of fields in the same way I do my select statement. The problem I am running into is defining the field data types of the temp table.<BR> <BR>In my stored procedure, is there a way to retrieve the field data types from the database table I am querying and set the temp table&#039;s data types to these same types?<BR> <BR>Thanks in advance for any help.<BR> <BR>Here is my stored procedure code so far:<BR> <BR>CREATE PROCEDURE dbo.sp_PagedGenericTableSelection<BR>(@Table varchar(50),<BR>@SelectionCriteria varchar(1024),<BR>@FieldSelect varchar(1024),<BR>@OrderField varchar(50),<BR>@AscOrDesc varchar(4),<BR>@Page int,<BR>@RecsPerPage int<BR>)<BR>AS<BR> <BR>-- We don&#039;t want to return the # of rows inserted<BR>-- into our temporary table, so turn NOCOUNT ON<BR>SET NOCOUNT ON<BR> <BR>-- Create a temporary table<BR>Create table #Temp<BR>(<BR>ID int IDENTITY,<BR><BR>--This part I currently have hard-coded for my testng purposes but would like to dymanically specify these fields based upon the field names passed in the @FieldSelect parameter. I need to retrieve the data types from the table I am selecting data from and create my temp table with the same data types.<BR>seller varchar(4), <BR>customer varchar(8), <BR>invoicenumber varchar(10), <BR>invoicedate varchar(8), <BR>hiddendate int, <BR>customername varchar(50), <BR>sales money<BR>)<BR> <BR>-- Insert the rows from Table into the temp table<BR>EXEC(&#039;INSERT INTO #Temp (&#039; + @FieldSelect + &#039;) Select &#039; + @FieldSelect + &#039; From &#039; + @Table + &#039; &#039; + @SelectionCriteria + &#039;<BR>Order By &#039; + @OrderField + &#039; &#039; + @AscOrDesc +&#039;&#039;)<BR> <BR>-- Find out the first and last record we want<BR>DECLARE @FirstRec int, @LastRec int<BR>SELECT @FirstRec = (@Page - 1) * @RecsPerPage<BR>SELECT @LastRec = (@Page * @RecsPerPage + 1)<BR> <BR>-- If user wants to see all records the @RecsPerPage will be -1<BR>IF @RecsPerPage = -1<BR>BEGIN<BR>-- Now, return the entire set of records<BR>SELECT *,<BR> TotalRecords = <BR> (<BR> Select Count(*) From #Temp TI<BR> )<BR>From #Temp<BR>END<BR>ELSE<BR>BEGIN<BR>-- Now, return the set of paged records, plus and indication of whether we have more records or not<BR>SELECT *,<BR> MoreRecords =<BR> (<BR> Select Count(*) From #Temp TI WHERE TI.ID &#062;= @LastRec<BR> ),<BR> TotalRecords = <BR> (<BR> Select Count(*) From #Temp TI<BR> )<BR>From #Temp WHERE ID &#062; @FirstRec AND ID &#060; @LastRec<BR>END<BR> <BR>-- Turn NOCOUNT back OFF<BR>SET NOCOUNT OFF<BR>GO<BR>

  2. #2
    Join Date
    Dec 1969

    Default sp_columns

    You might look into the sp_columns stored proc. The proc takes named arguments.<BR><BR>EXEC sp_columns @table_name = &#039;TABLENAME&#039;<BR><BR>EXEC sp_columns @table_name = &#039;TABLENAME&#039;, @column_name = &#039;COLUMNNAME&#039;<BR><BR>@table_name & @column_name are the named arguments<BR><BR>This proc returns the following fields (using one of my tables)<BR>EXEC sp_columns @table_name = &#039;PostedCharges&#039;, @column_name = &#039;ServiceDate&#039;<BR><BR>TABLE_QUALIFIER: QMSTR_Aug1; <BR>TABLE_OWNER: dbo; <BR>TABLE_NAME: PostedCharges; <BR>COLUMN_NAME: ServiceDate; <BR>DATA_TYPE: 11; <BR>TYPE_NAME: datetime; <BR>PRECISION: 23; <BR>LENGTH: 16; <BR>SCALE: 3; <BR>RADIX: ; <BR>NULLABLE: 0; <BR>REMARKS: ; <BR>COLUMN_DEF: ; <BR>SQL_DATA_TYPE: 9; <BR>SQL_DATETIME_SUB: 3; <BR>CHAR_OCTET_LENGTH: ; <BR>ORDINAL_POSITION: 5; <BR>IS_NULLABLE: NO ; <BR>SS_DATA_TYPE: 61; <BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts