Running a SQL Server stored procedure

[Timbo]

Can anyone give me a straightforward example of how to run a SQL Server stored procedure? I have seen the example in the help: Web Publishing -> Page Techniques. But I think I do not know enough about dot variables to understand it. Can anyone provide another example that has a bit more explanation in it?

[robert brown]

Re: Running a SQL Server stored procedure

Can anyone give me a straightforward example of how to run a SQL Server stored procedure?

Hi

Suppose the following is your stored procedure in sql


***********************************************************
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[CRM_VIEW]
@ltname varchar(100),
@crtby varchar(50),
@revi int,
@stat varchar(20),
@lk varchar(50)
as
insert into HRMIS_CMG_MASTER_LETTER values(@ltname,@crtby,getdate(),@revi,@stat,'No',@lk)

************************************************************


So in the about stored procedure named CRM_VIEW, you have to pass 5 arguments.
Following is the code to execute this stored procedure, the below code you have to write in ALPHA FIVE button click or validation or inition or activation where were you want to execute the stored procedures.



dim t as SQL::Connection
dim flag as L
dim sql_insert as C
dim created_by as C
dim schedule as C
dim revision as N
flag = t.Open("::name::sqlcon")
created_by="Robert Brown"
revison=2
nme="Alpha"
schedule="tommorw"

sql_insert="CRM_VIEW"+"'"+CurrentForm.Controls.cmgltrtype+"','"+created_by+"','"+revision+"','"+schedule+"','"+nme+"'"

t.Execute(sql_insert)
t.Close()

************************************************************

Hope the above example if you read 2,3 times you will get clear idea. the general syntax is

" stored procedure name "+" arg1, arg2, etc "

Stored procedure name should be in double quotes and arguments if it is a variable you should follow the syntax as '"+arg1+"' if the argument you want to pass is some direct value, you can pass that value in single quotes for example 'robert'

Thank you
Robert--

[Timbo]

Re: Running a SQL Server stored procedure

Lovely. Thankyou very much. This has helped me to get it working.

[robert brown]

Re: Running a SQL Server stored procedure

good :)

[wford]

Re: Running a SQL Server stored procedure

Hello, I have a version of the code above that works, it calls a stored procedure that sends an email using the variables I specify..in an onaftersqlcommand event on a grid that lets a user enter an email address and subject and some text for the email body.

What I need are the EmailTo, Subject, EmailBody to be filled in with the data from the grid that was just submitted instead of hard coding them.

Any Ideas how I access the variables so I can send to the stored procedure..nothing I have tried works. Thanks!

dim t as SQL::Connection
dim flag as L
dim sql_insert as C
dim EmailTo as C
dim Subject as C
dim EmailBody as C

flag = t.Open("::name::CRM")

EmailTo = "[email protected]"
Subject="Test"
EmailBody="This Is A Test Email"

sql_insert="sEmailCust"+"'"+EmailTo+"','"+Subject+"','"+EmailBody+"'"

t.Execute(sql_insert)
t.Close()