Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2003
    Posts
    2

    Red face Unanswered: Stored Procedure(s). SOS

    Hi. I have a small stored procedure that i want to do one of the following

    a) generate and output dynamic sql that can be executed
    b) generate dynamic sql, execute it, and output a record count.

    i dont know how to do this. ive been looking around and can find nothing helpful. thanks. heres what i have now
    Code:
    create procedure dbo.gkc_Match_Count
    	@search_text		varchar(200),
    	@join_table		varchar(200),
    	@join_description	varchar(200),
    	@sSql			varchar(2000)		output
    
    
    as
    
    begin
    	set nocount on
    
    	--declare @sSql		varchar(1000)
    	
    	set @sSql = 'SELECT COUNT(*) gkc_match_count FROM ' + @join_table + ' WHERE ' + @join_description + ' LIKE ''%' + @search_text + '%'' '
    	--return( @sSql )
    
    end

  2. #2
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    Try this one (I did not test it - but it has to work):

    create procedure dbo.gkc_Match_Count
    @search_text varchar(200),
    @join_table varchar(200),
    @join_description varchar(200),
    @sSql varchar(2000) output
    as

    begin
    set nocount on
    create table #tmp(column0 varchar(8000))

    set @sSql = 'SELECT COUNT(*) gkc_match_count FROM ' + @join_table + ' WHERE ' + @join_description + ' LIKE ''%' + @search_text + '%'' '
    insert @tmp
    exec ( @sSql )
    set @sSql=column0 from #tmp

    end

  3. #3
    Join Date
    Sep 2003
    Posts
    2
    awesome. thanks man. that did the trick!

Posting Permissions

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