Post reply

getting text output from stored procedure

  • June 25, 2005 at 6:30 pm

    #568461

    Steve:

    Sorry for lecturing, and Kitkat is fortunate that you share your knowledge and expertise with this site!

    I'm old school and avoid wizards like the plague. I encourage all programmers to understand the actual code which the wizards are depending on for accomplishing its output.

    Also, I ask that they consider this as their goal while using a wizard, generator, or designer; so that quick does not always replace the robust.

    kitkat: As time allows, please look into SQL system stored procedures calls for sp_help, sp_texthelp, sp_tables, sp_columns, etc...

    This is helpful in building your SQL knowledge and borrowing from its syntax. Also you can list all "column names" from any new table within your database by "table name" using:

    SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS

    WHERE TABLE_NAME='mytable'

    ORDER BY ORDINAL_POSITION

    Display all non-system "stored procedures":

    SELECT NAME

    FROM SYSOBJECTS

    WHERE XTYPE='P'

    Viewing your "sp's" with sp_helptext 'name':

    SP_HELPTEXT 'my_procedure'

    NOTE: procedure call ( to view self )

    USE MASTER;

    EXEC SP_HELPTEXT 'sp_helptext'

    Regards,

    Coach James

  • July 25, 2005 at 2:15 pm

    #576956

    Steve,

    I got the report to work in SQL reporting finally ....

    Instead of manipulating in data in reporting...i modify the calling stored procedure so that it gets AssetID in its first select statement. This is what i did

    CREATE PROCEDURE [dbo].[sp_exec_dependsonme]  @Asset varchar(50)

    AS

    begin

    set nocount on

    declare @statflag int

    set @statflag=1

    if @statflag=0  --never true, just to show AssetID field in reporting services data grid

    begin

     select AssetID from ##temp_dependsonme

    end

    if exists(select 1 from tempdb..sysobjects where name = '##temp_dependsonme' and type = 'U')

    begin

     drop table ##temp_dependsonme

    end

    create table ##temp_dependsonme (

     ID INT primary key identity,

     AssetID ntext )

    exec dbo.rec_dependsonme   @Asset

    select  AssetID from ##temp_dependsonme order by ID asc

    end

    GO

    It worked just fine in sql reports. Thanks for all your help. I did learn a good deal in this report.

    ENJOY!

    kitkat

  • July 25, 2005 at 2:18 pm

    #576959

    Does it work if you do this :

    if 1=0 --never true, just to show AssetID field in reporting services data grid

Viewing 3 posts - 16 through 17 (of 17 total)

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