[X]My Assistant

[mr.siro]

Hello guys, i have app with function check update, after check user will click button "update" and run script which i want.
Now, i wish to find a way to run this script with hide mode (user can't see content of script, just show form "Loadding" in screen of user).
Somebody can tell me how to do that. Thanks.
This post has been edited by mr.siro: Oct 16 2019, 03:16 AM

[GroverParkGeorge]

Unfortunately, it's not clear where or how this "script" needs to run. Please explain more fully.

Typically, scripts run without the user seeing the contents of them, so I'm not sure what the context is here.

[MadPiet]

use a pass-through query and use something like

EXEC <schema>.StoredProcedureName;

If it has parameters, you'll have to append them to the SQL string. If you have a dummy pass-through query set up, you can build the execute statement in VBA and then run it.

[mr.siro]

hello guys, i want to know, how to alter StoreProcedure with the part what i want. Example:

CODE

procedure test
@variable nvarchar(5)
as
begin
   if @variable = '0'
   begin select * from dbo.table1 end
   if @variable = '1'
   begin select * from dbo.table2 end
end

Now, i want alter that SP, i write :

CODE

alter procedure test
@variable nvarchar(5)
as
begin
   if @variable = '0'
   begin select * from dbo.table1 end
   if @variable = '1'
   begin select * from dbo.table2 end
   if @variable = '3'
   begin select * from dbo.table3 end
end

I want to know, have a way to add part :

CODE

if @variable = '3'
   begin select * from dbo.table3 end

without write code alter whole SP.

[cheekybuddha]

Perhaps something like:

CODE

CREATE PROCEDURE test
  @variable            NVARCHAR(5)
AS
BEGIN

  DECLARE @SQL       NVARCHAR(500);

  SET @SQL = CASE @variable
    WHEN '0' THEN 'table1'
    WHEN '1' THEN 'table2'
    WHEN '3' THEN 'table3'
  END;

  IF @SQL IS NOT NULL
  BEGIN
    SET@SQL = 'SELECT * FROM ' + @SQL + ';';
    EXECUTE(@SQL);
  END

END

(untested)

hth,

d

[mr.siro]

i mean, how to add part @variable = 3 in to SP test which not need to write code alter whole SP

[cheekybuddha]

If I understand you correctly then I'm not sure how to do what you want.

You can write the original procedure to accept a table name as a parameter, and then just make sure you pass the correct table name:

CODE

CREATE PROCEDURE test
  @tbl            NVARCHAR(50)
AS
BEGIN

  DECLARE @SQL       NVARCHAR(500);

  SET@SQL = 'SELECT * FROM ' + @tbl + ';';
  EXECUTE(@SQL);

END

So, you can call:

CODE

EXEC test 'table3';

But then you might just as well pass the whole SELECT statement and not bother with a procedure at all!

[mr.siro]

no, this is example, the point that i want to know how to alter a procedure that is not need to writer code alter whole that procedure. Example select maybe not clear, i have another example:

CODE

Procedure test
@variable nvarchar(5)
as
begin
      if @variable = '0'
      begin select * from table1 end
      if @variable = '1'
      begin update table1 set col1 = 'abc'
end

Now, i want to add part: if @variable = '2' begin delete from table2 where colID = '1' end
I can do that with full code:

CODE

alter Procedure test
@variable nvarchar(5)
as
begin
      if @variable = '0'
      begin select * from table1 end
      if @variable = '1'
      begin update table1 set col1 = 'abc' end
      @variable = '2'
      begin delete from table2 where colID = '1' end
end

So, i want to know, have a way to add part @variable = '2' to proceduce "test", not need to write full code.

[cheekybuddha]

There is no way to alter a procedure 'on the fly' without re-writing the whole thing.

You can construct the original procedure to accept an SQL string as a parameter and execute if, but then you may as well just execute the SQL directly and bypass the procedure altogether.

Perhaps if you explained what you are trying to do in reality rather than using a generic example we might be able to offer a better solution.

hth,

d

[theDBguy]

So, i want to know, have a way to add part @variable = '2' to proceduce "test", not need to write full code.

Hi. I could be wrong but I don't think you can do what you want. I think that's just the way T-SQL works. You'll have to do it that way.

[MadPiet]

Oh wow. Is this like a catch-all, do-everything stored procedure? Just say no. Optimizing that is going to be a nightmare.

What is the point of this stored procedure, anyway?