UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Run Script With Hide Mode, SQL Server 2008 R2    
 
   
mr.siro
post Oct 16 2019, 03:15 AM
Post#1



Posts: 185
Joined: 27-January 18



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
Go to the top of the page
 
GroverParkGeorge
post Oct 16 2019, 06:47 AM
Post#2


UA Admin
Posts: 36,040
Joined: 20-June 02
From: Newcastle, WA


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.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
MadPiet
post Oct 16 2019, 10:39 AM
Post#3



Posts: 3,358
Joined: 27-February 09



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.
Go to the top of the page
 
mr.siro
post Oct 19 2019, 03:30 AM
Post#4



Posts: 185
Joined: 27-January 18



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.
Go to the top of the page
 
cheekybuddha
post Oct 19 2019, 03:58 AM
Post#5


UtterAccess VIP
Posts: 11,678
Joined: 6-December 03
From: Telegraph Hill


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

--------------------


Regards,

David Marten
Go to the top of the page
 
mr.siro
post Oct 19 2019, 04:39 AM
Post#6



Posts: 185
Joined: 27-January 18



i mean, how to add part @variable = 3 in to SP test which not need to write code alter whole SP
Go to the top of the page
 
cheekybuddha
post Oct 19 2019, 05:17 AM
Post#7


UtterAccess VIP
Posts: 11,678
Joined: 6-December 03
From: Telegraph Hill


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!

--------------------


Regards,

David Marten
Go to the top of the page
 
mr.siro
post Oct 19 2019, 09:07 AM
Post#8



Posts: 185
Joined: 27-January 18



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.
Go to the top of the page
 
cheekybuddha
post Oct 19 2019, 10:50 AM
Post#9


UtterAccess VIP
Posts: 11,678
Joined: 6-December 03
From: Telegraph Hill


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. shrug.gif

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

--------------------


Regards,

David Marten
Go to the top of the page
 
theDBguy
post Oct 19 2019, 11:17 AM
Post#10


Access Wiki and Forums Moderator
Posts: 76,556
Joined: 19-June 07
From: SunnySandyEggo


QUOTE (mr.siro)
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.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
MadPiet
post Oct 20 2019, 01:46 AM
Post#11



Posts: 3,358
Joined: 27-February 09



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?
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    15th November 2019 - 01:41 PM