store procedure - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

store procedure

Author

Topic

Gayatri D.Patil
Yak Posting Veteran

58 Posts

Posted - 2007-12-22 : 01:55:49

hello sir
i want to write store procedure.
I write following code--

drop Proc Analyst

CREATE PROC Analyst
@UserID varchar(20),
@StockName varchar(50)
AS
INSERT tAnalystAssignHistory
(
AnalystAssignHistoryCode,
SecurityCode,
ISIN,
StockName,
UserID,
IsDummyEnabled,
AnalystAssignDate,
StatusCode,
CreatedBy,
CreateDate,
UpdatedBy,
UpdateDate
)
SELECT COALESCE((SELECT MAX(AnalystAssignHistoryCode) FROM tAnalystAssignHistory), 0) + 1,
SecurityCode,
ISIN,
StockName,
'ShahVis',
IsDummyEnabled,
AnalystAssignDate,
'A',
'Gayatri',
GETDATE(),
NULL,
NULL
FROM tAnalystAssignHistory
WHERE UserId = @UserID
and StockName = @StockName
GO

EXEC Analyst'PatelAmi','Gujarat Ambuja Cements Ltd.'

In this procedure i want to runtime insert ShahVis i.e REassignuserid. it will always change so i want runtime insert this field.
and created by this is store in globaly i want to retrive this runtime.
Please help me.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-22 : 02:12:30

Do you mea you need to pass the new user id as a param rather than hardcoding as 'ShahVis'? then do this:-

CREATE PROC Analyst
@UserID varchar(20),
@StockName varchar(50),
@NewUserID varchar(20)
AS
INSERT tAnalystAssignHistory
(
AnalystAssignHistoryCode,
SecurityCode,
ISIN,
StockName,
UserID,
IsDummyEnabled,
AnalystAssignDate,
StatusCode,
CreatedBy,
CreateDate,
UpdatedBy,
UpdateDate
)
SELECT COALESCE((SELECT MAX(AnalystAssignHistoryCode) FROM tAnalystAssignHistory), 0) + 1,
SecurityCode,
ISIN,
StockName,
@NewUserID,
IsDummyEnabled,
AnalystAssignDate,
'A',
'Gayatri',
GETDATE(),
NULL,
NULL 
FROM tAnalystAssignHistory
WHERE UserId = @UserID
and StockName = @StockName 
GO

EXEC Analyst'PatelAmi','Gujarat Ambuja Cements Ltd.','your new user id'

Gayatri D.Patil
Yak Posting Veteran

58 Posts

Posted - 2007-12-22 : 02:25:12

hello sir
yes its run.
but sir in my project i have userid=dropdownbax,stockname=listbox
and newuserid=globaly saved variable.
and i want to insert runtime data through dropdownbax,listbox,globaly saved variable etc.
then how can use stored procedure.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-22 : 02:27:54

What is the front end you are using? Grab the selected values of these params in front end and pass it as params while calling & executing the SP

Gayatri D.Patil
Yak Posting Veteran

58 Posts

Posted - 2007-12-22 : 02:36:15

i am ussing asp.net with c#
backend-MSSql Server
i didn't understand grab values etc...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-22 : 02:50:36

Write the .NET code to grab the selected values from combo,listbox and variable and pass it as params while calling the created SP.Look in any ASP.net tutorials to get an idea of how to grab values selected from a form and pass onto SP as parameters.

Refer this too. Might be of help

[url]http://forums.asp.net/t/1135266.aspx[/url]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-22 : 04:25:29

You have already asked this question here and got valid answers!
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=94531

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources