interesting sp challenge - 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

SQL Server 2000 Forums

SQL Server Development (2000)

interesting sp challenge

Author

Topic

Hommer
Aged Yak Warrior

808 Posts

Posted - 2006-08-28 : 12:42:11

Hi, everybody,

Have any of you run into a requirement like this?

One of the parameter of this stored proc is optional and the direction will be set at the runtime.

If it is null, then it is an output, run insert statement with the other parameters then return @@identity to the param in question.

If it has value, then run update, use the param in the where clause.

I have tried several variations, but could not get it.

That calling part is from dotnet.

Any recommendation? Thanks!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-28 : 12:46:02

[code]CREATE PROCEDURE uspItemSave
(
@ID INT OUT,
@Name VARCHAR(50)
)
AS

SET NOCOUNT ON

IF @ID IS NULL
BEGIN
INSERT Items (Name) SELECT @Name
SELECT @ID = @@SCOPE_IDENTITY()
END
ELSE
UPDATE Items SET Name = @Name WHERE ID = @ID[/code]

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-28 : 12:46:59

And from .Net, set @ID as adParamInputOutput.

Peter Larsson
Helsingborg, Sweden

Hommer
Aged Yak Warrior

808 Posts

Posted - 2006-08-28 : 15:10:35

Peso,

Thank you for your reply.

I understand the concept of @@SCOPE_IDENTITY, but when I used it as you have suggested, I got: Must declare the variable '@@SCOPE_IDENTITY'.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2006-08-28 : 17:18:12

Remove the @@

SELECT @ID = SCOPE_IDENTITY()

-Ryan

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-29 : 01:16:31

Ooops. My bad. Ryan is right.
I started typing @@identity, but changed my mind since I don't know if the application is multiuser, or has audit triggers on the table.

SCOPE_IDENTITY() will take care of all that.

Peter Larsson
Helsingborg, Sweden

Hommer
Aged Yak Warrior

808 Posts

Posted - 2006-08-29 : 11:01:00

Thanks for the helps.

I still have a little trouble retreaving the return value. I have tried to use the arParms(0) first or directly use the variable, but none of it bring me back the ID. SQLHelper is from MS Date Access Application Block. The insert part of the sp did get run, and the data is in.

arParms(0) = New SqlParameter("@P1", SqlDbType.Int, ParameterDirection.InputOutput)
If LogID = 0 Then
arParms(0).Value = DBNull.Value
Else
arParms(0).Value = LogID
End If
If LogID = 0 Then
arParms(0).Value = CType(SqlHelper.ExecuteScalar(cn_str_Dev, CommandType.StoredProcedure, "CT_setCLG", arParms), Int32)
LogID = CType(arParms(0).Value, Int32)
Else
SqlHelper.ExecuteNonQuery(cn_str_Dev, CommandType.StoredProcedure, "CT_setCLG", arParms)
End If

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-08-29 : 11:13:19

I'd recommend you have your developers call different procedures for Updates and Selects. Your code will be more efficient.

What's next? Paremeters to indicate the table name? Heck, with enough parameters I suppose you could write an entire application that uses only one stored prodedure.

Reductio ad absurdum.

This is not a good database application design.

Hommer
Aged Yak Warrior

808 Posts

Posted - 2006-08-29 : 11:52:06

Good point!
I am glad I learnt something new(SCOPE_IDENTITY), but also got the good advice.
You folks are just great!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources