Re: need some advise

From: Keith Kratochvil (sqlguy.back2u_at_comcast.net)
Date: 08/11/04 

Date: Wed, 11 Aug 2004 09:31:08 -0500

I would opt for creating a stored procedure and executing that. This will
allow you to change the stored procedure if necessary without having to
recompile your code. Another benefit with this method is that you have the
ability to use that same sql code elsewhere (simply by calling the stored
procedure). 

-- 
Keith
"Simon Whale" <simon@noway.ukdatait.com> wrote in message
news:OhYNi46fEHA.2352@TK2MSFTNGP09.phx.gbl...
> Hi all,
>
> need some advise on the best way to do something, i have written a script
> that i want to execute from an application that i am writting using
ado.net.
>
> the script is below i know that i need to change the script to pass the
info
> back to the progam, but what should i set this script as function? stored
> procedure? or just execute the script?
>
> i would be grateful for any comments
>
> Many thanks
> Simon Whale
>
>
> ---SQL Script
>  --variables
>  declare @name varchar(50)
>  declare @out varchar(50)
>  declare @found int
>  declare @sfound varchar(2000)
>  declare @s varchar(2000)
>
>
>  set @found = 0   --sets a flag if any have been found
>  set @out = ''   --used to retrieve numerb from tables
>  set @sfound = ''
>
>  declare db cursor for
>  select name
>  from sysobjects
>  where xtype = 'u' and patindex('%[a-z][0][0-9][0-9]',name) > 0 or name in
> ('tps_ns','ctps_ns','fps_ns') or name like 'silent%'
>
>  open db
>  fetch next from db into @name
>
>  while @@Fetch_status = 0
>  begin
>   declare @sql nvarchar(2000)
>   set @sql = 'select @out = col001 from ' + @name + ' where col001 = ''' +
> @cli + ''''
>   exec sp_executesql @sql, N'@out varchar(50) output', @out output
>
>   if @out <> ''
>   begin
>    set @found = @found + 1
>    set @sfound = @sfound + @name  + ';'
>   end
>
>   fetch next from db into @name
>   set @out = ''
>  end
>
>  close db
>  deallocate db
>
>  if @found = 0
>  begin
>   set @found =  'No Matches Have Been Found'
>  end
>
> print @found
>
>
>

Relevant Pages

  • OpenStoredProcedure and Parameters
    ... I need to execute a stored procedure ... new stored procedure that inserts records into the table; ... DeleteAdHocQuery(UserName) ... DECLARE @UserName varchar ...
    (microsoft.public.access.modulesdaovba)
  • Re: Table owner in dynamic filtered merge replication
    ... parts of every script step by step. ... restores initial state of stored procedure (which is changed after ... new servers I am installing (where merge replication is used) and every ...
    (microsoft.public.sqlserver.replication)
  • Re: Problem running a 4GL program with an stored procedure
    ... EXECUTE PROCEDURE test; ... you can set a 'good' environment or just add a 'set -x' to see the errors ... script set the appropriate environment variables prior to running the ... You may want to export the stored procedure from the "old" system as ...
    (comp.databases.informix)
  • Re: Using select with passed table name
    ... EXECUTE sp_executesql @SQL_String, ... In your case, you don't use that value in the stored procedure, but you could. ... DECLARE @Parameter_Definition NVARCHAR ... declare @ktr smallint ...
    (microsoft.public.sqlserver.programming)
  • Re: Logging within User_Defined Functions (UDF)
    ... You can't perform dynamic SQL or DML in a function. ... Looks like a stored procedure that you ... Ultimately you can only execute ... > DECLARE @cmdstr nvarchar ...
    (microsoft.public.sqlserver.programming)
Loading